Provider-Payer

ComponentSchema

---

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


CREATE OR REPLACE TABLE {{ schema_name }}.tmp_whisper_provider_payer_{{ sub_version }}
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
SELECT 
    CAST(provider_id AS VARCHAR) as provider_id,
    CAST(tq_payer_id AS VARCHAR) as payer_id,
    claims as provider_payer_claims,
    charges as provider_payer_charges,
    pct_of_total_claims as within_provider_payer_pct_of_total_claims,
    pct_of_total_charges as within_provider_payer_pct_of_total_charges,
    payor_rank as within_provider_payer_payer_rank
FROM {{ schema_name }}.tmp_ref_definitive_hospital_payer_mix_{{ sub_version }}
WHERE provider_id IS NOT NULL

Provider-Payer-Network

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


CREATE OR REPLACE TABLE {{ schema_name }}.tmp_whisper_provider_payer_network_{{ sub_version }}
AS
SELECT 
    provider_id,
    payer_id,
    network_id,
    APPROX_PERCENTILE(
        CASE
            WHEN msdrg_mrf_base_rate_mult_cms_weight_validation_score > 1
                AND canonical_rate_type = 'impute: msdrg_mrf_base_rate_mult_cms_weight'
                AND canonical_rate_score > 1
                AND canonical_rate_percent_of_state_avg_medicare BETWEEN 1 AND 20
            THEN msdrg_mrf_base_rate
            WHEN msdrg_base_rate_mult_cms_weight_validation_score > 1
                AND canonical_rate_type = 'impute: msdrg_base_rate_mult_cms_weight'
                AND canonical_rate_score > 1
                AND canonical_rate_percent_of_state_avg_medicare BETWEEN 1 AND 20
            THEN msdrg_base_rate
            ELSE NULL
        END, 
        0.5
    ) as provider_network_msdrg_base_rate
FROM {{ schema_name }}.tmp_int_combined_no_whisp_{{ sub_version }}
WHERE msdrg_base_rate_mult_cms_weight_validation_score > 1
    OR msdrg_mrf_base_rate_mult_cms_weight_validation_score > 1
GROUP BY 
    provider_id,
    payer_id,
    network_id