Version: 2.2

Device Carveouts

The table below shows the number of device carveout percentage rates. "Device carveouts" are rates with revenue code that are one of ('0275', '0276', '0277', '0278').

Data Dictionary:

payer MRF: number of providers from payer MRFs
hospital MRF: number of providers from hospital MRFs
num_providers: number of providers either source
num_providers_with_both: number of providers with both payer and hospital MRF rates
num_validated: number of providers with validated rates
pct_validated: percentage of providers with validated rates

As an example: 1181 providers have rates from both payer and hospital MRFs for Aetna, and 876 of those have validated rates.

"Validated" means that the percentage rate is within 10% of each other.

	payer_mrf	hospital_mrf	num_providers	num_providers_with_both	num_validated	pct_validated
('7', 'Aetna')	3670	1780	4269	1181	876	0.741744
('76', 'Cigna')	2885	1728	3984	612	449	0.73366
('522', 'Kaiser Permanente')	3393	152	3404	140	35	0.25
('643', 'United Healthcare')	1005	1873	2542	335	202	0.602985
('42', 'Anthem')	1522	565	1907	153	109	0.712418
('49', 'Blue Cross Blue Shield of Michigan')	222	32	241	13	6	0.461538
('169', 'Blue Cross Blue Shield of Texas')	0	239	239	0	0	nan
('50', 'Blue Cross Blue Shield of Minnesota')	221	66	227	60	42	0.7
('390', 'Blue Cross Blue Shield of Kansas')	197	36	210	23	17	0.73913
('461', 'Wellmark Blue Cross Blue Shield')	196	52	208	11	11	1
('61', 'Regence Blue Cross Blue Shield')	173	0	173	0	0	nan
('799', 'Quartz')	164	36	169	31	30	0.967742
('403', 'Blue Shield of California')	89	79	151	17	8	0.470588
('229', 'Blue Cross Blue Shield of New Jersey (Horizon)')	98	64	150	6	5	0.833333
('388', 'Blue Cross Blue Shield of Florida (Florida Blue)')	0	132	132	0	0	nan
('398', 'Blue Cross Blue Shield of Tennessee')	81	66	125	22	15	0.681818
('389', 'Blue Cross Blue Shield of Illinois')	0	102	102	0	0	nan
('391', 'Blue Cross Blue Shield of Louisiana')	78	39	101	16	8	0.5
('300', 'Providence Health Plan')	83	21	88	16	16	1
('52', 'Blue Cross Blue Shield of North Carolina')	40	61	75	26	22	0.846154
('354', 'UPMC Health Plan')	0	64	64	0	0	nan
('770', 'Mass General Brigham Health Plan')	62	0	62	0	0	nan
('455', 'Blue Cross Blue Shield of Hawaii')	57	0	57	0	0	nan
('628', 'Premera Blue Cross')	23	35	54	0	0	nan
('299', 'Priority Health')	7	49	50	6	2	0.333333
('286', 'MVP Health Care')	0	50	50	0	0	nan
('720', 'Harvard Pilgrim')	0	50	50	0	0	nan
('111', 'Geisinger')	2	48	49	0	0	nan
('101', 'Blue Cross Blue Shield of New York (Excellus)')	26	17	43	0	0	nan
('174', 'Highmark Blue Cross Blue Shield')	0	42	42	0	0	nan
('458', 'Blue Cross Blue Shield of North Dakota')	37	7	39	5	2	0.4
('51', 'Blue Cross Blue Shield of Nebraska')	0	38	38	0	0	nan
('462', 'Blue Cross Blue Shield of Vermont')	32	8	33	7	7	1
('317', 'Tufts Health Plan')	0	33	33	0	0	nan
('636', 'SelectHealth')	0	29	29	0	0	nan
('168', 'Blue Cross Blue Shield of South Carolina')	19	16	28	7	4	0.571429
('383', 'Blue Cross of Idaho')	5	23	26	2	2	1
('392', 'Blue Cross Blue Shield of Massachusetts')	0	20	20	0	0	nan
('456', 'Blue Cross Blue Shield of Mississippi')	14	6	19	1	1	1
('454', 'Blue Cross Blue Shield of Arkansas')	0	15	15	0	0	nan
('166', 'Blue Cross Blue Shield of New Mexico')	0	13	13	0	0	nan
('165', 'Blue Cross Blue Shield of Montana')	0	11	11	0	0	nan
('56', 'Blue Cross Blue Shield of Pennsylvania (Independence)')	0	10	10	0	0	nan
('44', 'Blue Cross Blue Shield of Maryland & DC (CareFirst)')	0	8	8	0	0	nan
('53', 'Blue Cross Blue Shield of Oklahoma')	0	7	7	0	0	nan
('43', 'Blue Cross Blue Shield of Arizona')	0	7	7	0	0	nan
('723', 'Fidelis')	0	5	5	0	0	nan
('160', 'Blue Cross Blue Shield of Alabama')	0	5	5	0	0	nan
('397', 'Blue Cross Blue Shield of Rhode Island')	0	3	3	0	0	nan
('54', 'Capital Blue Cross')	0	1	1	0	0	nan

Code

Payer Device Rates SQL

WITH
network_spine AS (
    SELECT 
        payer_id,
        network_id,
        network_name,
        t.payer_data_network
    FROM 
        tq_dev.internal_dev_csong_cld_v2_0_0.tmp_ref_network_spine_2025_05,
        UNNEST(payer_data_network) AS t(payer_data_network)
),
payer_spine AS (
    SELECT DISTINCT
        payer_id,
        payer_name
    FROM tq_dev.internal_dev_csong_cld_v2_0_0.tmp_ref_payer_spine_2025_05
),
provider_spine AS (
    SELECT 
        npi_value AS npi, 
        provider_id
    FROM 
        tq_dev.internal_dev_csong_cld_v2_0_0.tmp_ref_provider_spine_2025_05,
        UNNEST(npi) AS t(npi_value)
    WHERE 
        provider_id IS NOT NULL
),
core_rates AS (
    SELECT
        cr.payer_id,
        ps.payer_name,
        ns.network_id,
        ns.network_name,
        ps.provider_id,
        CASE
            WHEN negotiated_rate < 1 THEN negotiated_rate * 100
            WHEN negotiated_rate > 100 THEN NULL
            ELSE negotiated_rate
        END AS percentage
    FROM tq_production.public_2025_05.core_rates cr
    JOIN network_spine ns
        ON ns.payer_id = cr.payer_id
        AND ns.payer_data_network = cr.product_network_label
    JOIN payer_spine ps
        ON ps.payer_id = cr.payer_id
    JOIN provider_spine ps
        ON ps.npi = cr.provider_npi
    WHERE billing_code_type = 'RC'
    AND billing_code IN (
        '0275',
        '0276',
        '0277',
        '0278'
    )
    AND billing_class = 'institutional'
    AND negotiated_type = 'percentage'
),
mode_rates AS (
    SELECT
        payer_id,
        network_id,
        provider_id,
        percentage,
        ROW_NUMBER() OVER (
            PARTITION BY payer_id, network_id, provider_id 
            ORDER BY COUNT DESC
        ) AS rn
    FROM (
        SELECT
            payer_id,
            network_id,
            provider_id,
            percentage,
            COUNT(*) as count
        FROM core_rates
        GROUP BY 1, 2, 3, 4
    )
)
SELECT 
    cr.payer_id,
    cr.payer_name,
    cr.network_id,
    cr.network_name,
    cr.provider_id,
    min(cr.percentage) as min_rate, 
    max(cr.percentage) as max_rate,
    avg(cr.percentage) as avg_rate,
    approx_percentile(cr.percentage, 0.5) as median_rate,
    ANY_VALUE(mr.percentage) as mode_rate,
    count(*) as num_rates
FROM core_rates cr
LEFT JOIN (SELECT * FROM mode_rates WHERE rn = 1) mr
    ON mr.payer_id = cr.payer_id
    AND mr.network_id = cr.network_id
    AND mr.provider_id = cr.provider_id
GROUP BY 1, 2, 3, 4, 5

Provider Device Rates SQL

WITH
network_spine AS (
    SELECT 
        payer_id,
        network_id,
        network_name,
        t.payer_data_network
    FROM 
        tq_dev.internal_dev_csong_cld_v2_0_0.tmp_ref_network_spine_2025_05,
        UNNEST(payer_data_network) AS t(payer_data_network)
),
payer_spine AS (
    SELECT 
        payer_id,
        payer_name
    FROM tq_dev.internal_dev_csong_cld_v2_0_0.tmp_ref_payer_spine_2025_05
),
provider_spine AS (
    SELECT 
        npi_value AS npi, 
        provider_id
    FROM 
        tq_dev.internal_dev_csong_cld_v2_0_0.tmp_ref_provider_spine_2025_05,
        UNNEST(npi) AS t(npi_value)
    WHERE 
        provider_id IS NOT NULL
),
hospital_rates AS (
    SELECT
        hr.payer_id,
        ps.payer_name,
        ns.network_id,
        ns.network_name,
        hr.provider_id,
        CASE
            WHEN negotiated_percentage < 1 THEN negotiated_percentage * 100
            WHEN negotiated_percentage > 100 THEN NULL
            ELSE negotiated_percentage
        END AS percentage
    FROM glue.hospital_data.hospital_rates hr
    JOIN (
        SELECT 
            DISTINCT
            payer_id, 
            network_name,
            t.plan_name
        FROM tq_dev.internal_dev_csong_cld_v2_0_0.tmp_ref_plan_bridge_analysis_2025_05, 
            UNNEST(unique_plans) AS t(plan_name)
    ) pb
        ON pb.payer_id = CAST(hr.payer_id AS VARCHAR)
        AND pb.plan_name = hr.plan_name
    JOIN network_spine ns
        ON ns.payer_id = pb.payer_id
        AND ns.network_name = pb.network_name
    JOIN payer_spine ps
        ON ps.payer_id = pb.payer_id
    WHERE 
        LPAD(hr.revenue_code, 4, '0') IN (
            '0275',
            '0276',
            '0277',
            '0278'
        )
        AND hr.negotiated_percentage IS NOT NULL
        AND contract_methodology = 'percent of total billed charges'
        AND COALESCE(billing_class, '') != 'Professional'
        AND payer_class_name = 'Commercial'
),
mode_rates AS (
    SELECT
        payer_id,
        network_id,
        provider_id,
        percentage,
        ROW_NUMBER() OVER (
            PARTITION BY payer_id, network_id, provider_id
            ORDER BY COUNT DESC
        ) AS rn
    FROM (
        SELECT
            CAST(payer_id AS VARCHAR) as payer_id,
            network_id,
            provider_id,
            percentage,
            COUNT(*) as count
        FROM hospital_rates
        GROUP BY 1, 2, 3, 4
    )
)
SELECT
    CAST(hr.payer_id AS VARCHAR) AS payer_id,
    hr.payer_name,
    hr.network_id,
    hr.network_name,
    hr.provider_id,
    min(hr.percentage) as min_rate,
    max(hr.percentage) as max_rate,
    avg(hr.percentage) as avg_rate,
    approx_percentile(hr.percentage, 0.5) as median_rate,
    ANY_VALUE(mr.percentage) as mode_rate,
    count(*) as num_rates
FROM hospital_rates hr
LEFT JOIN (SELECT * FROM mode_rates WHERE rn = 1) mr
    ON mr.payer_id = CAST(hr.payer_id AS VARCHAR)
    AND mr.network_id = hr.network_id
    AND mr.provider_id = hr.provider_id
GROUP BY 1, 2, 3,4, 5

Analysis

df = (
    df_payer
    .merge(
        df_provider, 
        on=['payer_id', 'payer_name', 'network_id', 'provider_id'], 
        how='outer', 
        suffixes=('_payer', '_provider')
    )
)
df

df['validated'] = (df['mode_rate_payer'] - df['mode_rate_provider']).abs() < 5
df['both_rates_present'] = df['mode_rate_payer'].notnull() & df['mode_rate_provider'].notnull()
df['payer_mrf'] = df['mode_rate_payer'].notnull()
df['hospital_mrf'] = df['mode_rate_provider'].notnull()
df_check = (
    df
    .loc[df['mode_rate_payer'].notnull() | df['mode_rate_provider'].notnull()]
    .groupby(['payer_id', 'payer_name'])
    .agg(
        payer_mrf=('payer_mrf', lambda x: df.loc[x.index[x], 'provider_id'][x].nunique()),
        hospital_mrf=('hospital_mrf', lambda x: df.loc[x.index[x], 'provider_id'][x].nunique()),
        num_unq_providers=('provider_id', pd.Series.nunique),
        num_providers_with_both=('both_rates_present', lambda x: df.loc[x.index[x], 'provider_id'][x].nunique()),
        num_unq_providers_validated=('validated', lambda x: df.loc[x.index[x], 'provider_id'].nunique())
    )
)
df_check = df_check.rename(columns={
    'num_unq_providers_validated': 'num_validated',
    'num_unq_providers': 'num_providers'
})
df_check['pct_validated'] = df_check['num_validated'] / df_check['num_providers_with_both']
df_check = df_check.sort_values('num_providers', ascending=False)
print(df_check.to_markdown())

More Details:

Density plot across all payers:

alt text

Distributions by payer:

alt text

Payer Device Rates SQL​

Provider Device Rates SQL​

Analysis​

More Details:​

On this page:

Payer Device Rates SQL

Provider Device Rates SQL

Analysis

More Details: