Policy Reporter

ComponentSchema

---

Payer Covered Lives (CBSA and State)

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


CREATE TABLE {{ schema_name }}.tmp_ref_policy_reporter_{{ sub_version }} AS 
WITH
policy_reporter AS (
    SELECT
        pr.line_of_business,
        pr.plan_type,
        pr.policy_reporter_payer,
        pr.parent_organization,
        pr.state_short as state,
        pr.county,
        pr.covered_lives,
        pr.tq_payer_payer_id,
        xwalk.cbsa_description,
        xwalk.cbsa
    FROM {{ cld_params.Tables.POLICY_REPORTER_COUNTY.value }} pr
    LEFT JOIN {{ cld_params.Tables.POLICY_REPORTER_CBSA_XWALK.value }} xwalk
        ON pr.cbsa = xwalk.policy_reporter_cbsa
        AND pr.state_short = xwalk.state
    WHERE pr.line_of_business = 'Commercial'
),
cbsa_payer_total AS (
    SELECT
        line_of_business,
        state,
        cbsa,
        tq_payer_payer_id,
        sum(covered_lives) as payer_covered_lives
    FROM policy_reporter
    GROUP BY 1, 2, 3, 4
),
cbsa_total AS (
    SELECT
        line_of_business,
        state,
        cbsa,
        sum(covered_lives) as cbsa_covered_lives
    FROM policy_reporter
    GROUP BY 1, 2, 3
),
state_payer_total AS (
    SELECT
        line_of_business,
        state,
        tq_payer_payer_id,
        sum(covered_lives) as payer_covered_lives
    FROM policy_reporter
    GROUP BY 1, 2, 3
),
state_total AS (
    SELECT
        line_of_business,
        state,
        sum(covered_lives) as state_covered_lives
    FROM policy_reporter
    GROUP BY 1, 2
),
national_payer_total AS (
    SELECT
        line_of_business,
        tq_payer_payer_id,
        sum(covered_lives) as payer_covered_lives
    FROM policy_reporter
    GROUP BY 1, 2
),
national_total AS (
    SELECT
        line_of_business,
        sum(covered_lives) as national_covered_lives
    FROM policy_reporter
    GROUP BY 1
),
payer_names AS (
    SELECT distinct payer_id, payer_name
    FROM {{ schema_name }}.tmp_ref_payer_spine_{{ sub_version }}
),
payer_market_share AS (
    SELECT 
        distinct
        pr.state,
        pr.cbsa_description,
        pr.cbsa,
        pr.line_of_business,
        pr.tq_payer_payer_id as payer_id,
        pn.payer_name,
        cpt.payer_covered_lives as cbsa_payer_covered_lives,
        ct.cbsa_covered_lives,
        cpt.payer_covered_lives / ct.cbsa_covered_lives as cbsa_market_share,
        spt.payer_covered_lives as state_payer_covered_lives,
        st.state_covered_lives,
        spt.payer_covered_lives / st.state_covered_lives as state_market_share,
        npt.payer_covered_lives as national_payer_covered_lives,
        nt.national_covered_lives,
        npt.payer_covered_lives / nt.national_covered_lives as national_market_share
    FROM policy_reporter pr
    LEFT JOIN cbsa_payer_total cpt
        ON pr.state = cpt.state
        AND pr.cbsa = cpt.cbsa
        AND pr.tq_payer_payer_id = cpt.tq_payer_payer_id
        AND pr.line_of_business = cpt.line_of_business
    LEFT JOIN cbsa_total ct
        ON pr.state = ct.state
        AND pr.cbsa = ct.cbsa
        AND pr.line_of_business = ct.line_of_business
    LEFT JOIN state_payer_total spt
        ON pr.state = spt.state
        AND pr.tq_payer_payer_id = spt.tq_payer_payer_id
        AND pr.line_of_business = spt.line_of_business
    LEFT JOIN state_total st
        ON pr.state = st.state
        AND pr.line_of_business = st.line_of_business
    LEFT JOIN national_payer_total npt
        ON pr.tq_payer_payer_id = npt.tq_payer_payer_id
        AND pr.line_of_business = npt.line_of_business
    LEFT JOIN national_total nt
        ON pr.line_of_business = nt.line_of_business
    LEFT JOIN payer_names pn
        ON pr.tq_payer_payer_id = pn.payer_id
)
SELECT
    state,
    cbsa_description,
    cbsa,
    payer_id,
    payer_name,
    cbsa_payer_covered_lives,
    cbsa_covered_lives,
    cbsa_market_share,
    RANK() OVER (
        PARTITION BY state, cbsa, line_of_business
        ORDER BY cbsa_market_share DESC
    ) as cbsa_rank,
    state_payer_covered_lives,
    state_covered_lives,
    state_market_share,
    RANK() OVER (
        PARTITION BY state, line_of_business
        ORDER BY state_market_share DESC
    ) as state_rank,
    national_payer_covered_lives,
    national_covered_lives,
    national_market_share,
    RANK() OVER (
        PARTITION BY line_of_business
        ORDER BY national_market_share DESC
    ) as national_rank
FROM payer_market_share