v2_2_0 → v2_2_1

Generated: 2025-11-03 22:14:29.
Filter: canonical_rate_score > 1.

---

1. Summary Statistics

Overview

• Total ROIDs: 493,633,402 → 532,278,202 (+38,644,800, +7.8%)

By Provider Type and Bill Type

provider_type	bill_type	new_distinct_roids	old_distinct_roids	new_distinct_networks	old_distinct_networks	new_distinct_providers	old_distinct_providers	new_distinct_billing_codes	old_distinct_billing_codes
Hospital	Inpatient	41,357,176	39,729,511	170	170	5,477	5,016	1,716	1,716
Laboratory	Professional	2,248,199	2,212,363	149	149	731	730	917	917
ASC	Outpatient	38,716,300	38,620,904	145	144	5,584	5,569	3,371	3,371
Hospital	Outpatient	173,223,088	167,246,191	170	170	5,528	5,441	7,026	7,028
Physician Group	Professional	273,683,261	242,779,612	170	169	16,464	16,448	2,839	2,388
Imaging Center	Professional	3,050,178	3,044,821	152	151	3,004	3,004	172	172

Click to see SQL

WITH
new AS (
    SELECT
        provider_type,
        bill_type,
        COUNT(DISTINCT roid) AS distinct_roids,
        COUNT(DISTINCT payer_id) AS distinct_payers,
        COUNT(DISTINCT network_id) AS distinct_networks,
        COUNT(DISTINCT provider_id) AS distinct_providers,
        COUNT(DISTINCT billing_code) AS distinct_billing_codes
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY 1,2
),
old AS (
    SELECT
        CASE
            WHEN provider_type LIKE '%Hospital%' THEN 'Hospital'
            ELSE provider_type
        END AS provider_type,
        bill_type,
        COUNT(DISTINCT roid) AS distinct_roids,
        COUNT(DISTINCT payer_id) AS distinct_payers,
        COUNT(DISTINCT network_id) AS distinct_networks,
        COUNT(DISTINCT provider_id) AS distinct_providers,
        COUNT(DISTINCT billing_code) AS distinct_billing_codes
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY 1, 2
)
SELECT
    provider_type,
    bill_type,
    new.distinct_roids AS new_distinct_roids,
    old.distinct_roids AS old_distinct_roids,
    new.distinct_networks AS new_distinct_networks,
    old.distinct_networks AS old_distinct_networks,
    new.distinct_providers AS new_distinct_providers,
    old.distinct_providers AS old_distinct_providers,
    new.distinct_billing_codes AS new_distinct_billing_codes,
    old.distinct_billing_codes AS old_distinct_billing_codes
FROM new
JOIN old USING (provider_type, bill_type)

2. Rate Object Space Dropoffs

Provider Dropoffs

Count: 5 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT provider_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
),
old AS (
    SELECT DISTINCT provider_id, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
)
SELECT
    DISTINCT
    provider_id
FROM new
FULL OUTER JOIN old USING (provider_id)
WHERE new.source IS NULL AND old.source IS NOT NULL

Payer Dropoffs

Count: 0 dropped

No dropoffs detected.

Click to see SQL

WITH
new AS (
    SELECT DISTINCT payer_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
),
old AS (
    SELECT DISTINCT payer_id, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
)
SELECT
    DISTINCT
    payer_id
FROM new
FULL OUTER JOIN old USING (payer_id)
WHERE new.source IS NULL AND old.source IS NOT NULL

Network Dropoffs

Count: 1 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT network_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
),
old AS (
    SELECT DISTINCT network_id, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
)
SELECT
    DISTINCT
    network_id
FROM new
FULL OUTER JOIN old USING (network_id)
WHERE new.source IS NULL AND old.source IS NOT NULL

Provider-Network Combination Dropoffs

Count: 532 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT provider_id, network_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
),
old AS (
    SELECT DISTINCT provider_id, network_id, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
)
SELECT
    DISTINCT
    provider_id,
    network_id
FROM new
FULL OUTER JOIN old USING (provider_id, network_id)
WHERE new.source IS NULL AND old.source IS NOT NULL

Bill Type-Billing Code Combination Dropoffs

Count: 2 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT bill_type, billing_code, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
),
old AS (
    SELECT DISTINCT bill_type, billing_code, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
)
SELECT
    DISTINCT
    bill_type,
    billing_code
FROM new
FULL OUTER JOIN old USING (bill_type, billing_code)
WHERE new.source IS NULL AND old.source IS NOT NULL

3. Large Rate Swings (>10% change)

Bill Type + Provider Level

Count: 11974 entities with significant rate changes

bill_type	provider_id	new_avg_pct_of_medicare_rate	old_avg_pct_of_medicare_rate	new_avg_canonical_rate	old_avg_canonical_rate	medicare_rate_pct_change	canonical_rate_pct_change	new_roid_count	old_roid_count
Outpatient	25998	6.01	1.06	1,390.35	1,390.35	464.2%	0.0%	536	536
Outpatient	20424	6.63	1.24	1,598.70	1,598.70	436.8%	0.0%	1,060	1,060
Outpatient	29621	7.28	1.37	1,612.22	1,612.22	432.3%	0.0%	1,046	1,046
Outpatient	31992	19.9	3.81	4,173.43	4,173.43	422.4%	0.0%	2,243	2,243
Outpatient	27741	9.82	1.99	2,078.92	2,078.92	394.0%	-0.0%	1,797	1,797
Outpatient	21351	13.93	2.84	2,224.76	2,224.76	390.0%	0.0%	2,059	2,059
Outpatient	27563	4.38	0.9	836.12	836.12	386.9%	0.0%	1,548	1,548
Outpatient	24315	7.41	1.54	1,579.14	1,579.14	380.9%	0.0%	1,880	1,880
Outpatient	25802	4.37	0.97	881.89	881.89	348.8%	0.0%	1,758	1,758
Outpatient	27903	3.82	0.9	852.72	852.72	324.6%	-0.0%	1,733	1,733
Outpatient	27917	5.41	1.29	1,242.14	1,242.14	318.6%	0.0%	800	800
Outpatient	8489	4.59	1.13	10,437.26	644.12	307.1%	1520.4%	31,247	4,088
Outpatient	9730	4.09	1.02	11,041.49	431.72	301.6%	2457.5%	32,470	817
Outpatient	8749	2.57	0.65	1,217.43	932.93	295.5%	30.5%	13,800	4,423
Outpatient	29546	7.77	2	2,271.80	2,271.80	289.1%	0.0%	987	987
Outpatient	31630	23.08	6.1	7,624.85	7,639.58	278.3%	-0.2%	8,955	8,934
Outpatient	24211	4.57	1.24	2,164.61	2,165.02	268.7%	-0.0%	10,110	10,108
Outpatient	20039	4.28	1.18	1,922.10	1,922.50	262.9%	-0.0%	18,443	18,439
Outpatient	30688	10.47	3.02	4,061.71	4,068.15	246.3%	-0.2%	22,704	22,668
Outpatient	25612	14.47	4.19	5,839.47	5,839.47	245.0%	-0.0%	766	766
Outpatient	9633	3.58	1.04	8,004.01	593.69	244.2%	1248.2%	29,256	2,040
Outpatient	23078	5.47	1.62	2,553.21	2,553.58	238.2%	-0.0%	12,611	12,609
Outpatient	22553	6.25	1.89	2,520.56	2,524.50	230.5%	-0.2%	18,397	18,368
Outpatient	26077	9.48	2.93	3,530.89	3,531.23	223.2%	-0.0%	20,178	20,176
Outpatient	20340	4.88	1.51	2,913.10	2,913.10	222.2%	-0.0%	2,106	2,106
Outpatient	10420	3.51	1.09	6,146.90	594.98	221.8%	933.1%	44,380	1,886
Outpatient	27485	2.76	0.87	1,177.91	1,178.09	218.5%	-0.0%	23,959	23,955
Inpatient	4038	1.5	0.48	18,226.76	7,205.92	212.9%	152.9%	39	1,713
Inpatient	860	2.12	0.69	24,867.88	8,340.87	208.6%	198.1%	195	2,543
Outpatient	24189	9.27	3.01	3,466.98	3,467.32	208.4%	-0.0%	20,162	20,160
Outpatient	20282	4.69	1.53	2,758.29	2,758.29	207.5%	0.0%	5,466	5,466
Outpatient	29123	9.02	2.95	3,358.85	3,359.17	206.2%	-0.0%	20,159	20,157
Inpatient	5221	1.72	0.56	38,018.75	10,247.51	205.1%	271.0%	83	3,408
Inpatient	4118	1.71	0.56	19,210.84	6,313.25	204.6%	204.3%	51	1,078
Outpatient	25541	9.48	3.11	3,530.89	3,531.23	204.4%	-0.0%	20,178	20,176
Inpatient	9651	3.26	1.09	66,050.22	13,881.03	198.8%	375.8%	8,690	1,458
Outpatient	24408	3.48	1.17	2,006.70	2,006.70	196.4%	0.0%	4,298	4,298
Outpatient	26698	6.77	2.29	3,949.25	3,949.25	195.1%	-0.0%	3,284	3,284
Outpatient	20379	3.78	1.29	1,994.45	1,994.45	191.7%	0.0%	5,946	5,946
Outpatient	28885	3.82	1.31	2,028.04	2,028.04	191.5%	0.0%	5,921	5,921
Outpatient	21781	5.32	1.84	2,039.50	2,051.51	189.4%	-0.6%	9,082	9,013
Outpatient	27268	3.37	1.17	1,943.03	1,943.03	186.8%	0.0%	2,196	2,196
Outpatient	29086	4.65	1.63	1,716.40	1,716.58	185.5%	-0.0%	17,981	17,979
Inpatient	5804	4	1.4	57,179.09	15,411.01	185.2%	271.0%	1,815	195
Inpatient	3479	1.82	0.64	20,968.18	8,483.23	183.3%	147.2%	88	2,230
Inpatient	4819	4.47	1.59	113,148.37	33,001.14	180.9%	242.9%	3,026	6,614
Outpatient	32086	6.82	2.43	3,538.45	3,538.45	180.9%	0.0%	6,298	6,298
Outpatient	29535	8.69	3.16	3,284.31	3,284.31	175.4%	0.0%	23,699	23,699
Inpatient	5547	1.26	0.47	38,147.55	10,643.64	171.5%	258.4%	119	5,827
Inpatient	3693	1.44	0.53	14,973.06	7,610.65	171.1%	96.7%	27	360

Showing 50 of 11974 total rows

Click to see SQL

WITH
new AS (
    SELECT
        bill_type,
        provider_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY bill_type, provider_id
),
old AS (
    SELECT
        bill_type,
        provider_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY bill_type, provider_id
)
SELECT
    bill_type,
    provider_id,
    new.avg_pct_of_medicare_rate AS new_avg_pct_of_medicare_rate,
    old.avg_pct_of_medicare_rate AS old_avg_pct_of_medicare_rate,
    new.avg_canonical_rate AS new_avg_canonical_rate,
    old.avg_canonical_rate AS old_avg_canonical_rate,
    (new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0) AS medicare_rate_pct_change,
    (new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0) AS canonical_rate_pct_change,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count
FROM new
JOIN old USING (bill_type, provider_id)
WHERE (
    ABS((new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0)) > 0.1
   OR ABS((new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0)) > 0.1
) AND (
    CASE
        WHEN bill_type = 'Inpatient' 
        THEN new.roid_count > 10 AND old.roid_count > 10
        WHEN bill_type = 'Outpatient' 
        THEN new.roid_count > 500 AND old.roid_count > 500
        WHEN bill_type = 'Professional' 
        THEN new.roid_count > 500 AND old.roid_count > 500
    END
)
ORDER BY ABS(medicare_rate_pct_change) DESC

Network + Provider Level

Count: 51060 entities with significant rate changes

network_id	provider_id	new_avg_pct_of_medicare_rate	old_avg_pct_of_medicare_rate	new_avg_canonical_rate	old_avg_canonical_rate	medicare_rate_pct_change	canonical_rate_pct_change	new_roid_count	old_roid_count
8,361,580,493,441,765,265	2029	9.08	1.22	20,403.50	1,757.03	646.1%	1061.2%	4,180	1,204
1,391,056,274,517,336,075	2493928895245108889	2.35	0.42	187.89	153.77	453.4%	22.2%	1,494	1,867
1,391,056,274,517,336,075	-5135206558611698801	2.35	0.42	187.89	153.77	453.4%	22.2%	1,494	1,867
8,635,146,956,337,329,045	24211	5.65	1.17	2,720.89	2,720.89	383.9%	-0.0%	916	916
6,437,583,333,460,157,798	22553	7.61	1.72	3,478.29	3,478.29	342.1%	0.0%	3,258	3,258
-3,063,756,195,086,079,598	22553	7.48	1.71	3,410.46	3,438.28	336.6%	-0.8%	3,323	3,296
-5,146,581,664,284,924,254	30688	6.75	1.55	3,016.50	3,016.50	336.1%	0.0%	3,243	3,243
-3,776,001,016,975,145,508	30688	6.75	1.55	3,016.50	3,016.50	336.1%	0.0%	3,243	3,243
-3,776,001,016,975,145,508	20282	6.04	1.4	3,871.24	3,871.24	331.4%	-0.0%	1,088	1,088
-70,870,604,090,909,000	20282	5.33	1.27	3,450.54	3,450.54	320.0%	-0.0%	1,096	1,096
-3,776,001,016,975,145,508	27959	6.7	1.6	3,803.99	3,803.99	318.6%	0.0%	1,384	1,384
-3,776,001,016,975,145,508	8489	4.65	1.12	11,506.01	626.63	313.7%	1736.2%	5,986	1,013
-3,776,001,016,975,145,508	20039	4.67	1.15	2,177.46	2,177.46	306.7%	0.0%	3,241	3,241
4,911,047,023,988,898,996	20039	4.67	1.15	2,177.46	2,177.46	306.7%	0.0%	3,241	3,241
4,857,361,656,543,545,479	30688	12.05	2.97	5,326.39	5,407.14	306.3%	-1.5%	2,408	2,372
-3,776,001,016,975,145,508	27485	4.04	1	1,882.49	1,882.49	304.0%	0.0%	3,243	3,243
4,911,047,023,988,898,996	27485	4.04	1	1,882.49	1,882.49	304.0%	0.0%	3,243	3,243
-3,776,001,016,975,145,508	24211	6.18	1.53	3,030.69	3,030.69	303.8%	-0.0%	3,238	3,238
311,448,953,162,816,591	30688	6.72	1.68	2,981.54	2,981.54	300.4%	0.0%	2,411	2,411
-3,776,001,016,975,145,508	26698	8.65	2.2	5,445.82	5,445.82	293.4%	0.0%	1,088	1,088
4,281,965,180,796,802,413	8489	4.41	1.13	10,818.38	669.49	288.6%	1515.9%	5,767	1,016
8,361,580,493,441,765,265	8489	4.41	1.13	10,818.38	669.49	288.6%	1515.9%	5,767	1,016
-3,776,001,016,975,145,508	29086	7.18	1.85	3,069.10	3,069.10	287.8%	0.0%	3,248	3,248
4,281,965,180,796,802,413	29086	3.85	1	1,672.20	1,672.73	283.8%	-0.0%	2,977	2,976
8,361,580,493,441,765,265	29086	3.85	1	1,672.20	1,672.73	283.8%	-0.0%	2,977	2,976
3,267,736,702,885,179,114	2346	4.27	1.12	10,663.86	3,609.85	282.3%	195.4%	5,462	1,160
-7,580,909,833,117,018,375	31630	23.24	6.14	7,630.73	7,645.73	278.3%	-0.2%	2,932	2,925
-2,162,498,661,799,343,237	31630	23.24	6.14	7,630.73	7,645.73	278.3%	-0.2%	2,932	2,925
-1,068,861,235,619,238,745	31630	23.24	6.14	7,630.73	7,645.73	278.3%	-0.2%	2,932	2,925
-3,776,001,016,975,145,508	20379	3.76	1	2,146.40	2,146.40	276.4%	0.0%	1,090	1,090
-3,776,001,016,975,145,508	28885	3.95	1.05	2,254.59	2,254.59	276.2%	-0.0%	1,090	1,090
8,361,580,493,441,765,265	32734	5.93	1.6	2,619.30	2,620.11	272.0%	-0.0%	2,982	2,981
4,281,965,180,796,802,413	32734	5.93	1.6	2,619.30	2,620.11	272.0%	-0.0%	2,982	2,981
-3,776,001,016,975,145,508	23078	7.4	2	3,589.69	3,589.69	270.5%	-0.0%	2,617	2,617
-3,063,756,195,086,079,598	23078	6.51	1.76	3,165.79	3,165.79	269.9%	-0.0%	2,632	2,632
6,437,583,333,460,157,798	23078	6.51	1.76	3,165.79	3,165.79	269.9%	-0.0%	2,632	2,632
-3,776,001,016,975,145,508	29439	4.98	1.35	3,416.60	3,416.60	269.1%	-0.0%	1,042	1,042
8,361,580,493,441,765,265	29535	11.38	3.18	4,861.02	4,861.02	258.1%	0.0%	2,969	2,969
4,281,965,180,796,802,413	29535	11.38	3.18	4,861.02	4,861.02	258.1%	0.0%	2,969	2,969
4,299,532,402,194,607,519	26077	5.88	1.65	2,524.39	2,525.21	256.8%	-0.0%	2,979	2,978
7,058,457,158,512,878,544	26077	5.27	1.48	2,264.00	2,264.74	256.7%	-0.0%	2,978	2,977
7,058,457,158,512,878,544	27439	2.9	0.81	1,243.04	1,243.45	256.5%	-0.0%	2,958	2,957
4,299,532,402,194,607,519	27439	3.32	0.93	1,426.20	1,426.66	256.5%	-0.0%	2,972	2,971
-2,162,498,661,799,343,237	30688	16.05	4.53	5,214.75	5,214.75	254.5%	0.0%	2,652	2,652
8,361,580,493,441,765,265	29980	2.53	0.72	1,593.25	1,593.25	250.8%	0.0%	1,280	1,280
4,281,965,180,796,802,413	29980	2.53	0.72	1,593.25	1,593.25	250.8%	0.0%	1,280	1,280
4,377,565,664,656,721,182	21781	6.25	1.79	2,630.44	2,630.96	248.9%	-0.0%	3,172	3,171
-8,389,359,264,395,369,979	1279	10.17	2.96	35,879.97	19,215.66	244.2%	86.7%	5,032	3,678
4,281,965,180,796,802,413	20039	5.23	1.52	2,314.95	2,315.66	243.3%	-0.0%	2,982	2,981
8,361,580,493,441,765,265	20039	5.23	1.52	2,314.95	2,315.66	243.3%	-0.0%	2,982	2,981

Showing 50 of 51060 total rows

Click to see SQL

WITH
new AS (
    SELECT
        network_id,
        provider_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY network_id, provider_id
),
old AS (
    SELECT
        network_id,
        provider_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY network_id, provider_id
)
SELECT
    network_id,
    provider_id,
    new.avg_pct_of_medicare_rate AS new_avg_pct_of_medicare_rate,
    old.avg_pct_of_medicare_rate AS old_avg_pct_of_medicare_rate,
    new.avg_canonical_rate AS new_avg_canonical_rate,
    old.avg_canonical_rate AS old_avg_canonical_rate,
    (new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0) AS medicare_rate_pct_change,
    (new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0) AS canonical_rate_pct_change,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count
FROM new
JOIN old USING (network_id, provider_id)
WHERE (
    ABS((new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0)) > 0.1
   OR ABS((new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0)) > 0.1
) AND (
    new.roid_count > 500 AND old.roid_count > 500
)
ORDER BY ABS(medicare_rate_pct_change) DESC

Network + Provider + Bill Type Level

Count: 55282 entities with significant rate changes

network_id	provider_id	bill_type	new_avg_pct_of_medicare_rate	old_avg_pct_of_medicare_rate	new_avg_canonical_rate	old_avg_canonical_rate	medicare_rate_pct_change	canonical_rate_pct_change	new_roid_count	old_roid_count
-7,227,420,687,598,643,435	4819	Inpatient	6.46	0.68	241,653.49	12,917.41	845.8%	1770.8%	267	1,677
4,281,965,180,796,802,413	1297	Inpatient	4.31	0.47	53,526.14	9,160.15	815.8%	484.3%	28	1,179
8,361,580,493,441,765,265	1297	Inpatient	4.31	0.47	53,526.14	9,160.15	815.8%	484.3%	28	1,179
-3,776,001,016,975,145,508	4578	Inpatient	1.49	0.19	16,602.32	6,050.85	701.3%	174.4%	195	17
4,281,965,180,796,802,413	994	Inpatient	3.8	0.48	56,287.01	10,227.44	699.8%	450.4%	28	1,716
8,361,580,493,441,765,265	994	Inpatient	3.8	0.48	56,287.01	10,227.44	699.8%	450.4%	28	1,716
8,361,580,493,441,765,265	2029	Outpatient	9.1	1.14	20,184.67	663.69	698.1%	2941.3%	4,154	1,183
-6,293,294,949,651,930,973	2029	Outpatient	8.84	1.18	25,520.12	21,653.66	648.3%	17.9%	4,230	1,170
-2,162,498,661,799,343,237	1988	Inpatient	3.85	0.63	77,736.66	9,073.03	507.9%	756.8%	39	405
1,391,056,274,517,336,075	-5135206558611698801	Professional	2.35	0.42	187.89	153.77	453.4%	22.2%	1,494	1,867
1,391,056,274,517,336,075	2493928895245108889	Professional	2.35	0.42	187.89	153.77	453.4%	22.2%	1,494	1,867
-2,596,917,860,902,475,653	4819	Inpatient	4.49	0.81	164,790.28	15,022.71	451.1%	996.9%	333	1,688
-2,162,498,661,799,343,237	3303	Inpatient	4.22	0.86	43,771.07	11,537.80	393.7%	279.4%	108	1,218
-2,162,498,661,799,343,237	4819	Inpatient	6.59	1.36	143,377.25	31,452.87	384.9%	355.8%	747	1,568
8,635,146,956,337,329,045	24211	Outpatient	5.65	1.17	2,720.89	2,720.89	383.9%	0.0%	916	916
-7,533,659,758,684,922,701	1669	Inpatient	3.36	0.72	55,594.41	14,247.14	368.5%	290.2%	262	1,321
8,361,580,493,441,765,265	3258	Inpatient	2.57	0.57	34,005.71	7,073.43	349.9%	380.8%	31	872
4,281,965,180,796,802,413	3258	Inpatient	2.57	0.57	34,005.71	7,073.43	349.9%	380.8%	31	872
6,437,583,333,460,157,798	22553	Outpatient	7.61	1.72	3,478.29	3,478.29	342.1%	-0.0%	3,258	3,258
-3,063,756,195,086,079,598	22553	Outpatient	7.48	1.71	3,410.46	3,438.28	336.6%	-0.8%	3,323	3,296
-5,146,581,664,284,924,254	30688	Outpatient	6.75	1.55	3,016.50	3,016.50	336.1%	-0.0%	3,243	3,243
-3,776,001,016,975,145,508	30688	Outpatient	6.75	1.55	3,016.50	3,016.50	336.1%	0.0%	3,243	3,243
-3,776,001,016,975,145,508	5181	Inpatient	4.95	1.14	75,306.06	18,290.61	334.5%	311.7%	1,679	801
-3,776,001,016,975,145,508	20282	Outpatient	6.04	1.4	3,871.24	3,871.24	331.4%	0.0%	1,088	1,088
4,281,965,180,796,802,413	904	Inpatient	3.13	0.73	39,969.43	9,570.83	328.9%	317.6%	36	1,078
8,361,580,493,441,765,265	904	Inpatient	3.13	0.73	39,969.43	9,570.83	328.9%	317.6%	36	1,078
-3,776,001,016,975,145,508	8489	Outpatient	4.82	1.12	10,115.98	626.63	328.4%	1514.4%	5,621	1,013
-70,870,604,090,909,000	20282	Outpatient	5.33	1.27	3,450.54	3,450.54	320.0%	-0.0%	1,096	1,096
8,816,084,628,491,357,618	1154	Inpatient	1.99	0.47	21,499.73	4,825.38	318.8%	345.6%	24	206
-2,510,676,791,703,302,876	1154	Inpatient	1.99	0.47	21,499.73	4,825.38	318.8%	345.6%	24	206
-3,776,001,016,975,145,508	27959	Outpatient	6.7	1.6	3,803.99	3,803.99	318.6%	-0.0%	1,384	1,384
-3,776,001,016,975,145,508	20039	Outpatient	4.67	1.15	2,177.46	2,177.46	306.7%	-0.0%	3,241	3,241
4,911,047,023,988,898,996	20039	Outpatient	4.67	1.15	2,177.46	2,177.46	306.7%	-0.0%	3,241	3,241
4,857,361,656,543,545,479	30688	Outpatient	12.05	2.97	5,326.39	5,407.14	306.3%	-1.5%	2,408	2,372
-3,776,001,016,975,145,508	27485	Outpatient	4.04	1	1,882.49	1,882.49	304.0%	-0.0%	3,243	3,243
4,911,047,023,988,898,996	27485	Outpatient	4.04	1	1,882.49	1,882.49	304.0%	0.0%	3,243	3,243
-3,776,001,016,975,145,508	24211	Outpatient	6.18	1.53	3,030.69	3,030.69	303.8%	0.0%	3,238	3,238
4,281,965,180,796,802,413	4656	Inpatient	2.41	0.6	28,671.27	9,993.03	303.2%	186.9%	28	1,713
4,281,965,180,796,802,413	1503	Inpatient	2.54	0.63	33,528.24	11,099.75	302.9%	202.1%	399	1,706
8,361,580,493,441,765,265	1503	Inpatient	2.54	0.63	33,528.24	11,099.75	302.9%	202.1%	399	1,706
8,361,580,493,441,765,265	4656	Inpatient	2.41	0.6	28,671.27	10,010.67	301.7%	186.4%	28	1,713
3,267,736,702,885,179,114	2346	Outpatient	4.35	1.09	10,272.27	566.12	300.8%	1714.5%	5,309	1,010
311,448,953,162,816,591	30688	Outpatient	6.72	1.68	2,981.54	2,981.54	300.4%	0.0%	2,411	2,411
8,361,580,493,441,765,265	8489	Outpatient	4.5	1.13	10,476.02	669.49	296.3%	1464.8%	5,580	1,016
4,281,965,180,796,802,413	8489	Outpatient	4.5	1.13	10,476.02	669.49	296.3%	1464.8%	5,580	1,016
-3,776,001,016,975,145,508	26698	Outpatient	8.65	2.2	5,445.82	5,445.82	293.4%	0.0%	1,088	1,088
8,361,580,493,441,765,265	1510	Inpatient	2.41	0.62	31,316.55	11,120.56	289.3%	181.6%	459	1,706
4,281,965,180,796,802,413	1510	Inpatient	2.41	0.62	31,316.55	11,120.56	289.3%	181.6%	459	1,706
-3,776,001,016,975,145,508	29086	Outpatient	7.18	1.85	3,069.10	3,069.10	287.8%	-0.0%	3,248	3,248
8,361,580,493,441,765,265	2044	Inpatient	2.03	0.52	25,982.37	9,705.23	286.7%	167.7%	28	1,249

Showing 50 of 55282 total rows

Click to see SQL

WITH
new AS (
    SELECT
        network_id,
        provider_id,
        bill_type,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY network_id, provider_id, bill_type
),
old AS (
    SELECT
        network_id,
        provider_id,
        bill_type,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY network_id, provider_id, bill_type
)
SELECT
    network_id,
    provider_id,
    bill_type,
    new.avg_pct_of_medicare_rate AS new_avg_pct_of_medicare_rate,
    old.avg_pct_of_medicare_rate AS old_avg_pct_of_medicare_rate,
    new.avg_canonical_rate AS new_avg_canonical_rate,
    old.avg_canonical_rate AS old_avg_canonical_rate,
    (new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0) AS medicare_rate_pct_change,
    (new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0) AS canonical_rate_pct_change,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count
FROM new
JOIN old USING (network_id, provider_id, bill_type)
WHERE (
    ABS((new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0)) > 0.1
   OR ABS((new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0)) > 0.1
) AND (
    CASE
        WHEN bill_type = 'Inpatient' 
        THEN new.roid_count > 10 AND old.roid_count > 10
        WHEN bill_type = 'Outpatient' 
        THEN new.roid_count > 500 AND old.roid_count > 500
        WHEN bill_type = 'Professional' 
        THEN new.roid_count > 500 AND old.roid_count > 500
    END
)
ORDER BY ABS(medicare_rate_pct_change) DESC

Network Level

Count: 58 entities with significant rate changes

network_id	new_avg_pct_of_medicare_rate	old_avg_pct_of_medicare_rate	new_avg_canonical_rate	old_avg_canonical_rate	medicare_rate_pct_change	canonical_rate_pct_change	new_roid_count	old_roid_count
-8,508,828,306,067,810,497	1.63	3.12	2,396.67	2,830.40	-47.8%	-15.3%	1,852,524	1,512,149
-7,942,674,608,514,495,282	2.07	3.49	5,720.81	5,855.95	-40.6%	-2.3%	2,077,155	1,946,028
5,781,103,079,387,890,636	1.78	2.97	2,604.04	2,884.34	-40.2%	-9.7%	1,859,537	1,724,357
-1,199,840,640,198,334,888	2.15	3.42	6,407.86	6,405.39	-37.1%	0.0%	2,160,326	2,079,588
-8,403,435,045,225,894,954	2.1	3.3	4,193.33	4,101.05	-36.4%	2.3%	623,789	598,076
8,530,564,294,955,512,747	1.74	2.71	4,734.07	4,385.70	-35.7%	7.9%	1,680,386	1,464,433
-3,063,756,195,086,079,598	2.06	3.15	4,348.73	4,429.77	-34.5%	-1.8%	2,495,700	2,347,206
-7,227,420,687,598,643,435	1.48	2.26	5,967.82	6,656.52	-34.2%	-10.3%	658,971	610,653
-2,596,917,860,902,475,653	1.84	2.74	6,408.30	6,931.44	-32.8%	-7.5%	891,072	806,842
-7,365,202,427,208,089,985	2.15	3.16	5,368.20	5,293.66	-32.1%	1.4%	974,445	890,833
-70,870,604,090,909,000	2.17	3.16	6,326.67	6,167.84	-31.3%	2.6%	1,781,430	1,611,256
1,017,413,876,722,652,424	2.09	2.96	5,462.56	5,823.33	-29.5%	-6.2%	5,579,083	5,162,601
-2,105,332,499,153,538,685	2.74	3.89	9,166.63	9,176.45	-29.4%	-0.1%	874,160	860,858
7,616,836,552,481,822,175	2.16	3.04	6,011.03	6,349.94	-28.8%	-5.3%	6,077,189	5,597,668
-434,992,428,366,718,724	2.24	3.11	4,271.26	4,514.83	-27.9%	-5.4%	1,852,312	1,738,498
1,998,605,818,287,672,800	2.67	3.66	5,360.22	5,156.08	-27.1%	4.0%	818,142	728,635
5,177,932,399,303,345,046	1.81	2.43	4,841.89	4,794.24	-25.5%	1.0%	2,020,559	1,892,896
311,448,953,162,816,591	1.67	2.21	4,966.63	4,950.52	-24.5%	0.3%	6,082,162	5,891,044
2,039,367,776,086,447,454	2.06	2.64	4,370.51	4,673.09	-22.0%	-6.5%	1,595,988	1,434,713
-4,630,321,108,541,466,571	1.85	2.37	6,686.09	7,551.46	-21.9%	-11.5%	1,194,508	1,054,325
4,857,361,656,543,545,479	2.06	2.57	6,169.29	6,087.94	-19.8%	1.3%	6,803,355	6,649,139
-2,865,465,467,459,569,344	4.03	4.97	4,598.46	4,962.30	-18.9%	-7.3%	675,848	492,385
-3,711,121,367,896,011,833	2.19	2.69	5,536.56	5,545.00	-18.5%	-0.2%	3,322,825	3,039,067
2,119,715,416,692,563,737	2.12	2.59	5,163.81	5,587.78	-18.4%	-7.6%	4,521,543	4,207,630
-2,162,498,661,799,343,237	2.25	2.73	5,273.50	5,409.38	-17.8%	-2.5%	69,006,283	62,537,335
5,750,867,047,671,580,791	2.33	2.81	6,826.60	7,577.41	-17.3%	-9.9%	3,431,958	2,940,280
-4,138,824,164,451,705,107	2.24	2.7	8,645.66	7,744.77	-17.2%	11.6%	456,362	378,939
-1,068,861,235,619,238,745	2.16	2.59	4,446.33	4,795.80	-16.4%	-7.3%	2,126,057	1,918,709
-7,580,909,833,117,018,375	2.41	2.83	6,976.54	7,004.32	-14.7%	-0.4%	2,886,613	2,769,822
8,611,629,356,743,080,984	2.83	3.29	6,816.13	7,410.76	-14.0%	-8.0%	5,002,103	4,380,977
3,133,716,806,509,821,992	1.92	2.14	5,348.65	5,242.35	-10.3%	2.0%	1,262,096	1,213,498
-7,973,680,817,747,944,938	1.93	2.12	4,134.72	4,980.52	-8.8%	-17.0%	1,497,259	1,218,770
707,343,854,139,029,255	1.94	2.13	4,356.99	5,236.06	-8.8%	-16.8%	1,525,823	1,246,963
1,164,822,038,571,945,223	2.87	3.11	3,953.72	3,101.66	-7.8%	27.5%	713,395	604,349
-1,275,781,459,639,371,202	3.19	3.36	4,763.06	2,974.75	-5.0%	60.1%	118,174	101,250
4,711,821,234,192,922,644	2.87	3	1,863.95	2,156.52	-4.2%	-13.6%	1,201,425	1,007,793
8,180,198,595,049,150,616	1.66	1.73	3,360.58	3,867.70	-4.0%	-13.1%	273,733	238,610
5,263,188,873,138,819,140	1.6	1.67	3,075.81	3,510.95	-3.9%	-12.4%	1,727,761	1,417,326
6,650,880,607,858,420,237	1.63	1.7	3,192.65	3,603.46	-3.9%	-11.4%	1,866,362	1,532,190
4,988,414,509,075,400,581	2.29	2.2	10,452.51	8,128.84	3.9%	28.6%	1,196,553	1,073,078
-4,897,081,641,892,712,607	2.09	2.17	3,726.17	4,182.68	-3.5%	-10.9%	1,418,369	1,253,726
-4,944,049,946,733,699,762	4.37	4.5	7,183.45	8,089.68	-3.0%	-11.2%	699,624	603,874
5,145,713,689,396,368,830	1.34	1.39	2,648.04	3,021.65	-3.0%	-12.4%	1,987,467	1,701,624
1,579,516,467,629,270,577	3.28	3.2	7,640.42	6,723.45	2.5%	13.6%	364,182	346,418
1,222,051,438,589,625,017	2.79	2.72	11,929.11	9,336.64	2.4%	27.8%	999,119	875,135
-4,871,709,966,839,072,996	1.9	1.95	6,653.93	7,428.36	-2.4%	-10.4%	76,561	62,725
-1,766,392,260,690,175,685	2.48	2.53	6,689.46	5,926.51	-2.1%	12.9%	2,013,801	1,733,190
-6,899,755,675,572,465,411	2.37	2.42	6,430.58	5,822.81	-1.9%	10.4%	1,797,860	1,540,579
1,295,090,440,394,638,876	2.44	2.49	6,031.69	5,413.05	-1.8%	11.4%	513,321	402,549
6,352,083,177,318,679,706	2.58	2.62	1,724.21	1,940.91	-1.5%	-11.2%	498,833	418,996

Showing 50 of 58 total rows

Click to see SQL

WITH
new AS (
    SELECT
        network_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY network_id
),
old AS (
    SELECT
        network_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY network_id
)
SELECT
    network_id,
    new.avg_pct_of_medicare_rate AS new_avg_pct_of_medicare_rate,
    old.avg_pct_of_medicare_rate AS old_avg_pct_of_medicare_rate,
    new.avg_canonical_rate AS new_avg_canonical_rate,
    old.avg_canonical_rate AS old_avg_canonical_rate,
    (new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0) AS medicare_rate_pct_change,
    (new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0) AS canonical_rate_pct_change,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count
FROM new
JOIN old USING (network_id)
WHERE (
    ABS((new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0)) > 0.1
   OR ABS((new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0)) > 0.1
) AND (
    new.roid_count > 500 AND old.roid_count > 500
)
ORDER BY ABS(medicare_rate_pct_change) DESC

4. Large ROID Count Swings (>10% change)

By Service Line

Count: 4 categories with significant ROID count changes

service_line	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Infectious Disease	10,320,407	3,654,986	6,665,421	182.4%
Lab/Path	20,273,749	9,596,644	10,677,105	111.3%
Rehab	8,103,178	7,338,077	765,101	10.4%
Consultative and Preventative Care	10,008,693	9,086,731	921,962	10.1%

Click to see SQL

WITH
new AS (
    SELECT
        service_line,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY service_line
),
old AS (
    SELECT
        service_line,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY service_line
)
SELECT
    service_line,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (service_line)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY ABS(roid_count_pct_change) DESC

By Provider Type

Count: 1 categories with significant ROID count changes

provider_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Physician Group	273,683,261	242,779,612	30,903,649	12.7%

Click to see SQL

WITH
new AS (
    SELECT
        provider_type,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY provider_type
),
old AS (
    SELECT
        provider_type,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY provider_type
)
SELECT
    provider_type,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (provider_type)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY ABS(roid_count_pct_change) DESC

By State

Count: 14 categories with significant ROID count changes

state	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
AK	1,092,571	662,182	430,389	65.0%
DC	3,607,164	2,986,006	621,158	20.8%
HI	1,878,328	1,599,859	278,469	17.4%
WY	1,216,447	1,048,266	168,181	16.0%
ID	3,514,150	3,086,975	427,175	13.8%
MN	9,781,660	8,628,837	1,152,823	13.4%
SD	2,153,125	1,912,487	240,638	12.6%
OR	6,773,415	6,017,592	755,823	12.6%
KS	7,012,535	6,270,394	742,141	11.8%
NE	4,198,367	3,761,551	436,816	11.6%
MT	2,499,635	2,243,918	255,717	11.4%
VT	674,308	605,849	68,459	11.3%
MA	15,151,534	13,700,381	1,451,153	10.6%
MD	5,482,496	4,960,649	521,847	10.5%

Click to see SQL

WITH
new AS (
    SELECT
        state,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY state
),
old AS (
    SELECT
        state,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY state
)
SELECT
    state,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (state)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY ABS(roid_count_pct_change) DESC

By Payer Network Name

Count: 69 categories with significant ROID count changes

payer_network_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Moda Health Connexus	675,848	492,385	183,463	37.3%
PreferredOne PPO	513,321	402,549	110,772	27.5%
Premera Blue Cross Alaska Heritage Network	426,794	335,333	91,461	27.3%
UHA Health Insurance PPO	258,527	206,235	52,292	25.4%
Blue Cross Blue Shield of Kansas City Preferredcare Blue	362,144	290,264	71,880	24.8%
Harvard Pilgrim Health Care HMO	1,497,259	1,218,770	278,489	22.9%
Blue Cross Blue Shield of New York (Excellus) Excellus Bluecross Blueshield	1,852,524	1,512,149	340,375	22.5%
Harvard Pilgrim Health Care Choicenet PPO	1,525,823	1,246,963	278,860	22.4%
Health New England PPO	76,561	62,725	13,836	22.1%
Health Alliance Plan (HAP) HMO	1,727,761	1,417,326	310,435	21.9%
Health Alliance Plan (HAP) PPO	1,866,362	1,532,190	334,172	21.8%
Regence Blue Cross Blue Shield ID PPO	283,352	234,086	49,266	21.0%
Blue Cross Blue Shield of Wyoming PPO	394,097	326,341	67,756	20.8%
Wellmark Blue Cross Blue Shield SD PPO	456,362	378,939	77,423	20.4%
Providence Health Plan PEBB Choice	982,402	815,938	166,464	20.4%
Blue Cross Blue Shield of Maryland & DC (CareFirst) HMO	182,312	152,592	29,720	19.5%
MetroPlus Health Gold	1,201,425	1,007,793	193,632	19.2%
Mass General Brigham Health Plan Commercial PPO	498,833	418,996	79,837	19.1%
Network Health Plan PPO	627,693	529,768	97,925	18.5%
Blue Cross Blue Shield of Hawaii (HMSA) HMO	305,479	258,413	47,066	18.2%
Blue Cross of Idaho PPO	713,395	604,349	109,046	18.0%
Ambetter HMO	1,987,467	1,701,624	285,843	16.8%
Aetna FL HMO	3,431,958	2,940,280	491,678	16.7%
Blue Cross Blue Shield of Vermont PPO	118,174	101,250	16,924	16.7%
Blue Cross Blue Shield of Minnesota HMO	1,797,860	1,540,579	257,281	16.7%
MetroPlus Health Essential Plan	1,908,515	1,637,787	270,728	16.5%
Mass General Brigham Health Plan HMO	1,517,969	1,304,388	213,581	16.4%
Blue Cross Blue Shield of Hawaii (HMSA) PPO	337,113	289,717	47,396	16.4%
Geisinger PPO	2,233,266	1,921,488	311,778	16.2%
Blue Cross Blue Shield of Minnesota Aware PPO	2,013,801	1,733,190	280,611	16.2%
Sutter Health Plus PPO	699,624	603,874	95,750	15.9%
Geisinger HMO	2,138,462	1,858,929	279,533	15.0%
Univera Healthcare PPO	731,374	636,157	95,217	15.0%
UPMC Health Plan Premium	1,970,552	1,717,029	253,523	14.8%
Blue Cross Blue Shield of Kansas Blue Choice	1,680,386	1,464,433	215,953	14.7%
Blue Cross Blue Shield of Rhode Island PPO	273,733	238,610	35,123	14.7%
Blue Cross Blue Shield of Montana Blue Preferred PPO	515,156	449,723	65,433	14.5%
CDPHP (Capital District Physicians Health Plan) PPO	458,117	400,828	57,289	14.3%
Aetna CA HMO	5,002,103	4,380,977	621,126	14.2%
Sanford Health Plan PPO	999,119	875,135	123,984	14.2%
Blue Cross Blue Shield of Arizona PPO	1,607,091	1,409,605	197,486	14.0%
Priority Health HMO	390,699	343,217	47,482	13.8%
Fidelis Essential Plan	721,753	635,714	86,039	13.5%
WPS PPO	1,518,004	1,339,629	178,375	13.3%
Wellmark Blue Cross Blue Shield HMO	1,194,508	1,054,325	140,183	13.3%
Blue Cross Blue Shield of Mississippi Preferred Provider Network	1,418,369	1,253,726	164,643	13.1%
Blue Cross Blue Shield of Arizona HMO	1,606,631	1,426,008	180,623	12.7%
Kaiser Permanente Kaiser Health Plan (CA)	7,368,975	6,550,695	818,280	12.5%
Kaiser Permanente Kaiser Health Plan (HI)	312,562	278,319	34,243	12.3%
Blue Cross Blue Shield of Nebraska Networkblue PPO	818,142	728,635	89,507	12.3%

Showing 50 of 69 total rows

Click to see SQL

WITH
new AS (
    SELECT
        payer_network_name,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY payer_network_name
),
old AS (
    SELECT
        payer_network_name,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY payer_network_name
)
SELECT
    payer_network_name,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (payer_network_name)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY ABS(roid_count_pct_change) DESC

By Network + Canonical Rate Source (Check for New Pairs with 0 Rates)

Count: 10 categories with significant ROID count changes

payer_id	payer_name	network_name	canonical_rate_source	n_old	n_new
461	Wellmark Blue Cross Blue Shield	IA HMO	payer	754,282	0
461	Wellmark Blue Cross Blue Shield	IA HMO	imputation	100,785	0
461	Wellmark Blue Cross Blue Shield	IA HMO	hospital	161,478	0
791	MotivHealth Insurance Company	MotivNet	imputation	103	103
397	Blue Cross Blue Shield of Rhode Island	PPO	imputation	126	140
317	Tufts Health Plan	PPO MA	payer	325	612
628	Premera Blue Cross	Alaska Heritage Network	imputation	9,457	759
42	Anthem	Blue Access Gated EPO	hospital	2,106	798
958	MetroPlus Health	Gold	imputation	1,177	1,177
958	MetroPlus Health	Gold	hospital	1,190	1,181

Click to see SQL

WITH
new AS (
    SELECT 
        payer_id,
        payer_name, 
        network_name, 
        canonical_rate_source, 
        count(distinct roid) AS n_new
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY 1,2,3,4
),
old AS (
    SELECT 
        payer_id,
        payer_name, 
        network_name, 
        canonical_rate_source, 
        count(distinct roid) AS n_old
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY 1,2,3,4
)
SELECT
    payer_id,
    payer_name,
    network_name,
    canonical_rate_source,
    n_old,
    COALESCE(n_new, 0) as n_new
FROM old
FULL OUTER JOIN new USING (payer_id, payer_name, network_name, canonical_rate_source)
ORDER BY COALESCE(n_new, 0) ASC
LIMIT 10

By Health System Name

Count: 97 categories with significant ROID count changes

provider_type	health_system_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Physician Group	Orlando Health	133,886	105,540	28,346	26.9%
Physician Group	Baptist Health South Florida	120,041	95,130	24,911	26.2%
Physician Group	Providence Health & Services - Oregon and Southwest Washington	227,048	180,058	46,990	26.1%
Physician Group	CentraCare Health System	176,447	141,868	34,579	24.4%
Physician Group	North Mississippi Health Services	140,196	112,879	27,317	24.2%
Physician Group	UCLA Health System	729,121	588,421	140,700	23.9%
Physician Group	AdventHealth	470,617	380,215	90,402	23.8%
Physician Group	Samaritan Health Services	116,899	94,621	22,278	23.5%
Physician Group	Hawaii Pacific Health	130,167	105,435	24,732	23.5%
Physician Group	The Queens Health Systems	126,287	102,409	23,878	23.3%
Physician Group	Aspirus Health	420,043	341,877	78,166	22.9%
Physician Group	Asante	119,704	98,056	21,648	22.1%
Physician Group	University of California San Diego Health System	290,519	238,571	51,948	21.8%
Physician Group	Sharp HealthCare	106,200	87,433	18,767	21.5%
Physician Group	Bellin Health	421,882	349,928	71,954	20.6%
Physician Group	MercyOne	201,104	167,486	33,618	20.1%
Physician Group	Johns Hopkins Medicine	149,997	126,695	23,302	18.4%
Physician Group	Allegheny Health Network	152,945	129,349	23,596	18.2%
Physician Group	Trinity Health	102,783	87,061	15,722	18.1%
Physician Group	Billings Clinic Health System	120,112	101,835	18,277	17.9%
Physician Group	SUNY Upstate Medical University	170,751	144,777	25,974	17.9%
Physician Group	Louisiana Childrens Medical Center Health System	228,551	194,497	34,054	17.5%
Physician Group	University of Michigan - Sparrow	170,813	145,794	25,019	17.2%
Physician Group	Avera Health	178,677	152,758	25,919	17.0%
Physician Group	ThedaCare	112,249	96,021	16,228	16.9%
Physician Group	UCI Health	510,074	437,159	72,915	16.7%
Physician Group	Stony Brook Medicine	375,605	322,033	53,572	16.6%
Physician Group	Appalachian Regional Healthcare	118,452	101,655	16,797	16.5%
Physician Group	Cape Cod Healthcare	134,313	115,503	18,810	16.3%
Physician Group	St Lukes University Health Network	402,435	346,518	55,917	16.1%
Physician Group	Community Medical Centers	236,157	203,359	32,798	16.1%
Physician Group	Bronson Healthcare	189,671	163,473	26,198	16.0%
Physician Group	University of Pittsburgh Medical Center	171,304	147,728	23,576	16.0%
Physician Group	Scripps Health	259,640	224,274	35,366	15.8%
Physician Group	Indiana University Health	379,993	328,372	51,621	15.7%
Physician Group	Ascension Wisconsin	228,928	197,983	30,945	15.6%
Physician Group	UMass Memorial Health Care	176,436	152,603	23,833	15.6%
Physician Group	Aurora Health Care	219,740	190,204	29,536	15.5%
Physician Group	Salem Health	110,995	96,131	14,864	15.5%
Physician Group	John Muir Health	160,650	139,876	20,774	14.9%
Physician Group	Vanderbilt Health	170,634	148,622	22,012	14.8%
Physician Group	The University of Vermont Health Network	287,027	250,027	37,000	14.8%
Physician Group	UPMC Central Pennsylvania	284,203	247,675	36,528	14.7%
Physician Group	Gundersen Health System	240,885	209,927	30,958	14.7%
Physician Group	Allegiance Health Management	152,981	133,423	19,558	14.7%
Physician Group	Mass General Brigham	400,249	349,406	50,843	14.6%
Physician Group	St Lukes Health System	104,298	91,081	13,217	14.5%
Physician Group	OSF HealthCare	357,794	313,510	44,284	14.1%
Physician Group	Baptist Health	268,789	235,764	33,025	14.0%
Physician Group	Wellstar MCG Health	105,258	92,457	12,801	13.8%

Showing 50 of 97 total rows

Click to see SQL

WITH
new AS (
    SELECT
        provider_type,
        health_system_name,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY 1,2
),
old AS (
    SELECT
        provider_type,
        health_system_name,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY 1,2
)
SELECT
    provider_type,
    health_system_name,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (provider_type, health_system_name)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
AND (
    new.roid_count > 100000 OR old.roid_count > 100000
)
ORDER BY ABS(roid_count_pct_change) DESC

By Canonical Rate Score

Count: 2 categories with significant ROID count changes

canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
2	13,164,651	3,441,727	9,722,924	282.5%
3	49,260,810	59,388,568	-10,127,758	-17.1%

Click to see SQL

WITH
new AS (
    SELECT
        canonical_rate_score,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_rate_score
),
old AS (
    SELECT
        canonical_rate_score,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_rate_score
)
SELECT
    canonical_rate_score,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (canonical_rate_score)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY ABS(roid_count_pct_change) DESC

By Canonical Rate Class

Count: 1 categories with significant ROID count changes

canonical_rate_class	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Transform	49,592,229	22,756,145	26,836,084	117.9%

Click to see SQL

WITH
new AS (
    SELECT
        canonical_rate_class,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_rate_class
),
old AS (
    SELECT
        canonical_rate_class,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_rate_class
)
SELECT
    canonical_rate_class,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (canonical_rate_class)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY ABS(roid_count_pct_change) DESC

By Canonical Rate Source

Count: 1 categories with significant ROID count changes

canonical_rate_source	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
payer	403,585,780	361,869,376	41,716,404	11.5%

Click to see SQL

WITH
new AS (
    SELECT
        canonical_rate_source,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_rate_source
),
old AS (
    SELECT
        canonical_rate_source,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_rate_source
)
SELECT
    canonical_rate_source,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (canonical_rate_source)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY ABS(roid_count_pct_change) DESC

By Canonical Contract Methodology

Count: 2 categories with significant ROID count changes

canonical_contract_methodology	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Unknown	14,299,321	12,182,342	2,116,979	17.4%
Fee Schedule	329,583,120	297,747,009	31,836,111	10.7%

Click to see SQL

WITH
new AS (
    SELECT
        canonical_contract_methodology,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_contract_methodology
),
old AS (
    SELECT
        canonical_contract_methodology,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_contract_methodology
)
SELECT
    canonical_contract_methodology,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (canonical_contract_methodology)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY ABS(roid_count_pct_change) DESC

By Canonical Crosswalk Method

Count: 2 categories with significant ROID count changes

canonical_crosswalk_method	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Crosswalked From CSTM-ALL to APR-DRG	915,276	776,582	138,694	17.9%
Crosswalked From APC to HCPCS	6,478,355	5,531,813	946,542	17.1%

Click to see SQL

WITH
new AS (
    SELECT
        canonical_crosswalk_method,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_crosswalk_method
),
old AS (
    SELECT
        canonical_crosswalk_method,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_crosswalk_method
)
SELECT
    canonical_crosswalk_method,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (canonical_crosswalk_method)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY ABS(roid_count_pct_change) DESC

By Canonical Gross Charge Type

Count: 3 categories with significant ROID count changes

canonical_gross_charge_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
komodo_gross_charge_state_median	9,334,384	7,601,888	1,732,496	22.8%
komodo_gross_charge_cbsa_median	1,958,936	1,696,932	262,004	15.4%
mrf_gross_charge_cbsa_median	11,682,570	10,293,463	1,389,107	13.5%

Click to see SQL

WITH
new AS (
    SELECT
        canonical_gross_charge_type,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_gross_charge_type
),
old AS (
    SELECT
        canonical_gross_charge_type,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_gross_charge_type
)
SELECT
    canonical_gross_charge_type,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (canonical_gross_charge_type)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY ABS(roid_count_pct_change) DESC

---