CCN Shared-Provider Rate Comparison Report

Date: 2026-04-20 Jira: CLD-69

Methodology

Sampled 100 random CCN groups from the 526 identified groups (246 total providers).

For each group, queried core_rates and hospital_rates with:

50 DRG codes (random sample)
200 Outpatient codes (random sample)

Grouped by all non-rate attributes to find tuples where multiple providers in the same CCN group report data. Then checked whether the reported rates are identical or different.

Results: core_rates (Payer MRF)

Source: tq_production.public_2026_02.core_rates

Matched on: ccn, billing_code, billing_code_type, product_network_label, billing_code_modifier, service_code, billing_class, negotiated_type, payer_id

Payers queried: UHC (643), Aetna (76), Cigna (628), BCBS (42)

Metric	Value
Rate groups with 2+ providers	105,472
Identical rates	83,507 (79.2%)
Different rates	21,965 (20.8%)

Examples of Different Rates in core_rates

1. CCN 453304 | 744 (MS-DRG) | SUREST - CALIFORNIA | payer 643

Provider 5402: $2652.00
Provider 8772: $2652.00
Provider 8506: $2652.00
Provider 5402: $4774.00
Provider 8506: $4774.00
Provider 9229: $4774.00
Provider 8506: $2271.00
Provider 5402: $2271.00
Provider 8772: $4774.00
Provider 9229: $2652.00
Provider 9229: $2271.00
Provider 8772: $2271.00

2. CCN 453304 | 744 (MS-DRG) | CHOICE PLUS | payer 643

Provider 8772: $4774.00
Provider 8506: $2652.00
Provider 9229: $4774.00
Provider 5402: $4774.00
Provider 8506: $4774.00
Provider 5402: $2271.00
Provider 9229: $2652.00
Provider 5402: $2652.00
Provider 8772: $2271.00
Provider 8506: $2271.00
Provider 8772: $2652.00
Provider 9229: $2271.00

3. CCN 453304 | 580 (MS-DRG) | CORE ESSENTIAL EPO | payer 643

Provider 8772: $2652.00
Provider 9229: $2271.00
Provider 9229: $4774.00
Provider 8506: $2652.00
Provider 8772: $4774.00
Provider 5402: $4774.00
Provider 8506: $2271.00
Provider 5402: $2271.00
Provider 9229: $2652.00
Provider 8772: $2271.00
Provider 5402: $2652.00
Provider 8506: $4774.00

4. CCN 453304 | 580 (MS-DRG) | CHOICE PLUS | payer 643

Provider 9229: $4774.00
Provider 9229: $2271.00
Provider 8772: $2652.00
Provider 8506: $2271.00
Provider 8772: $4774.00
Provider 9229: $2652.00
Provider 8506: $2652.00
Provider 5402: $2271.00
Provider 8772: $2271.00
Provider 5402: $2652.00
Provider 5402: $4774.00
Provider 8506: $4774.00

5. CCN 453304 | 744 (MS-DRG) | CORE ESSENTIAL EPO | payer 643

Provider 5402: $2271.00
Provider 5402: $4774.00
Provider 9229: $2652.00
Provider 9229: $2271.00
Provider 8772: $2652.00
Provider 8506: $2271.00
Provider 8506: $4774.00
Provider 9229: $4774.00
Provider 8772: $2271.00
Provider 8772: $4774.00
Provider 8506: $2652.00
Provider 5402: $2652.00

6. CCN 453304 | 580 (MS-DRG) | SUREST - NATIONAL | payer 643

Provider 5402: $2271.00
Provider 8772: $2271.00
Provider 8506: $4774.00
Provider 8506: $2271.00
Provider 9229: $2652.00
Provider 8772: $2652.00
Provider 5402: $2652.00
Provider 8506: $2652.00
Provider 9229: $4774.00
Provider 5402: $4774.00
Provider 9229: $2271.00
Provider 8772: $4774.00

7. CCN 453304 | 580 (MS-DRG) | SUREST - CALIFORNIA | payer 643

Provider 5402: $4774.00
Provider 9229: $2271.00
Provider 8506: $4774.00
Provider 8506: $2271.00
Provider 8772: $2271.00
Provider 8772: $2652.00
Provider 8772: $4774.00
Provider 5402: $2652.00
Provider 5402: $2271.00
Provider 8506: $2652.00
Provider 9229: $4774.00
Provider 9229: $2652.00

8. CCN 453304 | 744 (MS-DRG) | SUREST - NATIONAL | payer 643

Provider 5402: $4774.00
Provider 8772: $2271.00
Provider 8506: $2271.00
Provider 8772: $4774.00
Provider 8506: $4774.00
Provider 9229: $2271.00
Provider 9229: $2652.00
Provider 8772: $2652.00
Provider 8506: $2652.00
Provider 9229: $4774.00
Provider 5402: $2271.00
Provider 5402: $2652.00

9. CCN 453304 | 580 (MS-DRG) | OPTIONS PPO | payer 643

Provider 9229: $2652.00
Provider 5402: $4774.00
Provider 8772: $2271.00
Provider 8506: $2271.00
Provider 9229: $2271.00
Provider 5402: $2271.00
Provider 5402: $2652.00
Provider 8772: $4774.00
Provider 8772: $2652.00
Provider 8506: $4774.00
Provider 9229: $4774.00
Provider 8506: $2652.00

10. CCN 453304 | 744 (MS-DRG) | OPTIONS PPO | payer 643

Provider 9229: $2652.00
Provider 5402: $4774.00
Provider 8772: $2271.00
Provider 8506: $2652.00
Provider 9229: $4774.00
Provider 9229: $2271.00
Provider 8772: $4774.00
Provider 8506: $4774.00
Provider 5402: $2271.00
Provider 5402: $2652.00
Provider 8772: $2652.00
Provider 8506: $2271.00

Results: hospital_rates (Hospital MRF)

Source: tq_production.hospital_data.hospital_rates

Matched on: ccn, billing_code, billing_code_type, plan_name, setting, contract_methodology, billing_class, billing_code_modifiers

Metric	Value
Rate groups with 2+ providers	215,065
Identical dollar rates	168,455 (78.3%)
Different dollar rates	46,610 (21.7%)

Provider count distribution (hospital_rates)

Providers in group	Rate groups	Identical	Different	% Identical
2	138,390	108,682	29,708	78.5%
3	64,938	52,233	12,705	80.4%
4	9,311	5,405	3,906	58.0%
6	233	231	2	99.1%
7	2,193	1,904	289	86.8%

Examples of Different Rates in hospital_rates

1. CCN 450184 | 744 (MS-DRG) | plan: ULTRA EXCHANGE | setting: Inpatient

Provider 7279: $32108.05
Provider 6854: $32108.05
Provider 5395: $32108.05
Provider 6856: $32108.05
Provider 521: $39369.97
Provider 10619: $32108.05
Provider 6855: $32108.05

2. CCN 450184 | 203 (MS-DRG) | plan: ALL COMMERCIAL PLANS | setting: Inpatient

Provider 6856: $18129.95
Provider 6855: $18129.95
Provider 10619: $18129.95
Provider 521: $22860.14
Provider 5395: $18129.95
Provider 7279: $18129.95
Provider 6854: $18129.95

3. CCN 450184 | 807 (MS-DRG) | plan: ULTRA EXCHANGE | setting: Inpatient

Provider 6854: $15318.62
Provider 6856: $15318.62
Provider 6855: $15318.62
Provider 7279: $15318.62
Provider 5395: $15318.62
Provider 10619: $15318.62
Provider 521: $19311.92

4. CCN 450184 | 244 (MS-DRG) | plan: ULTRA EXCHANGE | setting: Inpatient

Provider 521: $35845.33
Provider 10619: $29157.78
Provider 6854: $29157.78
Provider 5395: $29157.78
Provider 6855: $29157.78
Provider 7279: $29157.78
Provider 6856: $29157.78

5. CCN 450184 | 031 (MS-DRG) | plan: ALL COMMERCIAL PLANS | setting: Inpatient

Provider 6856: $73386.06
Provider 521: $88873.72
Provider 7279: $73386.06
Provider 6854: $73386.06
Provider 6855: $73386.06
Provider 5395: $73386.06
Provider 10619: $73386.06

6. CCN 450184 | 157 (MS-DRG) | plan: ALL COMMERCIAL PLANS | setting: Inpatient

Provider 6856: $33309.62
Provider 5395: $33309.62
Provider 521: $40995.05
Provider 7279: $33309.62
Provider 10619: $33309.62
Provider 6854: $33309.62
Provider 6855: $33309.62

7. CCN 450184 | 031 (MS-DRG) | plan: ULTRA EXCHANGE | setting: Inpatient

Provider 5395: $61798.78
Provider 6856: $61798.78
Provider 6854: $61798.78
Provider 6855: $61798.78
Provider 521: $74841.02
Provider 7279: $61798.78
Provider 10619: $61798.78

8. CCN 450184 | 840 (MS-DRG) | plan: ALL COMMERCIAL PLANS | setting: Inpatient

Provider 5395: $55323.59
Provider 6854: $55323.59
Provider 7279: $55323.59
Provider 521: $67294.79
Provider 6855: $55323.59
Provider 10619: $55323.59
Provider 6856: $55323.59

9. CCN 450184 | 700 (MS-DRG) | plan: ALL COMMERCIAL PLANS | setting: Inpatient

Provider 6856: $18418.52
Provider 10619: $18418.52
Provider 7279: $18418.52
Provider 6855: $18418.52
Provider 5395: $18418.52
Provider 6854: $18418.52
Provider 521: $23204.89

10. CCN 450184 | 207 (MS-DRG) | plan: ULTRA EXCHANGE | setting: Inpatient

Provider 10619: $85662.26
Provider 5395: $85662.26
Provider 7279: $85662.26
Provider 6854: $85662.26
Provider 6856: $85662.26
Provider 6855: $85662.26
Provider 521: $103350.34

Conclusion

With the expanded sample (50 DRGs + 200 OP codes across 100 CCN groups):

core_rates (2026_02): 79.2% identical — payer MRFs consistently attribute the same rates to all facilities under a shared CCN
hospital_rates: 78.3% identical — most hospital MRF data is shared across CCN facilities, with 21.7% having facility-specific negotiated dollar amounts

The 21.7% different rates in hospital_rates likely reflect facility-specific pricing (e.g., different gross charges, facility-specific negotiated adjustments). The CCN canonical mapping approach still consolidates the majority correctly, and the non-identical rates would need further investigation to determine if they represent genuine facility-level differences or reporting methodology variations.

Data Files

core_rates_comparison.json — 105,472 core_rates comparison results
hospital_rates_comparison.json — 215,065 hospital_rates comparison results
sample_config.json — sampling parameters (includes full provider/code lists)
ccn_groups.json — complete list of all 526 CCN groups

Appendix: Reproducibility SQL

SELECT
    ccn,
    COUNT(DISTINCT provider_id) AS n_providers,
    ARRAY_AGG(provider_id ORDER BY CAST(provider_id AS INTEGER)) AS provider_ids,
    ARRAY_AGG(provider_name ORDER BY CAST(provider_id AS INTEGER)) AS provider_names
FROM tq_production.spines.spines_provider_hospitals
WHERE ccn IS NOT NULL AND ccn != ''
GROUP BY ccn
HAVING COUNT(DISTINCT provider_id) > 1
ORDER BY COUNT(DISTINCT provider_id) DESC

2. core_rates comparison (Payer MRF)

Compares negotiated rates across providers sharing a CCN, matched on all non-rate attributes. Replace {PROVIDER_IDS} and {BILLING_CODES} with values from sample_config.json.

WITH provider_ccn AS (
    SELECT provider_id, ccn, provider_name
    FROM tq_production.spines.spines_provider_hospitals
    WHERE provider_id IN ({PROVIDER_IDS})
),
provider_npis AS (
    SELECT provider_id, npi
    FROM tq_production.spines.spines_provider_hospitals
    WHERE provider_id IN ({PROVIDER_IDS})
    AND npi IS NOT NULL
),
rates AS (
    SELECT
        pc.ccn,
        pc.provider_id,
        pc.provider_name,
        cr.billing_code,
        cr.billing_code_type,
        cr.product_network_label,
        cr.billing_code_modifier,
        cr.service_code,
        cr.billing_class,
        cr.negotiated_rate,
        cr.negotiated_type,
        cr.payer_id
    FROM tq_production.public_2026_02.core_rates cr
    JOIN provider_npis pn ON cr.provider_npi = pn.npi
    JOIN provider_ccn pc ON pn.provider_id = pc.provider_id
    WHERE cr.billing_code IN ({BILLING_CODES})
    AND cr.payer_id IN ('643', '76', '628', '42')
)
SELECT
    ccn,
    billing_code,
    billing_code_type,
    product_network_label,
    billing_code_modifier,
    service_code,
    billing_class,
    negotiated_type,
    payer_id,
    COUNT(DISTINCT provider_id) AS n_providers,
    COUNT(DISTINCT negotiated_rate) AS n_distinct_rates,
    ARRAY_AGG(DISTINCT CAST(provider_id AS VARCHAR) || ':' || CAST(negotiated_rate AS VARCHAR)) AS provider_rates
FROM rates
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
HAVING COUNT(DISTINCT provider_id) > 1
ORDER BY n_providers DESC, n_distinct_rates ASC

3. hospital_rates comparison (Hospital MRF)

Compares negotiated dollar rates across providers sharing a CCN, matched on all non-rate attributes.

WITH provider_ccn AS (
    SELECT provider_id, ccn, provider_name
    FROM tq_production.spines.spines_provider_hospitals
    WHERE provider_id IN ({PROVIDER_IDS})
),
rates AS (
    SELECT
        pc.ccn,
        hr.provider_id,
        pc.provider_name,
        hr.billing_code,
        hr.billing_code_type,
        hr.plan_name,
        hr.setting,
        hr.contract_methodology,
        hr.billing_class,
        hr.billing_code_modifiers,
        hr.negotiated_dollar,
        hr.negotiated_percentage,
        hr.gross_charge,
        hr.discounted_cash_rate
    FROM tq_production.hospital_data.hospital_rates hr
    JOIN provider_ccn pc ON CAST(hr.provider_id AS VARCHAR) = pc.provider_id
    WHERE hr.billing_code IN ({BILLING_CODES})
    AND hr.provider_id IN ({PROVIDER_IDS})
)
SELECT
    ccn,
    billing_code,
    billing_code_type,
    plan_name,
    setting,
    contract_methodology,
    billing_class,
    billing_code_modifiers,
    COUNT(DISTINCT provider_id) AS n_providers,
    COUNT(DISTINCT negotiated_dollar) AS n_distinct_dollar_rates,
    COUNT(DISTINCT gross_charge) AS n_distinct_gross_charges,
    ARRAY_AGG(DISTINCT CAST(provider_id AS VARCHAR) || ':$' || COALESCE(CAST(negotiated_dollar AS VARCHAR), 'NULL')) AS provider_rates
FROM rates
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
HAVING COUNT(DISTINCT provider_id) > 1
ORDER BY n_providers DESC, n_distinct_dollar_rates ASC

Methodology​

Results: core_rates (Payer MRF)​

Examples of Different Rates in core_rates​

Results: hospital_rates (Hospital MRF)​

Provider count distribution (hospital_rates)​

Examples of Different Rates in hospital_rates​

Conclusion​

Data Files​

Appendix: Reproducibility SQL​

1. Identify CCN groups (hospitals sharing a CCN)​

2. core_rates comparison (Payer MRF)​

3. hospital_rates comparison (Hospital MRF)​

On this page: