final_rates

final_rates

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

--------------------------------
-- Purpose:
-- This table combines facility and professional CLD enhanced tables to create final rates for the pricing service

{% set base_columns = [
    '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', 
    'billing_code', 
    'billing_code_type', 
    'average_units', 
    'anesthesia_unit_conversion',
    'billing_code_description', 
    'validated_rate', 
    'validated_prof_fee_rate', 
    'claims_validated_rate', 
    'commercial_validated_reimbursement_rate', 
    'commercial_validated_cash_rate', 
    'state_avg_negotiated_rate_used',
    'state_avg_discounted_cash_rate_used',
    'negotiated_rate', 
    'discounted_cash_rate',
    'state_avg_medicare_rate',
    'lower_bound',
    'upper_bound',
    'contract_methodology', 
    'gross_charge', 
    'source', 
] %}

-- TO-DO: August 2025 - Keep Legacy Payer IDs and Plan Alias for Consumer Prod Eng; Remove once ready
{% set legacy_plan_names = [
    {"payer_id": 229, "network_name_old": "OMNIA", "network_name_new": "PPO"},
    {"payer_id": 7,   "network_name_old": "POS", "network_name_new": "Open Access Managed Choice"},
    {"payer_id": 7,   "network_name_old": "POS", "network_name_new": "EPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "GA OPEN ACCESS"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "ME Blue Choice PPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "IN Blue Access PPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "WI Blue Access PPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "NV Choice PPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "OH PPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "VA Keycare PPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "KY PPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "NH OPEN ACCESS"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "Blue Access Gated EPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "NY PPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "CO Blue Preferred PPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "CA Blue Cross PPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "EPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "CT Century Preferred PPO"},
    {"payer_id": 42,  "network_name_old": "PPO", "network_name_new": "MO Blue Access PPO"},
    {"payer_id": 403, "network_name_old": "PPO", "network_name_new": "Group PPO"},
    {"payer_id": 44,  "network_name_old": "PPO", "network_name_new": "Preferred Provider Network (PPO)"},
    {"payer_id": 388, "network_name_old": "PPO", "network_name_new": "BlueChoice PPO"},
    {"payer_id": 56,  "network_name_old": "PPO", "network_name_new": "KHPE Commercial HMO/POS"},
    {"payer_id": 56,  "network_name_old": "PPO", "network_name_new": "QCC Commercial PPO/EPO"},
    {"payer_id": 628, "network_name_old": "PPO", "network_name_new": "Alaska Heritage Network"},
    {"payer_id": 628, "network_name_old": "PPO", "network_name_new": "PBC PPO (Heritage) Network"},
    {"payer_id": 389, "network_name_old": "PPO", "network_name_new": "PPO Participating Provider Options"},
    {"payer_id": 169, "network_name_old": "PPO", "network_name_new": "Blue Choice PPO"},
    {"payer_id": 61,  "network_name_old": "PPO", "network_name_new": "WA PPO"},
    {"payer_id": 61,  "network_name_old": "PPO", "network_name_new": "OR PPO"},
    {"payer_id": 61,  "network_name_old": "PPO", "network_name_new": "ID PPO"},
    {"payer_id": 61,  "network_name_old": "PPO", "network_name_new": "UT PPO"},
    {"payer_id": 101, "network_name_old": "PPO", "network_name_new": "Excellus Bluecross Blueshield"}
] %}

{% set join_keys = ['sub_package_id', 'billing_code', 'fee_type', 'is_package_base_code'] %}

CREATE TABLE {{ ps_schema }}.pricing_service_rates_{{ ps_month }} 
WITH (
    PARTITIONING = ARRAY['payer_id']
)
AS
WITH

--------------------------------
-- Combine Facility and Professional CLD Enhanced Tables
--------------------------------
df AS (
    SELECT 
        {% for col in base_columns -%}
            {% if col in ['state_avg_negotiated_rate_used', 'state_avg_discounted_cash_rate_used'] -%}
                CAST(NULL AS Boolean) as {{ col }} ,
            {% else -%}
                {{ col }} ,
            {% endif -%}
        {% endfor -%}
        NULL
    FROM {{ ps_schema }}.clear_rates_enhanced_{{ ps_month }}
    WHERE provider_type != 'Physician Group'
    UNION ALL
    SELECT 
        {% for col in base_columns -%}
            {{ col }},
        {% endfor -%}
        NULL
    FROM {{ ps_schema }}.professional_cld_enhanced_{{ ps_month }}
),

--------------------------------
-- Allocations:

-- Consumer Cost Share requires non-null line item prices for non-base-code facility fees.
-- We use the line item's average expected share of the total sub-package price to allocate the sub-package price down to line items.
--------------------------------
avg_prices AS (
    SELECT 
        sub_package_id, 
        billing_code, 
        fee_type,
        is_package_base_code,
        avg(average_units * anesthesia_unit_conversion * negotiated_rate) as avg_price
    FROM df
    WHERE fee_type = 'facility'
    AND is_package_base_code = 0
    GROUP BY 1,2,3,4
),
allocations AS (
    SELECT
        sub_package_id,
        billing_code,
        fee_type,
        is_package_base_code,
        CAST(avg_price / (SUM(avg_price) OVER(PARTITION BY sub_package_id)) AS DECIMAL(10,3)) as allocation
    FROM avg_prices
),

-- for base codes, we only want the case rate and not the allocated price
case_rates AS (
    SELECT
        distinct
        payer_id,
        network_id,
        provider_id,
        sub_package_id,
        fee_type,
        negotiated_rate as case_rate
    FROM df
    WHERE fee_type = 'facility'
    AND is_package_base_code = 1
),

--------------------------------
-- Validation Indicators at the Sub-Package Level (Base Code is Validated)
--------------------------------
validated AS (
    SELECT 
        distinct
        ssp_id,
        sub_package_id,
        payer_id,
        provider_id,
        network_id,
        True as validated_subpackage
    FROM df
    WHERE
    (
        validated_rate = True OR 
        claims_validated_rate = True OR
        commercial_validated_reimbursement_rate = True
    )
    AND negotiated_rate > 1
    AND is_package_base_code = 1
    AND (
        fee_type = 'facility'
        OR
        (fee_type = 'professional' AND provider_type = 'Imaging Center')
    )
),

--------------------------------
-- Clear Rates doesn't populate metadata columns where canonical_rate is NULL (e.g. payer_name, billing_code_description)
-- Since Consumer Pricing Service performs additional imputations, we need to add these back in for essential fields
--------------------------------
network_name AS (
    SELECT distinct network_id, network_name, payer_name
    FROM {{ ps_schema }}.clear_rates_enhanced_{{ ps_month }}
    WHERE network_name IS NOT NULL
),
code_descriptions AS (
    SELECT distinct billing_code, billing_code_type, ANY_VALUE(billing_code_description) as billing_code_description
    FROM {{ ps_schema }}.clear_rates_enhanced_{{ ps_month }}
    WHERE billing_code_description IS NOT NULL
    GROUP BY 1,2
),

--------------------------------
-- Final Output
--------------------------------
output AS (
    SELECT 
        
        from_big_endian_64(xxhash64(CAST(
            df.ssp_id || 
            df.sub_package_id ||
            CAST(df.provider_id as VARCHAR) ||
            CAST(df.payer_id as VARCHAR) ||
            CAST(df.network_id as VARCHAR)
            AS varbinary
        ))) AS network_provider_sub_package_id,

        from_big_endian_64(xxhash64(CAST(
            df.ssp_id || 
            CAST(df.provider_id as VARCHAR) ||
            CAST(df.payer_id as VARCHAR) ||
            CAST(df.network_id as VARCHAR)
            AS varbinary
        ))) AS network_provider_ssp_id,

        {% for col in base_columns -%}
            {% if col in ['network_name', 'payer_name'] -%}
                COALESCE(df.{{ col }}, nn.{{ col }}) AS {{ col }},
            {% elif col == 'billing_code_description' -%}
                COALESCE(df.{{ col }}, cd.{{ col }}) AS {{ col }},
            {% else -%}
                df.{{ col }} AS {{ col }},
            {% endif -%}
        {% endfor -%}

        -- TO-DO: August 2025 - Keep Legacy Payer IDs and Plan Alias for Consumer Prod Eng; Remove once ready
        CASE
            WHEN df.payer_id = '7' THEN 7
            WHEN df.payer_id = '76' THEN 9
            WHEN df.payer_id = '643' THEN 95
            WHEN df.payer_id = '42' THEN 21
            WHEN df.payer_id = '169' THEN 266
            WHEN df.payer_id = '389' THEN 248
            WHEN df.payer_id = '403' THEN 270
            ELSE CAST(df.payer_id AS INT)
        END as hosp_payer_id,

        CASE
            {% for row in legacy_plan_names %}
                WHEN df.payer_id = '{{ row.payer_id }}' 
                    AND COALESCE(df.network_name, nn.network_name) = '{{ row.network_name_new }}' 
                THEN '{{ row.network_name_old }}'
            {% endfor %}
            ELSE COALESCE(df.network_name, nn.network_name)
        END as plan_alias,

        COALESCE(v.validated_subpackage, False) as validated_subpackage,


        -- if case rate is available (i.e. it is a non-base-code facility fee)
        --     THEN multiply it by the allocation, which is also only available 
        --          for non-base-code facility fees

        -- if case rate is not available (i.e. it is NOT a non-base-code facility fee)
        --     THEN do not multiply by the allocation
        CASE
            WHEN df.fee_type = 'professional'
                Then df.negotiated_rate * average_units  * anesthesia_unit_conversion
            WHEN df.fee_type = 'facility' AND df.is_package_base_code = 1
                THEN cr.case_rate
            WHEN df.fee_type = 'facility' AND df.is_package_base_code = 0
                THEN NULL
        END as price,
        COALESCE(al.allocation,1) as allocation,
        COALESCE(
            cr.case_rate, 
            df.negotiated_rate * average_units  * anesthesia_unit_conversion
        ) * COALESCE(al.allocation,1) as allocated_price

    FROM df
    LEFT JOIN allocations al
        ON {% for key in join_keys -%}
        df.{{ key }} = al.{{ key }}{{ " AND " if not loop.last else "" }}
        {% endfor -%}
    LEFT JOIN case_rates cr
        ON df.payer_id = cr.payer_id
        AND df.network_id = cr.network_id
        AND df.provider_id = cr.provider_id
        AND df.sub_package_id = cr.sub_package_id
        AND df.fee_type = cr.fee_type
    LEFT JOIN validated v
        ON df.ssp_id = v.ssp_id
        AND df.sub_package_id = v.sub_package_id
        AND df.payer_id = v.payer_id
        AND df.provider_id = v.provider_id
        AND df.network_id = v.network_id
    LEFT JOIN network_name nn
        ON df.network_id = nn.network_id
    LEFT JOIN code_descriptions cd
        ON df.billing_code = cd.billing_code
        AND df.billing_code_type = cd.billing_code_type
)
SELECT *
FROM output
WHERE (
    allocated_price IS NOT NULL
    OR negotiated_rate IS NOT NULL
    OR discounted_cash_rate IS NOT NULL
) AND (
    allocated_price > 1
    OR negotiated_rate > 1
    OR discounted_cash_rate > 1
)

-- SSP PLAUSIBILITY FILTERS
AND (
    provider_type != 'Imaging Center'
    OR
    (provider_type = 'Imaging Center' AND ssp_id LIKE 'RA%')
)

AND (
    provider_type != 'ASC'
    OR
    (provider_type = 'ASC' AND billing_code_type = 'HCPCS')
)