Payer

ComponentSchema

---

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


CREATE TABLE {{ schema_name }}.tmp_whisper_payer_{{ sub_version }} AS 
WITH 
payer_score_card_payer AS (
    SELECT 
        payer_id, 
        overall_score as payer_overall_score, 
        ip_hospital_score as payer_mrf_ip_hospital_score,
        op_hospital_score as payer_mrf_op_hospital_score,
        hospital_score as payer_mrf_hospital_score,
        asc_score as payer_mrf_asc_score,
        imaging_center_score as payer_mrf_imaging_center_score
    FROM {{ cld_params.Tables.PAYER_MRF_SCORES.value }}
    WHERE month_run = '{{ sub_version }}'
),
payer_score_card_state AS (
    SELECT 
        payer_id, 
        state, 
        overall_score as payer_overall_score_state, 
        ip_hospital_score as payer_mrf_ip_hospital_score_state,
        op_hospital_score as payer_mrf_op_hospital_score_state,
        hospital_score as payer_mrf_hospital_score_state,
        asc_score as payer_mrf_asc_score_state,
        imaging_center_score as payer_mrf_imaging_center_score_state
    FROM {{ cld_params.Tables.PAYER_MRF_STATE_SCORES.value }}
    WHERE month_run = '{{ sub_version }}'
)
SELECT 
    s.payer_id,
    s.payer_name,
    pscp.payer_overall_score,
    pscp.payer_mrf_ip_hospital_score,
    pscp.payer_mrf_op_hospital_score,
    pscp.payer_mrf_hospital_score,
    pscp.payer_mrf_asc_score,
    pscp.payer_mrf_imaging_center_score,
    pscs.payer_overall_score_state,
    pscs.payer_mrf_ip_hospital_score_state,
    pscs.payer_mrf_op_hospital_score_state,
    pscs.payer_mrf_hospital_score_state,
    pscs.payer_mrf_asc_score_state,
    pscs.payer_mrf_imaging_center_score_state,    
    pr.cbsa,
    pr.state,
    pr.cbsa_payer_covered_lives,
    pr.cbsa_market_share as cbsa_payer_covered_lives_market_share,
    pr.cbsa_rank as cbsa_payer_covered_lives_rank,
    pr.state_payer_covered_lives,
    pr.state_market_share as state_payer_covered_lives_market_share,
    pr.state_rank as state_payer_covered_lives_rank,
    pr.national_payer_covered_lives,
    pr.national_market_share as national_payer_covered_lives_market_share,
    pr.national_rank as national_payer_covered_lives_rank
FROM {{ schema_name }}.tmp_ref_payer_spine_{{ sub_version }} s
LEFT JOIN {{ schema_name }}.tmp_ref_policy_reporter_{{ sub_version }} pr
    ON pr.payer_id = s.payer_id
LEFT JOIN payer_score_card_payer pscp
    ON pscp.payer_id = s.payer_id
LEFT JOIN payer_score_card_state pscs
    ON pscs.payer_id = s.payer_id
    and pscs.state = pr.state