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','"percentile_30th"'),
    ('50','"median_allowed_amount"'),
    ('70','"percentile_70th"')
] %}
-- {%- set geos = ['provider_id', 'cbsa', 'zip3_code', 'state'] %}
{%- set geos = ['state'] %}
{%- set geo_name_map = {
    'state': 'state'
} %}
{%- set geo_table_map = {
    'state': 'claims_benchmarks_allowable_state_payerchannel'
} %}

CREATE OR REPLACE 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,
        ROUND(total_payment, 2) as 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,
        ROUND(total_payment, 2) as 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,
        ROUND(rate, 2) as 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
        , ROUND(max(a.rate), 2) 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,
        ROUND(rate, 2) as 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,
        ROUND(payment_limit, 2) as 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(
            CASE WHEN ipps.pricing_type = 'provider_adjusted' THEN ipps.total_payment ELSE NULL END,
            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(
            CASE WHEN ipps.pricing_type = 'provider_adjusted' THEN ipps.total_payment ELSE NULL END,
            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
),
ipps_aprdrg_geographic_avg AS (
    SELECT DISTINCT
        prov.state,
        prov.cbsa,
        ipps_aprdrg.apr_drg,
        ROUND(approx_percentile(
            CASE WHEN ipps_aprdrg.pricing_type = 'drg_crosswalked - provider_adjusted' THEN ipps_aprdrg.total_payment ELSE NULL END,
            0.5
        ) OVER (PARTITION BY ipps_aprdrg.apr_drg), 2) as national_avg_total_payment,
        ROUND(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), 2) as cbsa_avg_total_payment,
        ROUND(approx_percentile(
            CASE WHEN ipps_aprdrg.pricing_type = 'drg_crosswalked - provider_adjusted' THEN ipps_aprdrg.total_payment ELSE NULL END,
            0.5
        ) OVER (PARTITION BY prov.state, ipps_aprdrg.apr_drg), 2) 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
),
opps_geographic_avg AS (
    SELECT DISTINCT
        prov.state,
        prov.cbsa,
        opps.hcpcs,
        ROUND(approx_percentile(
            CASE WHEN opps.pricing_type = 'provider_adjusted' THEN opps.rate ELSE NULL END, 
            0.5
        ) OVER (PARTITION BY opps.hcpcs), 2) as national_avg_rate,
        ROUND(approx_percentile(
            CASE WHEN opps.pricing_type = 'provider_adjusted' THEN opps.rate ELSE NULL END, 
            0.5
        ) OVER (PARTITION BY prov.state, opps.hcpcs), 2) as state_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
    FROM opps
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} prov
        ON opps.provider_id = prov.provider_id
),
ascs_geographic_avg AS (
    SELECT DISTINCT
        prov.state,
        prov.cbsa,
        ascs.billing_code,
        ROUND(approx_percentile(
            CASE WHEN ascs.pricing_type = 'provider_adjusted' THEN ascs.rate ELSE NULL END,
            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(
            CASE WHEN ascs.pricing_type = 'provider_adjusted' THEN ascs.rate ELSE NULL END,
            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
),
lab_geographic_avg AS (
    SELECT 
        billing_code,
        ROUND(approx_percentile(rate, 0.5), 2) as lab_avg_medicare_rate,
        ROUND(approx_percentile(rate, 0.5), 2) as national_avg_rate
    FROM laboratories
    GROUP BY 1
),
mpfs_geographic_avg AS (
    SELECT DISTINCT
        prov.state,
        mpfs_facility.hcpcs,
        mpfs_facility.facility,
        ROUND(approx_percentile(mpfs_facility.state_avg_rate, 0.5) OVER (PARTITION BY mpfs_facility.hcpcs, mpfs_facility.facility), 2) as national_avg_rate,
        ROUND(mpfs_facility.state_avg_rate, 2) 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,
        ROUND(approx_percentile(anesthesia_cf_rates.cf, 0.5) OVER (PARTITION BY anesthesia_cf_rates.billing_code), 2) as national_avg_rate,
        ROUND(anesthesia_cf_rates.cf, 2) 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 'Hospital'
            when benchmarks.taxonomy_code in (
                    '282NC0060X' -- Critical Access Hospital
                    ,'261QC0050X' -- Critical Access Hospital Clinic/Center
                )
                AND npi_source = 'hco' 
                then 'Hospital'
            when benchmarks.taxonomy_code in (
                    '282NC2000X' -- Children's Hospital
                )
                AND npi_source = 'hco' 
                then 'Hospital'
            when benchmarks.taxonomy_code in (
                    '283X00000X' -- Rehabilitation Hospital
                )
                AND npi_source = 'hco' 
                then 'Hospital'
            when benchmarks.taxonomy_grouping in (
                    'Allopathic & Osteopathic Physicians'
                )
                AND npi_source IN ('hco', 'hcp') 
                then 'Physician Group'
            else NULL
        end as provider_type,
        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
        AND service_year > 2023
        AND count_encounters > 11
    GROUP BY 1, 2, 3, 4, 5
),
{%- endfor %}


------------------------------
-- FINAL BENCHMARKS TABLE
------------------------------
final AS (
    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 = 'Hospital' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'MS-DRG'
                THEN ipps.pricing_type
            WHEN ros.provider_type = 'Hospital' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'APR-DRG'
                THEN ipps_aprdrg.pricing_type
            WHEN ros.provider_type = '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 = 'Hospital' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'MS-DRG'
                THEN ipps.total_payment
            WHEN ros.provider_type = 'Hospital' AND ros.bill_type = 'Inpatient' AND ros.billing_code_type = 'APR-DRG'
                THEN ipps_aprdrg.total_payment
            -- Hospital Outpatient
            WHEN ros.provider_type = '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, laboratories.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 = '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 = '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 = 'Hospital' AND ros.bill_type = 'Outpatient' AND opps.rate IS NOT NULL
                THEN 'OPPS'
            WHEN ros.provider_type = 'Hospital' AND ros.bill_type = 'Outpatient' AND laboratories.rate IS NOT NULL
                THEN 'Clinical Lab'
            WHEN ros.provider_type = '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 laboratories.rate IS NOT NULL
                THEN 'Clinical Lab'
            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 = '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 = '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 = '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, laboratories.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 = '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 = '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 = '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, lab_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 = '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 = '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 = '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, lab_geographic_avg.national_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,
            CASE
                WHEN cs.is_surg_code = True OR ros.bill_type = 'Inpatient' THEN km_{{ geo }}.percentile_{{ perc }}_primary_claim
                ELSE km_{{ geo }}.percentile_{{ perc }}_primary_line_item
            END as {{ geo_name_map[geo] }}_{{ perc }}_primary,
            {%- 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 (
        SELECT DISTINCT billing_code, is_surg_code
        FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }}
    ) cs
        ON ros.billing_code = cs.billing_code
    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'
    LEFT JOIN ipps_geographic_avg
        ON prov.state = ipps_geographic_avg.state
        AND COALESCE(prov.cbsa, '') = COALESCE(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 COALESCE(prov.cbsa, '') = COALESCE(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 COALESCE(prov.cbsa, '') = COALESCE(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 COALESCE(prov.cbsa, '') = COALESCE(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'
    LEFT JOIN asp
        ON ros.billing_code = asp.hcpcs
        AND ros.billing_code_type = 'HCPCS'
    -- 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.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 %}
),

deduped AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY roid
            ORDER BY
                CASE WHEN medicare_rate IS NOT NULL THEN 0 ELSE 1 END,
                CASE WHEN state_avg_medicare_rate IS NOT NULL THEN 0 ELSE 1 END,
                CASE WHEN national_avg_medicare_rate IS NOT NULL THEN 0 ELSE 1 END
        ) AS rn
    FROM final
)

SELECT
    roid,
    network_id,
    provider_id,
    provider_type,
    bill_type,
    billing_code,
    billing_code_type,
    is_plausible,
    cbsa,
    medicare_pricing_type,
    medicare_rate,
    medicare_reference_source,
    state_avg_medicare_rate,
    national_avg_medicare_rate,
    cbsa_avg_medicare_rate,
    asp_payment_limit,
    asc_avg_medicare_rate,
    lab_avg_medicare_rate,
    {%- for geo in geos %}
        {%- for perc, _ in percentiles %}
        {{ geo_name_map[geo] }}_{{ perc }}_primary_claim,
        {{ geo_name_map[geo] }}_{{ perc }}_primary_line_item,
        {{ geo_name_map[geo] }}_{{ perc }}_secondary_line_item,
        {{ geo_name_map[geo] }}_{{ perc }}_primary,
        {%- endfor %}
    {{ geo_name_map[geo] }}_count_encounters{{ ',' if not loop.last else '' }}
    {%- endfor %},
    payer_id
FROM deduped
WHERE rn = 1