Benchmarks

ComponentSchema

---

SQL

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


{%- set percentiles = [
    ('30','"30th_percentile"'),
    ('50','"median_allowed_amount"'),
    ('70','"70th_percentile"')
] %}
{%- set geos = ['provider_id', 'cbsa', 'zip3_code', 'state'] %}
{%- set geo_name_map = {
    'provider_id': 'provider_id',
    'cbsa': 'cbsa',
    'zip3_code': 'zip3',
    'state': 'state'
} %}
{%- set geo_table_map = {
    'provider_id': 'claims_benchmarks_allowable_npi_payer',
    'cbsa': 'claims_benchmarks_allowable_cbsa_payer',
    'zip3_code': 'claims_benchmarks_allowable_zip3_payer',
    'state': 'claims_benchmarks_allowable_state_payer'
} %}

CREATE TABLE {{ schema_name }}.tmp_int_benchmarks_{{ sub_version }} 
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH
{% if historic_run %}
------------------------------------
-- Latest dates for historic runs
------------------------------------
latest_ipps_dates AS (
    SELECT
        MAX(start_effective_date) as latest_start_effective_date
    FROM {{ cld_params.Tables.IPPS_MEDICARE_REFERENCE_TABLE.value }}
    WHERE year(start_effective_date) = {{ sub_version[:4] }}
),
latest_opps_dates AS (
    SELECT
        MAX(start_effective_date) as latest_start_effective_date
    FROM {{ cld_params.Tables.OPPS_MEDICARE_REFERENCE_TABLE.value }}
    WHERE year(start_effective_date) = {{ sub_version[:4] }}
),
latest_asc_dates AS (
    SELECT
        MAX(start_effective_date) as latest_start_effective_date
    FROM {{ cld_params.Tables.ASC_MEDICARE_REFERENCE_TABLE.value }}
    WHERE year(start_effective_date) = {{ sub_version[:4] }}
),
latest_clinical_lab_dates AS (
    SELECT
        MAX(start_effective_date) as latest_start_effective_date
    FROM {{ cld_params.Tables.CLINICAL_LAB_REFERENCE_PRICING.value }}
    WHERE year(start_effective_date) = {{ sub_version[:4] }}
),
latest_asp_dates AS (
    SELECT
        MAX(start_effective_date) as latest_start_effective_date
    FROM {{ cld_params.Tables.ASP_PRICING_TABLE.value }}
    WHERE year(start_effective_date) = {{ sub_version[:4] }}
),
latest_mpfs_dates AS (
    SELECT
        MAX(start_effective_date) as latest_start_effective_date
    FROM {{ cld_params.Tables.MPFS_MEDICARE_REFERENCE_TABLE.value }}
    WHERE year(start_effective_date) = {{ sub_version[:4] }}
),
latest_anesthesia_dates AS (
    SELECT
        MAX(start_effective_date) as latest_start_effective_date
    FROM {{ cld_params.Tables.ANESTHESIA_REFERENCE_PRICING.value }}
    WHERE year(start_effective_date) = {{ sub_version[:4] }}
),
{% endif %}

ipps AS (
    SELECT
        ipps.provider_id,
        msdrg,
        total_payment,
        state,
        pricing_type
    FROM {{ cld_params.Tables.IPPS_MEDICARE_REFERENCE_TABLE.value }} ipps
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} ps
        ON ipps.provider_id = ps.provider_id
    WHERE {% if historic_run %}start_effective_date = (SELECT latest_start_effective_date FROM latest_ipps_dates)
        AND{% else %}is_latest_start_effective_date = True
        AND{% endif %} pricing_priority = 1
),
ipps_aprdrg AS (
    SELECT 
        ipps.provider_id,
        apr_drg,
        state,
        'drg_crosswalked - ' || ipps.pricing_type as pricing_type,
        total_payment
    FROM {{ cld_params.Tables.APRDRG_XWALKED_IPPS_REFERENCE_TABLE.value }} ipps
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} ps
        ON CAST(ipps.provider_id AS VARCHAR) = ps.provider_id
),
opps AS (
    SELECT
        opps.provider_id,
        hcpcs,
        rate,
        state,
        pricing_type
    FROM {{ cld_params.Tables.OPPS_MEDICARE_REFERENCE_TABLE.value }} opps
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} ps
        ON opps.provider_id = ps.provider_id
    WHERE {% if historic_run %}start_effective_date = (SELECT latest_start_effective_date FROM latest_opps_dates)
        AND{% else %}is_latest_start_effective_date = True
        AND{% endif %} pricing_priority = 1
),

------------------------------
-- ASCs
------------------------------
asc_pricing_latest AS (
  SELECT *
  FROM {{ cld_params.Tables.ASC_MEDICARE_REFERENCE_TABLE.value }} asc
  WHERE {% if historic_run %}start_effective_date = (SELECT latest_start_effective_date FROM latest_asc_dates)
      AND{% else %}is_latest_start_effective_date = True
      AND{% endif %} pricing_priority = 1
  AND COALESCE(modifier, '') IN ('', 'TC')
),
asc_pricing_ranked AS (
  SELECT
      npi,
      hcpcs AS billing_code,
      rate,
      pricing_type,
      start_effective_date,
      ROW_NUMBER() OVER (
        PARTITION BY npi, hcpcs
        ORDER BY CASE WHEN COALESCE(modifier, '') = '' THEN 0 ELSE 1 END
      ) AS rn
  FROM asc_pricing_latest
),
asc_pricing_most_recent AS (
    SELECT
        npi,
        billing_code,
        rate,
        pricing_type,
        start_effective_date
    FROM asc_pricing_ranked
    WHERE rn = 1
), 
provider_spine AS (
    SELECT 
        t.npi, 
        provider_type,
        provider_id,
        state
    FROM 
        {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }},
        UNNEST(npi) AS t(npi)
),
ascs AS ( -- add provider_id to ASC pricing
    SELECT 
        b.provider_id
        , a.billing_code
        , b.state
        , ANY_VALUE(a.pricing_type) as pricing_type
        , max(a.rate) as rate -- if a provider_id has multiple prices, get the max
    FROM asc_pricing_most_recent AS a
    JOIN provider_spine b
        ON b.npi = a.npi
    WHERE b.provider_type = 'ASC'
    GROUP BY 1,2,3
),

------------------------------
-- Laboratories
------------------------------
laboratories AS (
    SELECT
        hcpcs AS billing_code,
        rate
    FROM {{ cld_params.Tables.CLINICAL_LAB_REFERENCE_PRICING.value }} lab
    WHERE {% if historic_run %}start_effective_date = (SELECT latest_start_effective_date FROM latest_clinical_lab_dates)
        AND{% else %}is_latest_start_effective_date = True
        AND{% endif %} modifier = ''
    AND rate > 0
),

------------------------------
-- ASP
------------------------------
asp AS (
    SELECT
        hcpcs,
        payment_limit
    FROM {{ cld_params.Tables.ASP_PRICING_TABLE.value }} asp
    {% if historic_run %}
    WHERE start_effective_date = (SELECT latest_start_effective_date FROM latest_asp_dates)
    {% else %}
    WHERE is_latest_start_effective_date = true
    {% endif %}
),

------------------------------
-- MPFS
------------------------------
mpfs_rates AS (
    SELECT
        state_code as state,
        hcpcs,
        ROUND(approx_percentile(facility_rate, 0.5), 2) as avg_facility_rate,
        ROUND(approx_percentile(non_facility_rate, 0.5), 2) as avg_non_facility_rate
    FROM {{ cld_params.Tables.MPFS_MEDICARE_REFERENCE_TABLE.value }} mpfs
    WHERE {% if historic_run %}start_effective_date = (SELECT latest_start_effective_date FROM latest_mpfs_dates)
        AND{% else %}is_latest_start_effective_date = True
        AND{% endif %} modifier = ''
    GROUP BY state_code, hcpcs
),

------------------------------
-- GEOGRAPHIC
------------------------------
ipps_geographic_avg AS (
    SELECT DISTINCT
        prov.state,
        prov.cbsa,
        ipps.msdrg,
        ROUND(approx_percentile(ipps.total_payment, 0.5) OVER (PARTITION BY ipps.msdrg), 2) as national_avg_total_payment,
        ROUND(approx_percentile(ipps.total_payment, 0.5) OVER (PARTITION BY CASE WHEN prov.cbsa = '99999' THEN prov.state || prov.cbsa ELSE prov.cbsa END, ipps.msdrg), 2) as cbsa_avg_total_payment,
        ROUND(approx_percentile(ipps.total_payment, 0.5) OVER (PARTITION BY prov.state, ipps.msdrg), 2) as state_avg_total_payment
    FROM ipps
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} prov
        ON ipps.provider_id = prov.provider_id
    WHERE ipps.pricing_type = 'provider_adjusted'
),
ipps_aprdrg_geographic_avg AS (
    SELECT DISTINCT
        prov.state,
        prov.cbsa,
        ipps_aprdrg.apr_drg,
        approx_percentile(ipps_aprdrg.total_payment, 0.5) OVER (PARTITION BY ipps_aprdrg.apr_drg) as national_avg_total_payment,
        approx_percentile(ipps_aprdrg.total_payment, 0.5) OVER (PARTITION BY CASE WHEN prov.cbsa = '99999' THEN prov.state || prov.cbsa ELSE prov.cbsa END, ipps_aprdrg.apr_drg) as cbsa_avg_total_payment,
        approx_percentile(ipps_aprdrg.total_payment, 0.5) OVER (PARTITION BY prov.state, ipps_aprdrg.apr_drg) as state_avg_total_payment
    FROM ipps_aprdrg
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} prov
        ON ipps_aprdrg.provider_id = prov.provider_id
    WHERE ipps_aprdrg.pricing_type = 'drg_crosswalked - provider_adjusted'
),
opps_geographic_avg AS (
    SELECT DISTINCT
        prov.state,
        prov.cbsa,
        opps.hcpcs,
        ROUND(approx_percentile(opps.rate, 0.5) OVER (PARTITION BY opps.hcpcs), 2) as national_avg_rate,
        ROUND(approx_percentile(opps.rate, 0.5) OVER (PARTITION BY CASE WHEN prov.cbsa = '99999' THEN prov.state || prov.cbsa ELSE prov.cbsa END, opps.hcpcs), 2) as cbsa_avg_rate,
        ROUND(approx_percentile(opps.rate, 0.5) OVER (PARTITION BY prov.state, opps.hcpcs), 2) as state_avg_rate
    FROM opps
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} prov
        ON opps.provider_id = prov.provider_id
    WHERE opps.pricing_type = 'provider_adjusted'
),
ascs_geographic_avg AS (
    SELECT DISTINCT
        prov.state,
        prov.cbsa,
        ascs.billing_code,
        ROUND(approx_percentile(ascs.rate, 0.5) OVER (PARTITION BY ascs.billing_code), 2) as national_avg_rate,
        ROUND(approx_percentile(ascs.rate, 0.5) OVER (PARTITION BY CASE WHEN prov.cbsa = '99999' THEN prov.state || prov.cbsa ELSE prov.cbsa END, ascs.billing_code), 2) as cbsa_avg_rate,
        ROUND(approx_percentile(ascs.rate, 0.5) OVER (PARTITION BY prov.state, ascs.billing_code), 2) as state_avg_rate,
        ROUND(approx_percentile(ascs.rate, 0.5) OVER (PARTITION BY ascs.billing_code), 2) as asc_avg_medicare_rate
    FROM ascs
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} prov
        ON ascs.provider_id = prov.provider_id
    WHERE ascs.pricing_type = 'provider_adjusted'
),
lab_geographic_avg AS (
    SELECT 
        billing_code,
        approx_percentile(rate, 0.5) as lab_avg_medicare_rate,
        approx_percentile(rate, 0.5) as national_avg_rate
    FROM laboratories
    GROUP BY 1
),
mpfs_geographic_avg AS (
    SELECT DISTINCT
        prov.state,
        mpfs_facility.hcpcs,
        mpfs_facility.facility,
        approx_percentile(mpfs_facility.state_avg_rate, 0.5) OVER (PARTITION BY mpfs_facility.hcpcs, mpfs_facility.facility) as national_avg_rate,
        mpfs_facility.state_avg_rate as state_avg_rate
    FROM (
        SELECT 
            state,
            hcpcs,
            true as facility,
            avg_facility_rate as state_avg_rate
        FROM mpfs_rates
        UNION ALL
        SELECT 
            state,
            hcpcs,
            false as facility,
            avg_non_facility_rate as state_avg_rate
        FROM mpfs_rates
    ) mpfs_facility
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} prov
        ON mpfs_facility.state = prov.state
),
anesthesia_geographic_avg AS (
    SELECT DISTINCT
        prov.state,
        anesthesia_cf_rates.billing_code,
        approx_percentile(anesthesia_cf_rates.cf, 0.5) OVER (PARTITION BY anesthesia_cf_rates.billing_code) as national_avg_rate,
        anesthesia_cf_rates.cf as state_avg_rate
    FROM (
        SELECT
            state_code as state,
            hcpcs as billing_code,
            approx_percentile(conversion_factor, 0.5) as cf
        FROM {{ cld_params.Tables.ANESTHESIA_REFERENCE_PRICING.value }} anesthesia
        WHERE {% if historic_run %}start_effective_date = (SELECT latest_start_effective_date FROM latest_anesthesia_dates){% else %}is_latest_start_effective_date = True{% endif %}
        GROUP BY 1,2
    ) anesthesia_cf_rates
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} prov
        ON anesthesia_cf_rates.state = prov.state
),

------------------------------
-- CLAIMS
------------------------------
{%- for geo in geos %}
{{ geo }}_level_bm AS (
    SELECT
        {% if geo  == 'provider_id' %} 
        COALESCE(CAST(benchmarks.provider_id as VARCHAR), xwalk.provider_id) as provider_id,
        {% else %}
        {{ geo }},
        {% endif %}
        case
            when benchmarks.taxonomy_code in (
                    '261QA1903X' -- Ambulatory Surgical Clinic/Center
                    ,'261QE0800X' -- Endoscopy Clinic/Center
                    ,'261QS0132X' -- Ophthalmologic Surgery Clinic/Center
                    ,'261QS0112X' -- Oral and Maxillofacial Surgery Clinic/Center
                )
                AND npi_source = 'hco' 
                then 'ASC'
            when benchmarks.taxonomy_code in (
                    '261QR0200X' -- Radiology Clinic/Center
                    ,'2085R0202X' -- Diagnostic Radiology Physician
                    ,'261QM1200X' -- Magnetic Resonance Imaging (MRI) Clinic/Center
                    ,'261QR0206X' -- Mammography Clinic/Center
                    ,'335V00000X' -- Portable X-ray and/or Other Portable Diagnostic Imaging Supplier
                    ,'261QR0207X' -- Mobile Mammography Clinic/Center
                )
                AND npi_source = 'hco' 
                then 'Imaging Center'
            when benchmarks.taxonomy_code in (
                '282N00000X' -- General Acute Care Hospital
                )
                AND npi_source = 'hco' 
                then 'Short Term Acute Care Hospital'
            when benchmarks.taxonomy_code in (
                    '282NC0060X' -- Critical Access Hospital
                    ,'261QC0050X' -- Critical Access Hospital Clinic/Center
                )
                AND npi_source = 'hco' 
                then 'Critical Access Hospital'
            when benchmarks.taxonomy_code in (
                    '282NC2000X' -- Children's Hospital
                )
                AND npi_source = 'hco' 
                then 'Childrens Hospital'
            when benchmarks.taxonomy_code in (
                    '283X00000X' -- Rehabilitation Hospital
                )
                AND npi_source = 'hco' 
                then 'Rehabilitation Hospital'
            when benchmarks.taxonomy_grouping in (
                    'Allopathic & Osteopathic Physicians'
                )
                AND npi_source IN ('hco', 'hcp') 
                then 'Physician Group'
            else NULL
        end as provider_type,
        tq_payer_id,
        billing_code_type,
        billing_code,
        CASE
            WHEN claim_type_code = 'institutional'
                AND setting = 'Inpatient'
                THEN 'Inpatient'
            WHEN claim_type_code = 'institutional'
                AND setting = 'Outpatient'
                THEN 'Outpatient'
            WHEN claim_type_code = 'professional'
                THEN 'Professional'
        END AS bill_type,
        
        {%- for perc, perc_name in percentiles %}
            SUM({{ perc_name }}) FILTER (
                WHERE billing_code_ranking = 'primary' 
                AND allowed_amount_type = 'claim'
                AND count_encounters > 1
            ) AS percentile_{{ perc }}_primary_claim,

            SUM({{ perc_name }}) FILTER (
                WHERE billing_code_ranking = 'primary' 
                AND allowed_amount_type = 'line_item'
                AND count_encounters > 1
            ) AS percentile_{{ perc }}_primary_line_item,

            SUM({{ perc_name }}) FILTER (
                WHERE billing_code_ranking = 'secondary' 
                AND allowed_amount_type = 'line_item'
                AND count_encounters > 1
            ) AS percentile_{{ perc }}_secondary_line_item,

        {%- endfor %}
        SUM(count_encounters) FILTER (
            WHERE allowed_amount_type = 'line_item'
        ) as count_encounters
    FROM tq_intermediate.claims_benchmarks.{{ geo_table_map[geo] }} benchmarks
    {% if geo  == 'provider_id' %}
    JOIN provider_spine xwalk
        ON xwalk.npi = benchmarks.npi
    {% endif %}
    WHERE payer_channel = 'Commercial'
        AND billing_code_modifier IS NULL
        AND claim_type_code IN ('institutional', 'professional')
        AND median_allowed_amount IS NOT NULL
        {% if 'custom' in schema_name %}
            AND service_year = 2025
        {% elif 'test' not in sub_version %}
            AND service_year = {{ sub_version[:4] }}
        {% else %}
            AND service_year = 2025
        {% endif %}
    GROUP BY 1, 2, 3, 4, 5, 6
){{ ',' if not loop.last else '' }}
{%- endfor %}


------------------------------
-- FINAL BENCHMARKS TABLE
------------------------------
SELECT 
    ros.roid,
    ros.network_id,
    ros.provider_id,
    prov.provider_type, 
    ros.bill_type,
    ros.billing_code,
    ros.billing_code_type,
    ros.is_plausible,
    prov.cbsa,

    CASE 
        WHEN asp.payment_limit IS NOT NULL
            THEN NULL
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'MS-DRG'
            THEN ipps.pricing_type
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'APR-DRG'
            THEN ipps_aprdrg.pricing_type
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Outpatient'
            THEN opps.pricing_type
        WHEN ros.provider_type = 'ASC'
            THEN ascs.pricing_type
        ELSE NULL
    END as medicare_pricing_type,

    CASE 
        -- ASP
        WHEN asp.payment_limit IS NOT NULL 
            THEN asp.payment_limit
        -- Hospital Inpatient
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'MS-DRG'
            THEN ipps.total_payment
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'APR-DRG'
            THEN ipps_aprdrg.total_payment
        -- Hospital Outpatient
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Outpatient'
            THEN COALESCE(opps.rate, laboratories.rate, mpfs_geographic_avg.state_avg_rate)
        -- ASC
        WHEN ros.provider_type = 'ASC'
            THEN COALESCE(ascs.rate, laboratories.rate, mpfs_geographic_avg.state_avg_rate)
        -- Labs
        WHEN ros.provider_type = 'Laboratory'
            THEN laboratories.rate
        -- Professional
        WHEN ros.bill_type = 'Professional'
            THEN COALESCE(mpfs_geographic_avg.state_avg_rate, anesthesia_geographic_avg.state_avg_rate)
        ELSE NULL
    END as medicare_rate,

    CASE 
        -- ASP
        WHEN asp.payment_limit IS NOT NULL
            THEN 'ASP'
        -- Hospital Inpatient
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'MS-DRG' AND ipps.total_payment IS NOT NULL
            THEN 'IPPS'
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'APR-DRG' AND ipps_aprdrg.total_payment IS NOT NULL
            THEN 'IPPS XWALKED APR-DRG'
        -- Hospital Outpatient
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Outpatient' AND opps.rate IS NOT NULL
            THEN 'OPPS'
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Outpatient' AND laboratories.rate IS NOT NULL
            THEN 'Clinical Lab'
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Outpatient' AND mpfs_geographic_avg.state_avg_rate IS NOT NULL
            THEN 'MPFS'
        -- ASC
        WHEN ros.provider_type = 'ASC' AND ascs.rate IS NOT NULL
            THEN 'ASC'
        WHEN ros.provider_type = 'ASC' AND laboratories.rate IS NOT NULL
            THEN 'Clinical Lab'
        WHEN ros.provider_type = 'ASC' AND mpfs_geographic_avg.state_avg_rate IS NOT NULL
            THEN 'MPFS'
        -- Labs
        WHEN ros.provider_type = 'Laboratory' AND laboratories.rate IS NOT NULL
            THEN 'Clinical Lab'
        -- Professional
        WHEN ros.bill_type = 'Professional' AND mpfs_geographic_avg.state_avg_rate IS NOT NULL
            THEN 'MPFS'
        WHEN ros.bill_type = 'Professional' AND anesthesia_geographic_avg.state_avg_rate IS NOT NULL
            THEN 'Anesthesia'
        ELSE NULL 
    END as medicare_reference_source,

    CASE 
        -- ASP
        WHEN asp.payment_limit IS NOT NULL
            THEN asp.payment_limit
        -- Hospital Inpatient
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'MS-DRG'
            THEN ipps_geographic_avg.state_avg_total_payment
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'APR-DRG'
            THEN ipps_aprdrg_geographic_avg.state_avg_total_payment
        -- Hospital Outpatient
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Outpatient'
            THEN COALESCE(opps_geographic_avg.state_avg_rate, laboratories.rate, mpfs_geographic_avg.state_avg_rate)
        -- ASC
        WHEN ros.provider_type = 'ASC'
            THEN COALESCE(ascs_geographic_avg.state_avg_rate, laboratories.rate, mpfs_geographic_avg.state_avg_rate)
        -- Labs
        WHEN ros.provider_type = 'Laboratory'
            THEN laboratories.rate
        -- Professional
        WHEN ros.bill_type = 'Professional'
            THEN COALESCE(mpfs_geographic_avg.state_avg_rate, anesthesia_geographic_avg.state_avg_rate)
        ELSE NULL
    END as state_avg_medicare_rate,

    CASE 
        -- ASP
        WHEN asp.payment_limit IS NOT NULL
            THEN asp.payment_limit
        -- Hospital Inpatient
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'MS-DRG'
            THEN ipps_geographic_avg.national_avg_total_payment
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'APR-DRG'
            THEN ipps_aprdrg_geographic_avg.national_avg_total_payment
        -- Hospital Outpatient
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Outpatient'
            THEN COALESCE(opps_geographic_avg.national_avg_rate, lab_geographic_avg.national_avg_rate, mpfs_geographic_avg.national_avg_rate)
        -- ASC
        WHEN ros.provider_type = 'ASC'
            THEN COALESCE(ascs_geographic_avg.national_avg_rate, lab_geographic_avg.national_avg_rate, mpfs_geographic_avg.national_avg_rate)
        -- Labs
        WHEN ros.provider_type = 'Laboratory'
            THEN lab_geographic_avg.national_avg_rate
        -- Professional
        WHEN ros.bill_type = 'Professional'
            THEN COALESCE(mpfs_geographic_avg.national_avg_rate, anesthesia_geographic_avg.national_avg_rate)
        ELSE NULL
    END as national_avg_medicare_rate,

    CASE 
        -- ASP
        WHEN asp.payment_limit IS NOT NULL
            THEN asp.payment_limit
        -- Hospital Inpatient
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'MS-DRG'
            THEN ipps_geographic_avg.cbsa_avg_total_payment
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'APR-DRG'
            THEN ipps_aprdrg_geographic_avg.cbsa_avg_total_payment
        -- Hospital Outpatient
        WHEN ros.provider_type LIKE '%Hospital%' AND ros.bill_type = 'Outpatient'
            THEN COALESCE(opps_geographic_avg.cbsa_avg_rate, laboratories.rate, mpfs_geographic_avg.state_avg_rate)
        -- ASC
        WHEN ros.provider_type = 'ASC'
            THEN COALESCE(ascs_geographic_avg.cbsa_avg_rate, laboratories.rate, mpfs_geographic_avg.state_avg_rate)
        -- Labs
        WHEN ros.provider_type = 'Laboratory'
            THEN lab_geographic_avg.national_avg_rate
        -- Professional
        WHEN ros.bill_type = 'Professional'
            THEN COALESCE(mpfs_geographic_avg.state_avg_rate, anesthesia_geographic_avg.state_avg_rate)
        ELSE NULL
    END as cbsa_avg_medicare_rate,

    asp.payment_limit as asp_payment_limit,

    ascs_geographic_avg.asc_avg_medicare_rate,
    
    lab_geographic_avg.lab_avg_medicare_rate,

    {%- for geo in geos %}
        {%- for perc, _ in percentiles %}
        km_{{ geo }}.percentile_{{ perc }}_primary_claim as {{ geo_name_map[geo] }}_{{ perc }}_primary_claim,
        km_{{ geo }}.percentile_{{ perc }}_primary_line_item as {{ geo_name_map[geo] }}_{{ perc }}_primary_line_item,
        km_{{ geo }}.percentile_{{ perc }}_secondary_line_item as {{ geo_name_map[geo] }}_{{ perc }}_secondary_line_item,
        {%- endfor %}
    km_{{ geo }}.count_encounters as {{ geo_name_map[geo] }}_count_encounters{{ ',' if not loop.last else '' }}
    {%- endfor %},
    ros.payer_id
FROM {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} ros
LEFT JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} prov
    ON ros.provider_id = prov.provider_id
LEFT JOIN ipps
    ON ros.provider_id = ipps.provider_id
    AND ros.billing_code = ipps.msdrg
    AND ros.billing_code_type = 'MS-DRG'
    AND ros.bill_type = 'Inpatient'
LEFT JOIN ipps_aprdrg
    ON ros.provider_id = ipps_aprdrg.provider_id
    AND ros.billing_code = ipps_aprdrg.apr_drg
    AND ros.billing_code_type = 'APR-DRG'
    AND ros.bill_type = 'Inpatient'
LEFT JOIN opps
    ON ros.provider_id = opps.provider_id
    AND ros.billing_code = opps.hcpcs
    AND ros.billing_code_type = 'HCPCS'
    AND ros.bill_type = 'Outpatient'
LEFT JOIN ascs
    ON ros.provider_id = ascs.provider_id
    AND ros.billing_code = ascs.billing_code
    AND ros.billing_code_type = 'HCPCS'
    AND ros.bill_type = 'Outpatient'
LEFT JOIN laboratories
    ON ros.billing_code = laboratories.billing_code
    AND ros.billing_code_type = 'HCPCS'
    AND ros.bill_type = 'Professional'
LEFT JOIN ipps_geographic_avg
    ON prov.state = ipps_geographic_avg.state
    AND prov.cbsa = ipps_geographic_avg.cbsa
    AND ros.billing_code = ipps_geographic_avg.msdrg
    AND ros.billing_code_type = 'MS-DRG'
    AND ros.bill_type = 'Inpatient'
LEFT JOIN ipps_aprdrg_geographic_avg
    ON prov.state = ipps_aprdrg_geographic_avg.state
    AND prov.cbsa = ipps_aprdrg_geographic_avg.cbsa
    AND ros.billing_code = ipps_aprdrg_geographic_avg.apr_drg
    AND ros.billing_code_type = 'APR-DRG'
    AND ros.bill_type = 'Inpatient'
LEFT JOIN opps_geographic_avg
    ON prov.state = opps_geographic_avg.state
    AND prov.cbsa = opps_geographic_avg.cbsa
    AND ros.billing_code = opps_geographic_avg.hcpcs
    AND ros.billing_code_type = 'HCPCS'
    AND ros.bill_type = 'Outpatient'
LEFT JOIN ascs_geographic_avg
    ON prov.state = ascs_geographic_avg.state
    AND prov.cbsa = ascs_geographic_avg.cbsa
    AND ros.billing_code = ascs_geographic_avg.billing_code
    AND ros.billing_code_type = 'HCPCS'
    AND ros.bill_type = 'Outpatient'
LEFT JOIN lab_geographic_avg
    ON ros.billing_code = lab_geographic_avg.billing_code
    AND ros.billing_code_type = 'HCPCS'
    AND ros.bill_type = 'Professional'
LEFT JOIN asp
    ON ros.billing_code = asp.hcpcs
    AND ros.billing_code_type = 'HCPCS'
    AND ros.bill_type = 'Outpatient'
-- MPFS GEOGRAPHIC AVERAGES
LEFT JOIN mpfs_geographic_avg
    ON prov.state = mpfs_geographic_avg.state
    AND ros.billing_code = mpfs_geographic_avg.hcpcs
    AND ros.billing_code_type = 'HCPCS'
    AND (
        (
            ros.facility = mpfs_geographic_avg.facility
            AND ros.bill_type = 'Professional'
        )
        OR (
            mpfs_geographic_avg.facility = True
            AND ros.bill_type = 'Outpatient'
        )
    )
-- ANESTHESIA GEOGRAPHIC AVERAGES
LEFT JOIN anesthesia_geographic_avg
    ON prov.state = anesthesia_geographic_avg.state
    AND ros.billing_code = anesthesia_geographic_avg.billing_code
    AND ros.billing_code_type = 'HCPCS'
    AND ros.bill_type = 'Professional'
{%- for geo in geos %}
LEFT JOIN {{ geo }}_level_bm km_{{ geo }}
    ON prov.{{ geo_name_map[geo] }} = km_{{ geo }}.{{ geo }}
    AND prov.provider_type = km_{{ geo }}.provider_type
    AND ros.payer_id = km_{{ geo }}.tq_payer_id
    AND ros.bill_type = km_{{ geo }}.bill_type
    AND ros.billing_code_type = km_{{ geo }}.billing_code_type
    AND ros.billing_code = km_{{ geo }}.billing_code
{%- endfor %}