v2_4_1 → v2_4_2

Generated: 2026-03-17 19:09:14.
Filter: canonical_rate_score > 1.

---

1. Summary Statistics

Overview

• Total ROIDs: 642,842,847 → 1,020,366,918 (+377,524,071, +58.7%)

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
ASC	Outpatient	46,576,542	21,462,464	167	145	5,659	5,578	3,138	3,154
Physician Group	Professional	666,271,324	296,243,211	183	177	21,406	21,740	4,104	3,830
Hospital	Inpatient	59,131,946	59,364,628	181	177	5,976	5,968	1,711	1,711
Laboratory	Professional	13,034,316	12,774,428	179	171	3,728	3,711	922	916
Imaging Center	Professional	4,148,570	4,037,494	170	163	2,943	2,930	171	171
Hospital	Outpatient	231,204,220	248,960,622	188	180	6,007	5,999	7,629	7,512

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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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: 497 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT provider_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
),
old AS (
    SELECT DISTINCT provider_id, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
)
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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
),
old AS (
    SELECT DISTINCT payer_id, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
)
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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
),
old AS (
    SELECT DISTINCT network_id, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
)
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: 4955 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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
),
old AS (
    SELECT DISTINCT provider_id, network_id, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
)
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: 93 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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
),
old AS (
    SELECT DISTINCT bill_type, billing_code, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
)
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: 23599 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
Inpatient	527	inf	inf	38,542.66	43,623.11		-11.6%	16,533	15,465
Professional	9164136625644418629	3.15	0.91	925.69	70.26	244.7%	1217.5%	14,492	1,631
Professional	2867778108145911499	3.31	1.06	929.61	210.57	211.5%	341.5%	13,628	1,240
Professional	-1857918311266918042	3.98	1.37	2,044.90	267.54	189.8%	664.3%	11,231	3,442
Professional	-7781763660061443504	3.98	1.37	2,044.90	267.54	189.8%	664.3%	11,231	3,442
Professional	-5458665245145709647	3.98	1.37	2,044.90	267.54	189.8%	664.3%	11,231	3,442
Outpatient	6856	7.12	2.54	910.64	5,374.12	180.2%	-83.1%	928	62,181
Outpatient	4472	4.17	1.58	11,221.78	5,515.63	164.3%	103.5%	13,961	3,572
Outpatient	6855	7.12	2.7	910.64	5,510.76	163.3%	-83.5%	928	61,823
Inpatient	6277	5.44	2.09	117,425.49	50,755.91	160.4%	131.4%	1,613	1,706
Inpatient	9830	5.43	2.11	111,895.88	50,171.73	157.2%	123.0%	1,576	1,706
Professional	-2718255887486666313	4.71	1.85	182.07	905.21	155.4%	-79.9%	1,998	7,411
Professional	1564723820513311262	4.71	1.85	182.07	905.21	155.4%	-79.9%	1,998	7,411
Inpatient	6294	5.87	2.3	120,482.79	53,082.35	155.1%	127.0%	1,640	1,706
Inpatient	6299	5.32	2.12	115,874.36	52,718.92	150.8%	119.8%	1,605	1,706
Professional	-6635743926867485140	3.63	1.45	2,149.03	274.80	149.7%	682.0%	8,775	2,682
Professional	-8823675432567238805	3.63	1.45	2,149.03	274.80	149.7%	682.0%	8,775	2,682
Professional	4906784633219473154	3.66	1.47	2,081.05	273.63	148.3%	660.5%	11,565	4,032
Professional	749748930536889026	3.66	1.47	2,081.05	273.63	148.3%	660.5%	11,565	4,032
Professional	2530926514262842466	3.66	1.47	2,081.05	273.63	148.3%	660.5%	11,565	4,032
Professional	96430190332688784	3.66	1.47	2,081.05	273.63	148.3%	660.5%	11,565	4,032
Professional	-6207990687816752780	3.66	1.47	2,081.05	273.63	148.3%	660.5%	11,565	4,032
Professional	-4211309086894086532	3.66	1.47	2,081.05	273.63	148.3%	660.5%	11,565	4,032
Professional	-4173499991235388455	3.66	1.47	2,081.05	273.63	148.3%	660.5%	11,565	4,032
Professional	4267006233728183535	3.66	1.47	2,081.05	273.63	148.3%	660.5%	11,565	4,032
Professional	4648008225046110993	3.66	1.47	2,081.05	273.63	148.3%	660.5%	11,565	4,032
Professional	-5118931761662314135	3.66	1.47	2,081.05	273.63	148.3%	660.5%	11,565	4,032
Professional	-1789032016385453063	3.66	1.47	2,081.05	273.63	148.3%	660.5%	11,565	4,032
Professional	-6336639452455033076	3.66	1.47	2,081.05	273.63	148.3%	660.5%	11,565	4,032
Professional	-4113921521503489988	2.88	1.16	2,378.87	3,023.68	147.3%	-21.3%	13,166	4,675
Inpatient	6573	4.97	2.01	117,742.63	53,676.06	147.2%	119.4%	1,617	1,707
Inpatient	6576	4.96	2.01	117,742.63	53,676.06	147.2%	119.4%	1,617	1,707
Inpatient	6391	5.01	2.03	117,742.63	53,676.06	147.1%	119.4%	1,617	1,707
Inpatient	6306	5.25	2.19	126,001.16	58,728.13	140.3%	114.5%	1,664	1,708
Inpatient	6626	5.25	2.19	126,001.16	58,728.13	140.2%	114.5%	1,664	1,708
Inpatient	6631	5	2.08	129,584.51	58,950.13	140.1%	119.8%	1,678	1,708
Inpatient	6564	5.15	2.15	126,001.16	58,728.13	139.4%	114.5%	1,664	1,708
Inpatient	6390	5.14	2.15	126,001.16	58,728.13	139.3%	114.5%	1,664	1,708
Inpatient	6392	4.86	2.04	127,048.94	59,130.84	138.3%	114.9%	1,667	1,708
Inpatient	6668	4.77	2.01	127,048.94	59,130.84	137.4%	114.9%	1,667	1,708
Inpatient	6351	5.15	2.17	126,001.16	58,728.13	137.1%	114.5%	1,664	1,708
Inpatient	6424	4.64	1.96	126,001.16	58,728.13	136.4%	114.5%	1,664	1,708
Inpatient	9699	4.77	2.02	126,001.16	58,728.13	136.3%	114.5%	1,664	1,708
Inpatient	6305	4.59	1.96	126,001.16	58,728.13	134.5%	114.5%	1,664	1,708
Professional	-3456341506170562223	2.3	0.99	986.50	1,614.22	133.3%	-38.9%	20,502	2,952
Professional	-6535582143473791448	2.11	0.91	618.46	125.30	133.0%	393.6%	28,734	8,164
Professional	-3306173649138120255	3.59	1.54	2,286.90	298.68	132.8%	665.7%	9,952	3,223
Professional	5722851027690071014	3.59	1.54	2,286.90	298.68	132.8%	665.7%	9,952	3,223
Professional	-1949141443215327653	3.59	1.54	2,286.90	298.68	132.8%	665.7%	9,952	3,223
Professional	4609047229815327506	3.59	1.54	2,286.90	298.68	132.8%	665.7%	9,952	3,223

Showing 50 of 23599 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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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: 126762 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
-3,711,121,367,896,011,833	527	inf	inf	6,335.37	4,731.11		33.9%	4,845	3,910
-5,146,581,664,284,924,254	527	inf	inf	10,325.10	23,006.73		-55.1%	3,143	4,400
311,448,953,162,816,591	527	inf	inf	5,548.29	4,530.17		22.5%	6,962	6,219
-2,162,498,661,799,343,237	-4630720406761018877	4.63	0.85	349.12	71.25	443.3%	390.0%	2,004	1,603
2,119,715,416,692,563,737	-4630720406761018877	4.62	0.85	348.69	71.25	442.8%	389.4%	2,007	1,603
4,484,133,589,360,821,145	-4630720406761018877	4.62	0.85	348.42	71.25	442.7%	389.0%	2,003	1,603
-2,162,498,661,799,343,237	310648763012467535	5.53	1.06	1,319.37	184.93	419.6%	613.4%	2,147	1,349
4,484,133,589,360,821,145	310648763012467535	5.53	1.06	1,319.37	184.93	419.6%	613.4%	2,147	1,349
4,484,133,589,360,821,145	2884576090145768674	5	1.06	136.96	879.98	372.2%	-84.4%	631	2,477
-2,162,498,661,799,343,237	2884576090145768674	5	1.06	136.96	879.98	372.2%	-84.4%	631	2,477
8,611,629,356,743,080,984	-8217376086774675600	5.48	1.19	2,247.03	2,028.28	362.0%	10.8%	2,242	1,445
-7,580,909,833,117,018,375	2192970950410698770	7.45	1.62	1,216.89	413.43	359.9%	194.3%	980	3,961
-2,162,498,661,799,343,237	-1475316224070078299	5.26	1.15	1,351.61	1,254.92	356.2%	7.7%	2,591	2,207
-7,580,909,833,117,018,375	3663949834212260214	5.99	1.32	2,205.35	1,434.19	354.9%	53.8%	1,569	1,970
8,361,580,493,441,765,265	1211	8.3	1.84	47,626.70	35,579.52	351.1%	33.9%	3,450	2,669
5,810,723,572,563,874,815	1211	8.3	1.84	47,626.70	35,579.52	351.1%	33.9%	3,450	2,669
4,281,965,180,796,802,413	1211	8.3	1.84	47,618.61	35,579.52	351.1%	33.8%	3,450	2,669
4,043,190,250,846,473,271	10523	3.59	0.8	18,175.84	16,212.64	347.1%	12.1%	6,641	1,591
8,611,629,356,743,080,984	1812415066396978834	6.24	1.42	1,383.96	177.61	339.1%	679.2%	1,710	2,156
-7,580,909,833,117,018,375	3565199733945696002	7.46	1.73	1,218.11	423.44	331.6%	187.7%	979	3,966
-7,580,909,833,117,018,375	-5041970919340684827	7.46	1.73	1,218.11	424.16	331.0%	187.2%	979	3,966
5,810,723,572,563,874,815	-2029433978327019976	3.36	0.79	179.03	66.93	326.7%	167.5%	1,287	1,395
8,611,629,356,743,080,984	3165760820303762969	4.71	1.11	2,997.40	1,797.43	324.1%	66.8%	1,550	1,344
8,611,629,356,743,080,984	-4113921521503489988	4.71	1.11	2,997.40	1,797.43	324.1%	66.8%	1,550	1,344
4,484,133,589,360,821,145	-4113921521503489988	4.7	1.11	3,010.75	1,797.43	323.9%	67.5%	1,544	1,344
4,484,133,589,360,821,145	3165760820303762969	4.7	1.11	3,010.75	1,797.43	323.9%	67.5%	1,544	1,344
-2,162,498,661,799,343,237	3165760820303762969	4.7	1.11	3,012.11	1,797.43	323.8%	67.6%	1,543	1,344
-2,162,498,661,799,343,237	-4113921521503489988	4.7	1.11	3,012.11	1,797.43	323.8%	67.6%	1,543	1,344
8,611,629,356,743,080,984	611422980479900442	6.24	1.55	1,383.75	148.59	303.3%	831.3%	1,675	1,332
4,484,133,589,360,821,145	-7878858586204539757	3.55	0.9	1,112.00	1,906.06	292.7%	-41.7%	2,499	1,246
-2,162,498,661,799,343,237	-7878858586204539757	3.55	0.9	1,111.98	1,906.06	292.7%	-41.7%	2,499	1,246
2,119,715,416,692,563,737	-7878858586204539757	3.55	0.9	1,111.14	1,906.06	292.5%	-41.7%	2,501	1,246
-7,304,296,722,942,665,713	7092353783723240310	3.66	0.94	2,069.98	1,060.60	290.2%	95.2%	924	2,272
4,484,133,589,360,821,145	-1025741242917370462	4.55	1.17	2,318.27	435.97	288.5%	431.8%	3,418	830
-2,162,498,661,799,343,237	-1025741242917370462	4.55	1.17	2,315.16	435.97	288.4%	431.0%	3,415	830
8,611,629,356,743,080,984	574183652641257194	5.32	1.38	1,785.92	198.36	284.6%	800.4%	3,056	1,199
8,611,629,356,743,080,984	1449350649491743828	4.68	1.22	3,604.90	1,548.41	283.4%	132.8%	801	1,992
8,611,629,356,743,080,984	-2920077940434711393	4.77	1.27	4,016.29	176.50	276.8%	2175.5%	804	1,582
2,039,367,776,086,447,454	-7234755996163898053	5.92	1.59	1,330.86	187.34	273.6%	610.4%	1,924	1,486
-7,580,909,833,117,018,375	4120082186499671668	5.27	1.42	2,087.86	1,003.87	272.5%	108.0%	2,261	2,954
-7,580,909,833,117,018,375	-7590591966914886597	6	1.62	1,411.82	251.07	269.5%	462.3%	1,688	2,236
-7,995,682,717,897,123,039	30274	5.13	1.4	2,340.96	155.00	266.4%	1410.3%	2,528	520
8,611,629,356,743,080,984	-731403366484606185	4.22	1.16	2,747.00	1,623.06	263.9%	69.2%	1,760	1,876
8,611,629,356,743,080,984	-6818187387786801747	4.57	1.27	1,557.26	166.86	259.4%	833.2%	3,397	1,804
-8,508,828,306,067,810,497	878	4.94	1.4	34,811.66	27,052.65	253.2%	28.7%	6,989	844
4,484,133,589,360,821,145	-8217376086774675600	4.2	1.19	1,758.18	3,175.54	252.0%	-44.6%	2,915	877
4,484,133,589,360,821,145	-1962605836139962852	3	0.85	325.53	54.36	251.9%	498.9%	1,725	1,500
-2,162,498,661,799,343,237	-8217376086774675600	4.19	1.19	1,759.61	3,175.54	251.8%	-44.6%	2,911	877
-2,162,498,661,799,343,237	-1962605836139962852	3	0.85	325.44	54.36	251.7%	498.7%	1,723	1,500
8,611,629,356,743,080,984	-3637307436988807201	5.29	1.5	1,763.97	259.13	251.6%	580.7%	3,060	1,641

Showing 50 of 126762 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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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: 134518 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
-5,146,581,664,284,924,254	527	Inpatient	inf	inf	33,305.71	54,787.95		-39.2%	667	1,646
-3,711,121,367,896,011,833	527	Inpatient	inf	inf	13,698.35	27,729.38		-50.6%	1,366	189
8,361,580,493,441,765,265	1211	Outpatient	14.42	1.64	58,476.11	37,448.92	779.6%	56.1%	1,740	959
4,281,965,180,796,802,413	1211	Outpatient	14.42	1.64	58,476.11	37,448.92	779.6%	56.1%	1,740	959
5,810,723,572,563,874,815	1211	Outpatient	14.42	1.64	58,476.11	37,448.92	779.6%	56.1%	1,740	959
-2,162,498,661,799,343,237	-4630720406761018877	Professional	4.63	0.85	349.12	71.25	443.3%	390.0%	2,004	1,603
2,119,715,416,692,563,737	-4630720406761018877	Professional	4.62	0.85	348.69	71.25	442.8%	389.4%	2,007	1,603
4,484,133,589,360,821,145	-4630720406761018877	Professional	4.62	0.85	348.42	71.25	442.7%	389.0%	2,003	1,603
-2,162,498,661,799,343,237	310648763012467535	Professional	5.53	1.06	1,319.37	184.93	419.6%	613.4%	2,147	1,349
4,484,133,589,360,821,145	310648763012467535	Professional	5.53	1.06	1,319.37	184.93	419.6%	613.4%	2,147	1,349
4,484,133,589,360,821,145	2884576090145768674	Professional	5	1.06	136.96	879.98	372.2%	-84.4%	631	2,477
-2,162,498,661,799,343,237	2884576090145768674	Professional	5	1.06	136.96	879.98	372.2%	-84.4%	631	2,477
8,611,629,356,743,080,984	-8217376086774675600	Professional	5.48	1.19	2,247.03	2,028.28	362.0%	10.8%	2,242	1,445
-7,580,909,833,117,018,375	2192970950410698770	Professional	7.45	1.62	1,216.89	413.43	359.9%	194.3%	980	3,961
-2,162,498,661,799,343,237	-1475316224070078299	Professional	5.26	1.15	1,351.61	1,254.92	356.2%	7.7%	2,591	2,207
-7,580,909,833,117,018,375	3663949834212260214	Professional	5.99	1.32	2,205.35	1,434.19	354.9%	53.8%	1,569	1,970
8,611,629,356,743,080,984	1812415066396978834	Professional	6.24	1.42	1,383.96	177.61	339.1%	679.2%	1,710	2,156
-7,580,909,833,117,018,375	3565199733945696002	Professional	7.46	1.73	1,218.11	423.44	331.6%	187.7%	979	3,966
-7,580,909,833,117,018,375	-5041970919340684827	Professional	7.46	1.73	1,218.11	424.16	331.0%	187.2%	979	3,966
5,810,723,572,563,874,815	-2029433978327019976	Professional	3.36	0.79	179.03	66.93	326.7%	167.5%	1,287	1,395
8,611,629,356,743,080,984	3165760820303762969	Professional	4.71	1.11	2,997.40	1,797.43	324.1%	66.8%	1,550	1,344
8,611,629,356,743,080,984	-4113921521503489988	Professional	4.71	1.11	2,997.40	1,797.43	324.1%	66.8%	1,550	1,344
4,484,133,589,360,821,145	-4113921521503489988	Professional	4.7	1.11	3,010.75	1,797.43	323.9%	67.5%	1,544	1,344
4,484,133,589,360,821,145	3165760820303762969	Professional	4.7	1.11	3,010.75	1,797.43	323.9%	67.5%	1,544	1,344
-2,162,498,661,799,343,237	-4113921521503489988	Professional	4.7	1.11	3,012.11	1,797.43	323.8%	67.6%	1,543	1,344
-2,162,498,661,799,343,237	3165760820303762969	Professional	4.7	1.11	3,012.11	1,797.43	323.8%	67.6%	1,543	1,344
8,611,629,356,743,080,984	611422980479900442	Professional	6.24	1.55	1,383.75	148.59	303.3%	831.3%	1,675	1,332
4,484,133,589,360,821,145	-7878858586204539757	Professional	3.55	0.9	1,112.00	1,906.06	292.7%	-41.7%	2,499	1,246
-2,162,498,661,799,343,237	-7878858586204539757	Professional	3.55	0.9	1,111.98	1,906.06	292.7%	-41.7%	2,499	1,246
2,119,715,416,692,563,737	-7878858586204539757	Professional	3.55	0.9	1,111.14	1,906.06	292.5%	-41.7%	2,501	1,246
-7,304,296,722,942,665,713	7092353783723240310	Professional	3.66	0.94	2,069.98	1,060.60	290.2%	95.2%	924	2,272
4,484,133,589,360,821,145	-1025741242917370462	Professional	4.55	1.17	2,318.27	435.97	288.5%	431.8%	3,418	830
-2,162,498,661,799,343,237	-1025741242917370462	Professional	4.55	1.17	2,315.16	435.97	288.4%	431.0%	3,415	830
8,611,629,356,743,080,984	574183652641257194	Professional	5.32	1.38	1,785.92	198.36	284.6%	800.4%	3,056	1,199
8,611,629,356,743,080,984	1449350649491743828	Professional	4.68	1.22	3,604.90	1,548.41	283.4%	132.8%	801	1,992
8,611,629,356,743,080,984	-2920077940434711393	Professional	4.77	1.27	4,016.29	176.50	276.8%	2175.5%	804	1,582
2,039,367,776,086,447,454	-7234755996163898053	Professional	5.92	1.59	1,330.86	187.34	273.6%	610.4%	1,924	1,486
-7,580,909,833,117,018,375	4120082186499671668	Professional	5.27	1.42	2,087.86	1,003.87	272.5%	108.0%	2,261	2,954
-7,580,909,833,117,018,375	-7590591966914886597	Professional	6	1.62	1,411.82	251.07	269.5%	462.3%	1,688	2,236
-7,995,682,717,897,123,039	30274	Outpatient	5.13	1.4	2,340.96	155.00	266.4%	1410.3%	2,528	520
8,611,629,356,743,080,984	-731403366484606185	Professional	4.22	1.16	2,747.00	1,623.06	263.9%	69.2%	1,760	1,876
8,611,629,356,743,080,984	-6818187387786801747	Professional	4.57	1.27	1,557.26	166.86	259.4%	833.2%	3,397	1,804
4,484,133,589,360,821,145	-8217376086774675600	Professional	4.2	1.19	1,758.18	3,175.54	252.0%	-44.6%	2,915	877
4,484,133,589,360,821,145	-1962605836139962852	Professional	3	0.85	325.53	54.36	251.9%	498.9%	1,725	1,500
-2,162,498,661,799,343,237	-8217376086774675600	Professional	4.19	1.19	1,759.61	3,175.54	251.8%	-44.6%	2,911	877
-2,162,498,661,799,343,237	-1962605836139962852	Professional	3	0.85	325.44	54.36	251.7%	498.7%	1,723	1,500
8,611,629,356,743,080,984	-3637307436988807201	Professional	5.29	1.5	1,763.97	259.13	251.6%	580.7%	3,060	1,641
8,611,629,356,743,080,984	7166667426672543381	Professional	4.88	1.39	2,270.89	1,778.69	251.3%	27.7%	3,463	1,616
4,484,133,589,360,821,145	-7032371851563636773	Professional	3.01	0.87	406.00	149.98	245.8%	170.7%	1,775	1,889
-2,162,498,661,799,343,237	-7032371851563636773	Professional	3.01	0.87	405.81	149.98	245.8%	170.6%	1,774	1,889

Showing 50 of 134518 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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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: 159 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
-3,776,001,016,975,145,508	inf	inf	4,069.55	9,972.13		-59.2%	95,115,557	38,334,136
4,857,361,656,543,545,479	inf	inf	5,536.73	7,504.48		-26.2%	7,719,985	5,503,960
4,484,133,589,360,821,145	inf	inf	4,314.88	6,222.09		-30.7%	92,421,658	66,838,200
2,469,641,715,717,297,819	inf	inf	4,024.41	10,048.60		-60.0%	97,514,681	38,955,439
311,448,953,162,816,591	inf	inf	4,593.12	6,403.06		-28.3%	7,109,282	4,902,519
-5,146,581,664,284,924,254	inf	inf	6,205.98	10,406.89		-40.4%	4,072,741	2,779,519
4,281,965,180,796,802,413	inf	inf	3,166.76	4,764.00		-33.5%	114,711,371	73,016,805
-2,162,498,661,799,343,237	inf	inf	4,138.75	6,208.51		-33.3%	96,670,501	66,630,108
-3,711,121,367,896,011,833	inf	inf	4,700.09	7,002.01		-32.9%	4,820,153	3,638,397
8,361,580,493,441,765,265	inf	inf	3,246.80	4,880.15		-33.5%	116,997,485	75,306,728
6,974,535,101,123,239,131	1.6	3.05	2,997.74	10,563.28	-47.7%	-71.6%	1,240,118	503,462
-2,510,676,791,703,302,876	1.87	3.12	5,704.44	15,809.40	-39.9%	-63.9%	2,998,263	1,056,699
8,816,084,628,491,357,618	1.91	3.18	5,842.82	16,176.72	-39.8%	-63.9%	2,780,495	939,966
-2,685,661,158,361,719,346	1.49	2.47	2,872.52	9,283.33	-39.6%	-69.1%	2,225,272	585,034
-4,027,592,970,285,917,801	1.6	2.6	3,835.80	10,116.95	-38.5%	-62.1%	1,943,652	746,263
-7,942,674,608,514,495,282	2.37	1.78	18,299.50	5,496.64	32.9%	232.9%	422,427	1,737,406
-6,543,868,296,982,650,519	1.84	2.7	5,581.32	12,717.61	-31.9%	-56.1%	6,188,869	2,434,853
-3,954,532,594,486,231,313	2.03	2.92	5,408.61	12,453.52	-30.6%	-56.6%	2,550,770	908,558
4,911,047,023,988,898,996	1.94	2.77	6,760.04	13,698.44	-30.1%	-50.7%	3,538,834	1,695,391
-8,822,629,802,732,873,104	1.69	2.39	4,084.63	11,876.56	-29.3%	-65.6%	2,320,123	704,877
1,178,549,910,471,784,015	1.94	2.74	5,499.14	12,153.27	-29.3%	-54.8%	1,010,775	496,310
-8,973,307,920,338,611,544	2.06	2.91	6,090.16	12,634.51	-29.0%	-51.8%	1,807,747	732,557
3,973,810,847,438,814,798	3.47	4.86	6,764.22	11,595.36	-28.7%	-41.7%	1,171,468	755,860
6,091,386,345,134,788,986	2.22	3.11	4,634.12	10,917.22	-28.5%	-57.6%	468,407	180,644
-7,420,630,624,364,773,044	1.7	2.36	4,052.26	8,660.43	-27.7%	-53.2%	1,522,693	733,954
7,058,457,158,512,878,544	2.4	3.26	10,798.36	14,497.79	-26.5%	-25.5%	1,716,511	1,364,070
-6,486,550,968,171,266,793	1.94	2.6	5,580.67	12,914.38	-25.4%	-56.8%	4,119,558	1,724,999
8,180,198,595,049,150,616	1.43	1.91	2,743.38	10,473.94	-24.9%	-73.8%	426,273	77,723
5,325,827,911,684,334,760	2.13	2.82	3,397.92	8,065.37	-24.3%	-57.9%	486,401	174,877
-7,695,283,351,826,393,948	2.22	2.86	5,217.08	8,640.48	-22.6%	-39.6%	1,297,277	803,294
5,750,867,047,671,580,791	1.91	2.4	5,148.53	8,070.15	-20.4%	-36.2%	5,169,609	3,429,850
1,134,341,852,115,164,929	1.38	1.69	2,833.94	5,303.66	-18.7%	-46.6%	1,575,058	991,207
-8,403,435,045,225,894,954	1.81	2.22	2,522.61	10,568.17	-18.6%	-76.1%	933,203	193,788
-4,211,968,839,930,412,133	1.97	2.4	3,263.66	8,201.72	-18.0%	-60.2%	3,127,670	1,022,228
4,299,532,402,194,607,519	2.91	3.53	13,643.52	16,607.93	-17.5%	-17.8%	1,868,476	1,519,743
-7,227,420,687,598,643,435	1.83	2.21	5,696.51	6,708.94	-17.5%	-15.1%	1,192,419	910,944
6,638,296,964,362,884,623	1.9	2.31	5,095.07	7,225.34	-17.5%	-29.5%	333,968	232,789
4,615,092,770,909,199,842	1.84	2.23	3,877.70	10,522.03	-17.3%	-63.1%	2,661,636	842,142
-1,068,861,235,619,238,745	1.91	2.29	3,528.61	5,223.24	-16.6%	-32.4%	3,176,617	2,016,469
2,039,367,776,086,447,454	1.79	2.15	3,517.61	5,255.82	-16.5%	-33.1%	2,387,830	1,655,499
-7,580,909,833,117,018,375	1.99	2.37	5,488.71	7,550.80	-16.0%	-27.3%	3,862,004	3,080,850
3,267,736,702,885,179,114	1.93	2.3	4,096.18	6,750.62	-15.9%	-39.3%	3,148,766	2,326,963
2,569,506,757,851,818,343	2.04	2.42	5,655.19	8,261.85	-15.4%	-31.6%	442,573	322,904
-2,105,332,499,153,538,685	1.9	2.25	5,900.62	8,495.53	-15.4%	-30.5%	1,384,914	882,529
1,579,516,467,629,270,577	2.11	2.47	5,813.01	8,427.51	-14.4%	-31.0%	416,974	316,287
-3,949,448,820,262,323,978	1.94	2.25	5,067.55	7,102.29	-13.8%	-28.6%	1,644,076	1,136,304
7,616,836,552,481,822,175	1.83	2.12	3,833.97	6,135.32	-13.8%	-37.5%	9,235,442	5,296,836
-1,884,235,521,684,351,163	3.41	3.01	3,075.79	8,635.37	13.4%	-64.4%	17,031	5,841
-434,992,428,366,718,724	2.14	2.46	3,896.58	6,137.10	-13.2%	-36.5%	2,688,672	1,635,883
5,373,876,635,451,223,842	2.07	2.37	3,532.51	5,812.97	-12.7%	-39.2%	5,560,469	4,293,107

Showing 50 of 159 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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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: 27 categories with significant ROID count changes

service_line	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Transplant	424,893	97,655	327,238	335.1%
Anesthesia	43,751,368	18,481,666	25,269,702	136.7%
Consultative and Preventative Care	28,015,137	12,829,088	15,186,049	118.4%
Behavioral Health	13,606,135	6,469,633	7,136,502	110.3%
Cardiovascular	57,711,486	31,153,672	26,557,814	85.2%
Ophthalmology	34,093,685	18,656,403	15,437,282	82.7%
Nephrology	2,634,174	1,494,526	1,139,648	76.3%
MSK	112,868,181	65,824,942	47,043,239	71.5%
Dermatology	52,831,203	31,312,512	21,518,691	68.7%
Rehab	12,992,251	7,898,387	5,093,864	64.5%
Gastrointestinal	45,425,306	28,050,824	17,374,482	61.9%
Neurology	39,156,365	24,221,460	14,934,905	61.7%
Radiology	161,191,315	100,675,630	60,515,685	60.1%
Infectious Disease	48,113,604	30,564,595	17,549,009	57.4%
Obstetrics	13,551,494	8,721,317	4,830,177	55.4%
Hematology	3,545,931	2,333,792	1,212,139	51.9%
Reproductive	16,458,716	11,125,621	5,333,095	47.9%
ENT	24,221,832	16,506,825	7,715,007	46.7%
Urology	19,430,698	13,439,244	5,991,454	44.6%
Pulmonology	14,165,952	10,084,024	4,081,928	40.5%
Oncology	18,123,790	13,740,835	4,382,955	31.9%
Lab/Path	73,290,129	55,764,175	17,525,954	31.4%
Endocrinology	2,929,475	2,252,143	677,332	30.1%
Emergency	1,248,346	996,117	252,229	25.3%
Trauma	2,858,046	2,332,214	525,832	22.5%
Diabetes	453,115	376,663	76,452	20.3%
DME and Supplies	3,023,976	2,529,332	494,644	19.6%

Click to see SQL

WITH
new AS (
    SELECT
        service_line,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY service_line
),
old AS (
    SELECT
        service_line,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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: 2 categories with significant ROID count changes

provider_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Physician Group	666,271,324	296,243,211	370,028,113	124.9%
ASC	46,576,542	21,462,464	25,114,078	117.0%

Click to see SQL

WITH
new AS (
    SELECT
        provider_type,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY provider_type
),
old AS (
    SELECT
        provider_type,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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

Error: Execution failed on sql: WITH new AS ( SELECT state, COUNT(DISTINCT roid) AS roid_count FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged WHERE canonical_rate_score > 1 AND network_type != 'NARROW' AND network_class = 'Commercial' GROUP BY state ), old AS ( SELECT state, COUNT(DISTINCT roid) AS roid_count FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged WHERE canonical_rate_score > 1 AND network_type != 'NARROW' AND network_class = 'Commercial' 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 TrinoQueryError(type=INTERNAL_ERROR, name=PAGE_TRANSPORT_TIMEOUT, message="Encountered too many errors talking to a worker node. The node may have crashed or be under too much load. This is probably a transient issue, so please retry your query in a few minutes. (http://10.35.123.171:8080/v1/task/20260317_232843_25579_pdssz.4.10.0/results/62/7 - 7 failures, failure duration 61.54s, total failed request time 71.55s)", query_id=20260317_232843_25579_pdssz) unable to rollback

By Payer Network Name

Count: 153 categories with significant ROID count changes

payer_network_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Harvard Pilgrim Health Care Choicenet PPO	2,110,077	316,059	1,794,018	567.6%
Blue Cross Blue Shield of Rhode Island PPO	426,273	77,723	348,550	448.5%
Regence Blue Cross Blue Shield UT PPO	933,203	193,788	739,415	381.6%
PreferredOne PPO	703,997	183,458	520,539	283.7%
Blue Cross Blue Shield of Arizona HMO	2,225,272	585,034	1,640,238	280.4%
Cigna NJ HMO	2,320,123	704,877	1,615,246	229.2%
Cigna NC HMO	2,661,636	842,142	1,819,494	216.1%
Cigna New England HMO	3,127,670	1,022,228	2,105,442	206.0%
Geisinger HMO	2,780,495	939,966	1,840,529	195.8%
Nomi Health	17,031	5,841	11,190	191.6%
Geisinger PPO	2,998,263	1,056,699	1,941,564	183.7%
Anthem VA HMO	2,550,770	908,558	1,642,212	180.7%
Cigna NH HMO	486,401	174,877	311,524	178.1%
Kaiser Permanente Kaiser Health Plan (HI)	288,415	107,463	180,952	168.4%
Kaiser Permanente Kaiser Health Plan (CA)	7,644,673	2,915,913	4,728,760	162.2%
Cigna AZ HMO	1,943,652	746,263	1,197,389	160.5%
Cigna ME HMO	468,407	180,644	287,763	159.3%
Mass General Brigham Health Plan Commercial PPO	682,107	265,615	416,492	156.8%
Cigna CA HMO	6,188,869	2,434,853	3,754,016	154.2%
Cigna National PPO	97,514,681	38,955,439	58,559,242	150.3%
MVP Health Care MVP EPO / PPO	2,549,602	1,024,733	1,524,869	148.8%
Cigna National OAP	95,115,557	38,334,136	56,781,421	148.1%
Anthem IN HMO	1,807,747	732,557	1,075,190	146.8%
Cigna AL HMO	1,240,118	503,462	736,656	146.3%
MVP Health Care HMO	2,531,501	1,046,546	1,484,955	141.9%
Cigna NY HMO	4,119,558	1,724,999	2,394,559	138.8%
Premera Blue Cross Alaska Heritage Network	527,975	250,460	277,515	110.8%
Cigna FL HMO	3,538,834	1,695,391	1,843,443	108.7%
Cigna TN HMO	1,522,693	733,954	788,739	107.5%
Cigna VA HMO	1,010,775	496,310	514,465	103.7%
Mass General Brigham Health Plan HMO	1,909,451	938,454	970,997	103.5%
Health Alliance Plan (HAP) HMO	2,840,076	1,502,393	1,337,683	89.0%
Blue Cross Blue Shield of New York (Excellus) Excellus Bluecross Blueshield	2,891,305	1,576,867	1,314,438	83.4%
Health Alliance Plan (HAP) PPO	2,965,461	1,620,300	1,345,161	83.0%
Univera Healthcare PPO	1,048,780	578,767	470,013	81.2%
HealthLink HMO	1,564,972	876,417	688,555	78.6%
UHA Health Insurance PPO	372,293	209,601	162,692	77.6%
HealthLink PPO	1,559,602	882,101	677,501	76.8%
Blue Cross Blue Shield of New Jersey (Horizon) OMNIA	422,427	1,737,406	-1,314,979	-75.7%
SummaCare SCCONNECT	3,517,746	2,012,679	1,505,067	74.8%
Blue Shield of California Group PPO	9,235,442	5,296,836	3,938,606	74.4%
Blue Shield of California HMO	8,383,083	4,826,583	3,556,500	73.7%
Blue Cross Blue Shield of Alabama Preferred PPO	2,678,136	1,542,810	1,135,326	73.6%
Blue Cross Blue Shield of Kansas City Preferredcare Blue	505,084	291,180	213,904	73.5%
MetroPlus Health Gold	1,370,882	790,775	580,107	73.4%
Blue Cross Blue Shield of Michigan HMO	3,183,192	1,860,919	1,322,273	71.1%
Aetna NV HMO	918,458	539,399	379,059	70.3%
Anthem NY PPO	4,099,835	2,417,502	1,682,333	69.6%
Premera Blue Cross HMO	1,359,098	802,369	556,729	69.4%
HealthSmart Preferred PPO	604,470	357,727	246,743	69.0%

Showing 50 of 153 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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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
42	Anthem	GA OPEN ACCESS	hospital	259,133	0
42	Anthem	OH PPO	hospital	342,725	0
42	Anthem	CT Century Preferred PPO	hospital	60,754	0
160	Blue Cross Blue Shield of Alabama	Preferred PPO	imputation	2,940	0
42	Anthem	IN Blue Access PPO	hospital	201,934	0
42	Anthem	NH OPEN ACCESS	hospital	48,505	0
42	Anthem	CA Blue Cross PPO	hospital	515,821	0
42	Anthem	CO Blue Preferred PPO	hospital	107,982	0
42	Anthem	ME Blue Choice PPO	hospital	13,113	0
726	Capital Health Plan	PPO	payer	24,165	0

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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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: 502 categories with significant ROID count changes

provider_type	health_system_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
ASC	Alliance Spine and Pain Centers	101,939	20,808	81,131	389.9%
Physician Group	DCH Health System	121,113	28,350	92,763	327.2%
ASC	Sutter Health	474,197	116,769	357,428	306.1%
Physician Group	Baptist Health South Florida	322,262	97,385	224,877	230.9%
Physician Group	HCA Central & West Texas Division - St Davids HealthCare	111,446	34,565	76,881	222.4%
Physician Group	Cape Cod Healthcare	180,212	56,251	123,961	220.4%
Physician Group	HCA Far West Division - Sunrise Health	114,572	36,698	77,874	212.2%
Physician Group	Advocate Health Care	128,899	41,735	87,164	208.9%
Physician Group	Piedmont Augusta	109,418	35,978	73,440	204.1%
Physician Group	St Lukes Hospital Health System	104,547	34,924	69,623	199.4%
ASC	Orlando Health	128,958	43,617	85,341	195.7%
Physician Group	BayCare Health System	385,434	135,035	250,399	185.4%
Physician Group	UMC Health System	113,872	40,053	73,819	184.3%
Physician Group	Circle Health	388,140	136,603	251,537	184.1%
ASC	PE GI Solutions	123,779	43,975	79,804	181.5%
Physician Group	Atrium Health Navicent	114,961	41,440	73,521	177.4%
Physician Group	South County Health	118,325	42,673	75,652	177.3%
ASC	Baylor Scott & White Health	303,768	109,764	194,004	176.7%
Physician Group	Ascension St Vincents HealthCare	164,153	59,510	104,643	175.8%
Physician Group	Sutter Health	336,306	122,602	213,704	174.3%
Physician Group	Northside Hospital System	149,979	55,337	94,642	171.0%
Physician Group	St Josephs / Candler	119,530	44,397	75,133	169.2%
ASC	Hoag Health System	109,107	40,727	68,380	167.9%
Physician Group	Jackson Health System	124,191	47,251	76,940	162.8%
Physician Group	Boston Medical Center Corporation	874,403	332,800	541,603	162.7%
ASC	Proliance Surgeons	149,700	58,277	91,423	156.9%
Physician Group	Infirmary Health System	171,685	66,946	104,739	156.5%
Physician Group	Premier Health	253,560	98,988	154,572	156.2%
Physician Group	Wellstar Health System	134,075	52,488	81,587	155.4%
Physician Group	Commonwealth Health	121,995	47,911	74,084	154.6%
ASC	Aurora Health Care	102,076	40,253	61,823	153.6%
Physician Group	North Mississippi Health Services	263,682	104,518	159,164	152.3%
Physician Group	University of Utah Health	187,198	74,840	112,358	150.1%
Physician Group	Carson Tahoe Health	110,983	44,432	66,551	149.8%
ASC	Monterey Peninsula Surgery Centers	118,833	47,905	70,928	148.1%
Physician Group	Providence Health & Services - Southern California	258,664	104,599	154,065	147.3%
Physician Group	Penn Medicine Lancaster General Health	364,079	148,298	215,781	145.5%
Physician Group	Huntsville Hospital Health System	297,273	121,185	176,088	145.3%
Physician Group	OhioHealth	355,091	145,041	210,050	144.8%
Physician Group	Oswego Health	123,665	50,600	73,065	144.4%
Physician Group	Mercy Medical Center	213,565	87,785	125,780	143.3%
Physician Group	Scripps Health	480,689	197,756	282,933	143.1%
Physician Group	Saint Clair Memorial Hospital	156,548	64,821	91,727	141.5%
Physician Group	TMC Health	333,475	138,165	195,310	141.4%
Physician Group	Christ Hospital System	103,356	42,951	60,405	140.6%
Physician Group	Chesapeake Regional Medical Center	114,411	47,660	66,751	140.1%
ASC	Azura Vascular Care	122,440	51,087	71,353	139.7%
Physician Group	Northeast Georgia Health System	209,221	87,342	121,879	139.5%
Physician Group	Memorial Hermann Health System	269,094	112,476	156,618	139.2%
Physician Group	Emanate Health	107,980	45,154	62,826	139.1%

Showing 50 of 502 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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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: 3 categories with significant ROID count changes

canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
2	183,132,196	79,896,632	103,235,564	129.2%
3	578,182,235	329,745,102	248,437,133	75.3%
4	219,551,413	189,350,804	30,200,609	15.9%

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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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 Score and Provider Type

Count: 11 categories with significant ROID count changes

provider_type	canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
ASC	2	662,584	172,691	489,893	283.7%
ASC	3	815,612	242,204	573,408	236.7%
ASC	4	45,098,346	21,047,569	24,050,777	114.3%
Hospital	5	31,504,321	37,675,253	-6,170,932	-16.4%
Hospital	3	75,022,233	87,719,688	-12,697,455	-14.5%
Imaging Center	3	171	268	-97	-36.2%
Imaging Center	2	58	77	-19	-24.7%
Physician Group	5	2,048,854	313,225	1,735,629	554.1%
Physician Group	4	8,019,650	2,791,132	5,228,518	187.3%
Physician Group	2	158,538,986	55,929,074	102,609,912	183.5%
Physician Group	3	497,663,834	237,209,780	260,454,054	109.8%

Click to see SQL

WITH
new AS (
    SELECT
        provider_type,
        canonical_rate_score,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY provider_type, canonical_rate_score
),
old AS (
    SELECT
        provider_type,
        canonical_rate_score,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY provider_type, canonical_rate_score
)
SELECT
    provider_type,
    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 (provider_type, canonical_rate_score)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY provider_type, ABS(roid_count_pct_change) DESC

By Canonical Rate Class

Count: 3 categories with significant ROID count changes

canonical_rate_class	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Raw	896,883,236	513,920,816	382,962,420	74.5%
Transform	39,857,636	33,941,151	5,916,485	17.4%
Impute	83,626,046	94,980,880	-11,354,834	-12.0%

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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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: 3 categories with significant ROID count changes

canonical_rate_source	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
payer	816,358,002	414,135,609	402,222,393	97.1%
imputation	83,626,046	94,980,880	-11,354,834	-12.0%
hospital	80,881,796	89,876,049	-8,994,253	-10.0%

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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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: 4 categories with significant ROID count changes

canonical_contract_methodology	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Fee Schedule	755,482,966	377,413,105	378,069,861	100.2%
Case Rate	136,747,686	119,669,706	17,077,980	14.3%
Per Diem	5,130,958	4,509,150	621,808	13.8%
Percent of Total Billed Charges	101,743,731	117,880,424	-16,136,693	-13.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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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: 6 categories with significant ROID count changes

canonical_crosswalk_method	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Crosswalked From NDC to HCPCS	1,954,260	950,588	1,003,672	105.6%
Crosswalked From RC-FAMILY to HCPCS	9,559,867	11,961,908	-2,402,041	-20.1%
Crosswalked From APC to HCPCS	9,186,438	7,987,270	1,199,168	15.0%
Crosswalked From RC-FAMILY to MS-DRG	941,127	837,809	103,318	12.3%
Crosswalked From RC-FAMILY to APR-DRG	1,272,350	1,140,096	132,254	11.6%
Crosswalked From CSTM-ALL to HCPCS	18,019,481	20,340,645	-2,321,164	-11.4%

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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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: 0 categories with significant ROID count changes

No significant ROID count swings detected.

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_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    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

---