Payer-Code

ComponentSchema

---

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

CREATE OR REPLACE TABLE {{ schema_name }}.tmp_whisper_payer_code_{{ sub_version }} 
WITH (
    PARTITIONING = ARRAY['payer_id','bill_type']
)
AS
WITH
codes AS (
    SELECT
        payer_id, 
        taxonomy_grouping,
        bill_type, 
        billing_code,
        billing_code_type,
        avg(canonical_rate) FILTER (WHERE canonical_rate_score > {{ min_non_outlier_raw_score }}) as canonical_rate
    FROM {{ schema_name }}.tmp_int_combined_no_whisp_{{ sub_version }}
    GROUP BY 1, 2, 3, 4, 5
),
komodo_utilization AS (
    SELECT
        distinct
        payer_id,
        taxonomy_grouping,
        billing_code,
        billing_code_type,
        bill_type,
        national_payer_code_taxonomy_encounters as count_encounters
    FROM {{ schema_name }}.tmp_ref_utilizations_{{ sub_version}}
)
SELECT
    c.payer_id,
    ps.payer_name,
    c.bill_type,
    c.billing_code,
    c.billing_code_type,
    c.taxonomy_grouping,
    c.canonical_rate as payer_code_avg_canonical_rate,
    ka.count_encounters as payer_code_avg_number_of_claims,
    c.canonical_rate * ka.count_encounters as payer_code_estimated_revenue,
    RANK() OVER (
        PARTITION BY c.payer_id, c.taxonomy_grouping 
        ORDER BY c.canonical_rate DESC
    ) as rank_payer_code_canonical_rate,
    RANK() OVER (
        PARTITION BY c.payer_id, c.taxonomy_grouping 
        ORDER BY c.canonical_rate * ka.count_encounters DESC
    ) as rank_payer_code_revenue,
    RANK() OVER (
        PARTITION BY c.payer_id, ka.taxonomy_grouping 
        ORDER BY ka.count_encounters DESC
    ) as rank_payer_code_claims
FROM codes c
JOIN {{ schema_name }}.tmp_ref_payer_spine_{{ sub_version }} ps
    ON c.payer_id = ps.payer_id
JOIN komodo_utilization ka
    ON ka.bill_type = c.bill_type
    AND ka.billing_code = c.billing_code
    AND ka.billing_code_type = c.billing_code_type
    AND ka.taxonomy_grouping = c.taxonomy_grouping
    AND ka.payer_id = c.payer_id
WHERE c.canonical_rate IS NOT NULL
    AND ka.count_encounters IS NOT NULL