Provider-Code

ComponentSchema

---

-- 🪟💲🌟
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ schema_name }}.tmp_whisper_provider_code_{{ sub_version }} 
-- Subversion: {{ sub_version }}


CREATE OR REPLACE TABLE {{ schema_name }}.tmp_whisper_provider_code_{{ sub_version }}
WITH (
    PARTITIONING = ARRAY['bill_type']
)
AS
WITH 
provider_rates AS (
    SELECT 
        provider_id, 
        taxonomy_grouping,
        state,
        bill_type, 
        billing_code,
        billing_code_type, 
        avg(canonical_rate) as canonical_rate
    FROM {{ schema_name }}.tmp_int_combined_no_whisp_{{ sub_version }}
    WHERE canonical_rate_score > {{ min_non_outlier_raw_score }}
    GROUP BY 1, 2, 3, 4, 5, 6
),
komodo_utilization AS (
    SELECT
        distinct
        state,
        taxonomy_grouping,
        billing_code,
        billing_code_type,
        bill_type,
        state_commercial_code_taxonomy_encounters as count_encounters
    FROM {{ schema_name }}.tmp_ref_utilizations_{{ sub_version}}
),
provider_code_allowables AS (
    SELECT
        pr.provider_id,
        pr.taxonomy_grouping,
        pr.bill_type,
        pr.billing_code_type,
        pr.billing_code,
        avg(canonical_rate) * sum(count_encounters) as total_estimated_revenue,
        sum(count_encounters) as total_claims,
        avg(canonical_rate) as canonical_rate
    FROM provider_rates pr
    JOIN komodo_utilization ka
        ON ka.state = pr.state
        AND ka.bill_type = pr.bill_type
        AND ka.billing_code = pr.billing_code
        AND ka.billing_code_type = pr.billing_code_type
        AND ka.taxonomy_grouping = pr.taxonomy_grouping
    GROUP BY 
        pr.provider_id,
        pr.bill_type,
        pr.billing_code_type,
        pr.billing_code,
        pr.taxonomy_grouping
)
SELECT 
    p.provider_id,
    p.provider_name,
    p.health_system_name,
    p.medicare_provider_id,
    p.city,
    p.cbsa_name,
    p.state,
    km.taxonomy_grouping,
    km.bill_type,
    km.billing_code_type,
    km.billing_code,
    km.total_estimated_revenue as provider_code_total_estimated_revenue,
    km.total_claims as provider_code_total_claims,
    km.canonical_rate as provider_code_avg_canonical_rate,
    ROW_NUMBER() OVER (
        PARTITION BY p.provider_id 
        ORDER BY km.total_estimated_revenue DESC
    ) as rank_code_within_provider_over_revenue,
    ROW_NUMBER() OVER (
        PARTITION BY p.provider_id 
        ORDER BY km.total_claims DESC
    ) as rank_code_within_provider_over_claims,
    ROW_NUMBER() OVER (
        PARTITION BY p.provider_id 
        ORDER BY km.canonical_rate DESC
    ) as rank_code_within_provider_over_canonical_rate
FROM {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} p
JOIN  provider_code_allowables km
    ON p.provider_id = km.provider_id
    AND p.taxonomy_grouping = km.taxonomy_grouping