Code

ComponentSchema

---

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

CREATE TABLE {{ schema_name }}.tmp_whisper_code_{{ sub_version }} 
WITH (
    PARTITIONING = ARRAY['bill_type']
)
AS
WITH
codes AS (
    SELECT 
        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
),
komodo_utilization AS (
    SELECT
        distinct
        taxonomy_grouping,
        billing_code,
        billing_code_type,
        bill_type,
        national_commercial_code_taxonomy_encounters as count_encounters
    FROM {{ schema_name }}.tmp_ref_utilizations_{{ sub_version}}
)
SELECT
    c.bill_type,
    c.billing_code,
    c.billing_code_type,
    c.taxonomy_grouping,
    c.canonical_rate as code_national_avg_canonical_rate,
    ka.count_encounters as code_national_avg_number_of_claims,
    c.canonical_rate * ka.count_encounters as code_national_estimated_revenue,
    RANK() OVER (
        PARTITION BY c.taxonomy_grouping 
        ORDER BY c.canonical_rate DESC
    ) as rank_code_canonical_rate,
    RANK() OVER (
        PARTITION BY c.taxonomy_grouping 
        ORDER BY c.canonical_rate * ka.count_encounters DESC
    ) as rank_code_revenue,
    RANK() OVER (
        PARTITION BY ka.taxonomy_grouping 
        ORDER BY ka.count_encounters DESC
    ) as rank_code_claims
FROM codes c
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
WHERE canonical_rate IS NOT NULL
AND count_encounters IS NOT NULL