Rate Selection: Hospital HCPCS/Rev Code

	0
total_count	660654388
multiple_rev_codes_count	53113037
multiple_rev_codes_with_rate_diff_count	8757320

8757320 / 660654388 = ~1.32%

HCPCS posted with multiple revenue codes that have different negotiated dollar amounts represent a small portion of hospital MRF rates.

WITH
df AS (
    SELECT 
        payer_id,
        provider_id,
        setting,
        billing_class,
        plan_name,
        billing_code,
        COUNT(DISTINCT COALESCE(revenue_code, '')) AS rev_code_count,
        MIN(negotiated_dollar) AS min_rate,
        MAX(negotiated_dollar) AS max_rate,
        MAX(negotiated_dollar) - MIN(negotiated_dollar) AS rate_diff
    FROM tq_production.hospital_data.hospital_rates
    WHERE payer_class_name = 'Commercial'
    AND billing_code_type = 'HCPCS'
    GROUP BY 1,2,3,4,5,6
)
SELECT
    COUNT(*) AS total_count,
    SUM(
        CASE WHEN rev_code_count > 1 THEN 1 ELSE 0 END
    ) AS multiple_rev_codes_count,
    SUM(
        CASE 
            WHEN rev_code_count > 1 
            AND rate_diff > 0
        THEN 1 ELSE 0 END
    ) AS multiple_rev_codes_with_rate_diff_count
FROM df