Definitive

ComponentSchema

---

Hospital Payer Mix

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


CREATE TABLE {{ schema_name }}.tmp_ref_definitive_hospital_payer_mix_{{ sub_version }} AS 
WITH 
hospital_xwalk AS (
    SELECT
        definitive_id,
        max(tq_id) as tq_id
    FROM {{ cld_params.Tables.DEFINITIVE_HOSPITAL_XWALK.value }}
    GROUP BY definitive_id
),
payer_xwalk AS (
    SELECT definitive_payer_id, payer_id
    FROM {{ schema_name }}.tmp_ref_payer_spine_{{ sub_version }}
),
payer_corrected_definitive AS (
    SELECT
        COALESCE(CAST(px.payer_id AS INT), df.payor_id) as corrected_payor_id,
        df.*
    FROM {{ cld_params.Tables.DEFINITIVE_HOSPITAL_CLAIMS_BASED_PAYOR_MIX.value }} df
    LEFT JOIN payer_xwalk px
        ON CONTAINS(px.definitive_payer_id, df.payor_id)
    WHERE claim_year = 2023
),
regrouped AS (
    SELECT
        corrected_payor_id,
        hospital_id,
        SUM(claims) as claims,
        SUM(pct_of_total_claims) as pct_of_total_claims,
        SUM(charges) as charges,
        SUM(pct_of_total_charges) as pct_of_total_charges
    FROM payer_corrected_definitive
    GROUP BY
        corrected_payor_id,
        hospital_id
),
reranked AS (
    SELECT
        rg.*,
        ROW_NUMBER() OVER (PARTITION BY rg.hospital_id ORDER BY rg.pct_of_total_claims DESC) as payor_rank
    FROM regrouped rg
)
SELECT 
    hx.tq_id as provider_id,
    df.corrected_payor_id as tq_payer_id,
    df.claims,
    df.pct_of_total_claims,
    df.charges,
    df.pct_of_total_charges,
    df.payor_rank
FROM reranked df
LEFT JOIN hospital_xwalk hx
    ON df.hospital_id = hx.definitive_id 
WHERE 
df.hospital_id NOT IN (
    SELECT hospital_id
    FROM {{ cld_params.Tables.DEFINITIVE_HOSPITAL_OVERVIEW.value }}
    WHERE company_status = 'Closed'
)
AND
df.corrected_payor_id IN (
    SELECT CAST(payer_id AS INT)
    FROM {{ schema_name }}.tmp_ref_payer_spine_{{ sub_version }}
)

Hospital Market Share

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


CREATE TABLE {{ schema_name }}.tmp_ref_definitive_hospital_market_share_{{ sub_version }} AS 
WITH
hospital_xwalk AS (
    SELECT
        definitive_id,
        provider_id
    FROM {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }}
),
df AS (
    SELECT 
        df.hospital_id as definitive_id,
        df.hospital_ownership,
        df.market_concentration_index,
        df.geographic_classification,
        df._340b_hospital_type,
        df.number_staffed_beds,
        df.number_licensed_beds,
        df.pos_medical_school_affiliation,
        df.medical_school_affiliates,
        hx.provider_id,
        df.hq_state as state,
        SPLIT(cbsa_code, ' -')[1] as cbsa,
        net_patient_revenue
    FROM {{ cld_params.Tables.DEFINITIVE_HOSPITAL_OVERVIEW.value }} df
    LEFT JOIN hospital_xwalk hx
        ON df.hospital_id = hx.definitive_id 
    WHERE company_status != 'Closed'
),
cbsa_total AS (
    SELECT 
        cbsa,
        SUM(net_patient_revenue) as cbsa_net_patient_revenue
    FROM df
    GROUP BY cbsa
),
state_total AS (
    SELECT 
        state,
        SUM(net_patient_revenue) as state_net_patient_revenue
    FROM df
    GROUP BY state
),
national_total AS (
    SELECT 
        SUM(net_patient_revenue) as national_net_patient_revenue
    FROM df
),
-- aggregate bc definitive:tq-provider-id is many:1 
agg AS (
    SELECT 
        df.provider_id,
        df.cbsa,
        df.state,
        df.hospital_ownership,
        df.market_concentration_index,
        df.geographic_classification,
        df._340b_hospital_type,
        df.number_staffed_beds,
        df.number_licensed_beds,
        df.pos_medical_school_affiliation,
        df.medical_school_affiliates,
        sum(net_patient_revenue) as net_patient_revenue,
        sum(net_patient_revenue) / state_net_patient_revenue as pct_of_state_net_patient_revenue,
        sum(net_patient_revenue) / cbsa_net_patient_revenue as pct_of_cbsa_net_patient_revenue,
        sum(net_patient_revenue) / national_net_patient_revenue as pct_of_national_net_patient_revenue
    FROM df
    LEFT JOIN cbsa_total ct
        ON df.cbsa = ct.cbsa
    LEFT JOIN state_total st
        ON df.state = st.state
    LEFT JOIN national_total nt
        ON 1=1
    GROUP BY 
        df.provider_id, 
        df.cbsa,
        df.state,
        df.hospital_ownership,
        df.market_concentration_index,
        df.geographic_classification,
        df._340b_hospital_type,
        df.number_staffed_beds,
        df.number_licensed_beds,
        df.pos_medical_school_affiliation,
        df.medical_school_affiliates,
        cbsa_net_patient_revenue,
        state_net_patient_revenue,
        national_net_patient_revenue
)
SELECT 
    a.provider_id,
    a.cbsa,
    a.state,
    a.hospital_ownership,
    a.market_concentration_index,
    a.geographic_classification,
    a._340b_hospital_type,
    a.number_staffed_beds,
    a.number_licensed_beds,
    a.pos_medical_school_affiliation,
    a.medical_school_affiliates,
    a.net_patient_revenue,
    a.pct_of_cbsa_net_patient_revenue,
    a.pct_of_state_net_patient_revenue,
    a.pct_of_national_net_patient_revenue,
    RANK() OVER (PARTITION BY a.cbsa ORDER BY a.net_patient_revenue DESC) as cbsa_rank,
    RANK() OVER (PARTITION BY a.state ORDER BY a.net_patient_revenue DESC) as state_rank,
    RANK() OVER (ORDER BY a.net_patient_revenue DESC) as national_rank
FROM agg a
WHERE net_patient_revenue IS NOT NULL
AND cbsa != '99999'