professional

professional

-- 🏥🏥🏥
-- Consumer Pricing Service 
-- run_id: {{ run_id }}
-- task_name: {{ task_name }}
-- Table: {{ ps_schema }}.professional_cld_enhanced_{{ ps_month }} 
-- Subversion: {{ ps_month }}

--------------------------------
-- Purpose:
-- This table pulls all professional fee data for facilities from CLD by mapping physician group professional fees
-- to facilities (Imaging Centers, ASCs, Hospitals) using provider xwalks.
--------------------------------

{% set cld_cols = [
    'id', 
    'roid', 
    'ssp_id', 
    'ssp_name', 
    'sub_package_id', 
    'payer_id', 
    'payer_name',
    'network_id',
    'network_name', 
    'provider_id', 
    'provider_type', 
    'npi', 
    'state', 
    'cbsa', 
    'cbsa_name', 
    'fee_type', 
    'is_package_base_code', 
    'bill_type',
    'facility',
    'billing_code', 
    'billing_code_type', 
    'anesthesia_unit_conversion',
    'average_units', 
    'billing_code_description', 
    'validated_rate', 
    'validated_prof_fee_rate', 
    'claims_validated_rate', 
    'commercial_validated_reimbursement_rate', 
    'commercial_validated_cash_rate', 
    'negotiated_rate', 
    'discounted_cash_rate',
    'state_avg_medicare_rate',
    'lower_bound',
    'upper_bound',
    'contract_methodology', 
    'gross_charge', 
    'source', 
] %}

CREATE TABLE {{ ps_schema }}.professional_cld_enhanced_{{ ps_month }} AS
WITH

--------------------------------
-- Clear Rates has Professional Fees for Facilities via Physician Groups

--  1. Get all facility providers (Imaging Centers, ASCs, Hospitals)
--  2. Get all professional fees from CLD where provider_type = 'Physician Group'
--  3. Cross join facility providers with professional fees
--------------------------------
providers AS (
    SELECT
        DISTINCT
        provider_id,
        provider_type,
        npi,
        state,
        cbsa,
        cbsa_name
    FROM {{ ps_schema }}.clear_rates_enhanced_{{ ps_month }}
    WHERE provider_type = 'ASC'
    OR provider_type LIKE '%Hospital%'
),
professional_fees AS (
    SELECT 
        ssp_id,
        sub_package_id,
        payer_id,
        network_id,
        bill_type,
        facility,
        billing_code,
        billing_code_type,
        ANY_VALUE(average_units) AS average_units,
        ANY_VALUE(anesthesia_unit_conversion) AS anesthesia_unit_conversion,
        ANY_VALUE(ssp_name) AS ssp_name,
        ANY_VALUE(payer_name) AS payer_name,
        ANY_VALUE(network_name) AS network_name,
        ANY_VALUE(is_package_base_code) AS is_package_base_code,
        ANY_VALUE(billing_code_description) AS billing_code_description,
        False AS validated_rate,
        True AS validated_prof_fee_rate,
        True AS claims_validated_rate,
        True AS commercial_validated_reimbursement_rate,
        True AS commercial_validated_cash_rate,
        ANY_VALUE(state_avg_medicare_rate) AS state_avg_medicare_rate,
        ANY_VALUE(lower_bound) AS lower_bound,
        ANY_VALUE(upper_bound) AS upper_bound,
        ANY_VALUE(contract_methodology) AS contract_methodology,
        ANY_VALUE(gross_charge) AS gross_charge,
        'professional' AS fee_type,
        'payer' AS source
    FROM {{ ps_schema }}.clear_rates_enhanced_{{ ps_month }} f
    WHERE fee_type = 'professional'
    AND provider_type = 'Physician Group'
    GROUP BY
        1,2,3,4,5,6,7,8
),
providers_professional_fees_cartesian AS (
    SELECT 
        from_big_endian_64(xxhash64(CAST(
            pf.payer_id || 
            CAST(pf.network_id AS VARCHAR) ||
            p.provider_id ||
            pf.bill_type ||
            pf.billing_code ||
            pf.billing_code_type ||
            COALESCE(CAST(pf.facility AS VARCHAR), '')
            AS varbinary
        ))) AS roid,
        p.*,
        pf.*
    FROM providers p
    CROSS JOIN professional_fees pf
), 

--------------------------------
-- Map Physician Groups to Facilities via Spines and Definitive Xwalks
--------------------------------
spines AS (
    SELECT 
        provider_id, 
        dh_hospital_id, 
        provider_state as state,
        provider_cbsa_code AS cbsa,
        c.cbsa_name,
        'Imaging Center' AS provider_type,
        npi
    FROM {{ tables.SPINES_PROVIDER_IMAGING_CENTERS.value }} i
    LEFT JOIN {{ tables.SPINES_GEO_CBSA.value }} c
        ON i.provider_cbsa_code = c.cbsa_id
    UNION ALL
    SELECT 
        provider_id, 
        dh_hospital_id, 
        provider_state as state,
        provider_cbsa_code AS cbsa,
        c.cbsa_name,
        'ASC' AS provider_type,
        npi
    FROM {{ tables.SPINES_PROVIDER_ASCS.value }} i
    LEFT JOIN {{ tables.SPINES_GEO_CBSA.value }} c
        ON i.provider_cbsa_code = c.cbsa_id
    UNION ALL
    SELECT 
        provider_id, 
        CAST(dh_hospital_id AS INT), 
        provider_state as state,
        provider_cbsa_code AS cbsa,
        c.cbsa_name,
        provider_subtype AS provider_type,
        npi
    FROM {{ tables.SPINES_PROVIDER_HOSPITALS.value }} i
    LEFT JOIN {{ tables.SPINES_GEO_CBSA.value }} c
        ON i.provider_cbsa_code = c.cbsa_id
),
physician_group_to_facility_xwalks AS (
    SELECT DISTINCT
        x.provider_id,
        provider_type,
        spi.provider_id AS facility_provider_id,
        spi.cbsa,
        spi.cbsa_name,
        spi.state,
        spi.npi
    FROM {{ tables.EMPLOYER_HCP_NPI_PROVIDER_ID_XWALK.value }} x
    LEFT JOIN {{ tables.DEFINITIVE_IMAGING_CENTER_PHYSICIANS.value }} i
        ON x.hcp_npi = CAST(i.npi AS VARCHAR)
    LEFT JOIN spines spi
        ON i.hospital_id = spi.dh_hospital_id
    WHERE i.hospital_id IS NOT NULL

    UNION ALL

    SELECT DISTINCT
        x.provider_id,
        provider_type,
        sps.provider_id AS facility_provider_id,
        sps.cbsa,
        sps.cbsa_name,
        sps.state,
        sps.npi
    FROM {{ tables.EMPLOYER_HCP_NPI_PROVIDER_ID_XWALK.value }} x
    LEFT JOIN {{ tables.DEFINITIVE_SURGERY_CENTERS_PHYSICIANS.value }} s
        ON x.hcp_npi = CAST(s.npi AS VARCHAR)
    LEFT JOIN spines sps
        ON s.hospital_id = sps.dh_hospital_id
    WHERE s.hospital_id IS NOT NULL

    UNION ALL

    SELECT DISTINCT
        x.provider_id,
        provider_type,
        sph.provider_id AS facility_provider_id,
        sph.cbsa,
        sph.cbsa_name,
        sph.state,
        sph.npi
    FROM {{ tables.EMPLOYER_HCP_NPI_PROVIDER_ID_XWALK.value }} x
    LEFT JOIN {{ tables.DEFINITIVE_PHYSICIANS_OVERVIEW.value }} p
        ON x.hcp_npi = CAST(p.npi AS VARCHAR)
    LEFT JOIN spines sph
        ON p.primary_affiliation_hospital_id = CAST(sph.dh_hospital_id AS VARCHAR)
    WHERE p.primary_affiliation_hospital_id IS NOT NULL
),

--------------------------------
-- Get Provider-Level Rates and Geo Averages
--------------------------------
prof_fees AS (
    SELECT 
        f.ssp_id,
        f.sub_package_id,
        f.payer_id,
        f.network_id,
        pg.facility_provider_id AS provider_id,
        NULLIF(pg.cbsa, '99999') AS cbsa,
        pg.state,
        'professional' AS fee_type,
        f.billing_code_type,
        f.billing_code,
        f.negotiated_rate,
        f.discounted_cash_rate
    FROM {{ ps_schema }}.clear_rates_enhanced_{{ ps_month }} f
    LEFT JOIN physician_group_to_facility_xwalks pg
        ON f.provider_id = pg.provider_id
        AND f.provider_type = 'Physician Group'
),
geo_avg AS (
    SELECT 
        COALESCE(cbsa, state) as geo,
        billing_code,
        billing_code_type,
        AVG(negotiated_rate) AS geo_avg_negotiated_rate,
        AVG(discounted_cash_rate) AS geo_avg_discounted_cash_rate
    FROM prof_fees
    WHERE negotiated_rate IS NOT NULL
    GROUP BY 1,2,3
),

--------------------------------
-- Dedupe with Max Rate
--------------------------------
prof_fees_max_ranked AS (
    SELECT *
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (
                PARTITION BY ssp_id, sub_package_id, network_id, provider_id, fee_type, billing_code_type, billing_code 
                ORDER BY negotiated_rate, discounted_cash_rate DESC
            ) AS rn
        FROM prof_fees
    )
    WHERE rn = 1
),

--------------------------------
-- For each facility provider, get professional fees from physician groups
--------------------------------
final AS (
    SELECT 
        {% for col in cld_cols -%}
            {%- if col == 'id' -%}
                from_big_endian_64(xxhash64(CAST(
                    CAST(cld.roid AS VARCHAR) || 
                    cld.provider_id ||
                    cld.ssp_id ||
                    cld.sub_package_id ||
                    cld.fee_type
                    AS varbinary
                ))) AS id,
            {%- elif col == 'negotiated_rate' -%}
                COALESCE(c.negotiated_rate, sa.geo_avg_negotiated_rate) AS unadjusted_negotiated_rate,
            {%- elif col == 'discounted_cash_rate' -%}
                COALESCE(c.discounted_cash_rate, sa.geo_avg_discounted_cash_rate) AS discounted_cash_rate,
            {%- else -%}
                cld.{{ col }}, 
            {%- endif -%}
        {%- endfor %}
        CASE
            WHEN c.negotiated_rate IS NULL THEN True
            ELSE NULL
        END AS state_avg_negotiated_rate_used,
        CASE
            WHEN c.discounted_cash_rate IS NULL THEN True
            ELSE NULL
        END AS state_avg_discounted_cash_rate_used
    FROM providers_professional_fees_cartesian cld
    LEFT JOIN prof_fees_max_ranked c
        ON cld.payer_id = c.payer_id
        AND cld.network_id = c.network_id
        AND cld.provider_id = c.provider_id
        AND cld.billing_code = c.billing_code
        AND cld.billing_code_type = c.billing_code_type
    LEFT JOIN geo_avg sa
        ON COALESCE(cld.cbsa, cld.state) = sa.geo
        AND cld.billing_code = sa.billing_code
        AND cld.billing_code_type = sa.billing_code_type
    WHERE (
        c.negotiated_rate IS NOT NULL
        OR sa.geo_avg_negotiated_rate IS NOT NULL
        OR c.discounted_cash_rate IS NOT NULL
        OR sa.geo_avg_discounted_cash_rate IS NOT NULL
    )
),
anesthesia_unit_conversions AS (
    SELECT 
        final.*,
        CASE 
            WHEN 
                payer_id = '643' 
                AND anesthesia_unit_conversion IS NOT NULL 
                AND source = 'payer'
            THEN unadjusted_negotiated_rate 
            WHEN 
                payer_id = '44' 
                AND anesthesia_unit_conversion IS NOT NULL 
                AND source = 'payer'
            THEN unadjusted_negotiated_rate * 15
            WHEN 
                payer_id IN ('7', '42', '76', '169', '389', '403', '229', '388') 
                AND anesthesia_unit_conversion IS NOT NULL 
                AND source = 'payer'
            THEN unadjusted_negotiated_rate/(an.base_units + 1)
            WHEN 
                payer_id = '101' 
                AND anesthesia_unit_conversion IS NOT NULL 
                AND source = 'payer'
            THEN unadjusted_negotiated_rate/an.base_units
            ELSE unadjusted_negotiated_rate
        END as negotiated_rate
    FROM final
    LEFT JOIN (
        SELECT billing_code, max(base_units) AS base_units
        FROM {{ tables.REF_ANESTHESIA_BASE_UNITS.value }}
        GROUP BY billing_code
    ) an 
        ON final.billing_code = an.billing_code
)
-- Dedupe at the facility level
SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY id
            ORDER BY negotiated_rate DESC, discounted_cash_rate DESC
        ) AS rn
    FROM anesthesia_unit_conversions
)
WHERE rn = 1