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', 
    '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',
    'state_avg_negotiated_rate_used',
    'state_avg_discounted_cash_rate_used',
    'negotiated_rate', 
    'discounted_cash_rate',
    'cbsa_avg_medicare_rate',
    'lower_bound',
    'upper_bound',
    'contract_methodology', 
    'gross_charge', 
    'source', 
] %}

{% set rate_cols = [
    'average_units',
    'negotiated_rate', 
    'gross_charge',
    'discounted_cash_rate', 
    'cbsa_avg_medicare_rate', 
    'lower_bound', 
    'upper_bound',
    'anesthesia_unit_conversion',
    'price',
    'allocation',
    'allocated_price',
] %}


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

CREATE OR REPLACE 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
    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 }},
            {% elif col in rate_cols -%}
                CAST(df.{{ col }} AS DECIMAL(21,3)) AS {{ col }},
            {% else -%}
                df.{{ col }} AS {{ col }},
            {% endif -%}
        {% endfor -%}

        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
        CAST(
            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 DECIMAL(21,3)) as price,
        CAST(COALESCE(al.allocation,1) AS DECIMAL(21,3)) as allocation,
        CAST(
            COALESCE(
                cr.case_rate, 
                df.negotiated_rate * average_units  * anesthesia_unit_conversion
            ) * COALESCE(al.allocation,1)
        AS DECIMAL(21,3)) 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')
)