v2_2_1 → v2_2_2

Generated: 2025-11-13 18:36:44.
Filter: canonical_rate_score > 1.

---

1. Summary Statistics

Overview

• Total ROIDs: 532,278,202 → 404,227,032 (-128,051,170, -24.1%)

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	Outpatient	177,986,732	173,223,088	171	170	5,620	5,528	7,064	7,026
Physician Group	Professional	153,542,945	273,683,261	170	170	16,501	16,464	2,838	2,839
Imaging Center	Professional	3,030,794	3,050,178	153	152	2,987	3,004	172	172
Hospital	Inpatient	42,440,939	41,357,176	171	170	5,571	5,477	1,716	1,716
ASC	Outpatient	25,065,265	38,716,300	141	145	5,583	5,584	3,371	3,371
Laboratory	Professional	2,160,357	2,248,199	151	149	721	731	917	917

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_2.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_1.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: 88 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT provider_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_2_2.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_1.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_2.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_1.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_2.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_1.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: 3604 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_2.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_1.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: 9 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_2.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_1.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: 19749 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
Professional	60938	4.75	1.01	595.74	126.46	368.9%	371.1%	1,252	1,240
Outpatient	9382	4.23	1.07	12,615.09	639.96	296.3%	1871.2%	43,864	1,186
Outpatient	9696	4.17	1.06	12,862.24	632.01	293.3%	1935.1%	44,859	1,185
Outpatient	10612	3.58	1.05	7,713.26	539.77	240.7%	1329.0%	35,782	2,149
Outpatient	8463	2.96	0.92	6,124.45	167.90	222.5%	3547.6%	39,179	797
Outpatient	10423	2.81	0.88	5,027.52	947.44	218.7%	430.6%	30,928	13,579
Outpatient	28928	7.88	2.5	10,741.11	1,843.88	215.3%	482.5%	680	2,967
Outpatient	8677	2.88	0.92	4,602.16	167.90	213.9%	2641.0%	14,182	797
Outpatient	4820	1.65	0.56	3,290.87	1,411.88	197.8%	133.1%	16,067	7,986
Outpatient	26495	3.24	1.25	3,629.65	1,884.71	159.4%	92.6%	6,696	9,208
Outpatient	9501	2.31	0.92	3,685.88	548.20	151.0%	572.4%	16,732	2,158
Outpatient	9061	2.62	1.09	8,585.51	26,795.60	140.2%	-68.0%	6,513	981
Inpatient	1600	3.65	1.54	58,251.65	13,736.01	137.0%	324.1%	1,895	199
Inpatient	2441	2.63	1.11	27,044.82	16,909.13	136.5%	59.9%	8,825	2,471
Outpatient	22742	1.84	0.79	1,453.05	863.15	133.8%	68.3%	1,457	1,894
Outpatient	9763	1.24	0.53	2,721.47	1,386.29	131.4%	96.3%	16,218	8,670
Outpatient	9223	2.37	1.04	2,729.20	468.50	127.8%	482.5%	22,561	2,257
Outpatient	28512	4.17	1.84	2,961.62	1,121.49	126.2%	164.1%	684	1,457
Outpatient	32711	2.82	1.25	2,841.67	2,881.77	125.8%	-1.4%	8,183	9,714
Inpatient	8583	2.23	0.99	37,605.57	22,669.52	124.6%	65.9%	6,053	795
Outpatient	8653	4.94	2.28	9,282.78	550.25	116.9%	1587.0%	22,274	650
Outpatient	29143	4.76	2.22	2,056.87	1,282.88	114.2%	60.3%	642	2,916
Outpatient	9101	2.56	1.2	8,431.69	745.83	113.5%	1030.5%	35,878	2,416
Outpatient	9622	4.19	1.98	9,479.56	1,192.61	111.9%	694.9%	16,262	3,315
Outpatient	8621	2.84	1.35	9,041.59	16,959.72	110.7%	-46.7%	49,576	1,127
Professional	8032902644883987048	4.09	1.96	1,528.96	712.73	108.5%	114.5%	18,526	23,637
Inpatient	10266	3.23	1.55	28,312.40	8,473.44	107.7%	234.1%	62	20
Outpatient	10418	3.43	1.65	8,386.88	606.51	107.6%	1282.8%	28,717	1,110
Outpatient	29354	2.53	1.22	2,111.34	2,091.73	107.5%	0.9%	730	6,589
Outpatient	3220	2.96	1.43	5,081.29	262.14	106.6%	1838.4%	3,465	797
Inpatient	9367	4.32	2.1	103,687.19	51,836.62	105.7%	100.0%	26,774	3,432
Professional	-5021669174724185205	1.62	0.79	122.57	363.33	105.3%	-66.3%	1,384	2,718
Inpatient	8621	2.36	1.17	44,555.16	14,952.99	102.0%	198.0%	13,649	2,051
Professional	-3101900049373431317	2.42	1.22	364.43	369.25	98.5%	-1.3%	5,522	7,605
Inpatient	9794	2.38	1.21	25,934.76	9,404.01	96.9%	175.8%	207	99
Outpatient	32769	2.59	1.36	2,259.76	1,440.89	90.9%	56.8%	1,278	1,940
Outpatient	10321	2.5	1.31	6,167.00	14,775.39	90.7%	-58.3%	24,452	2,639
Inpatient	9887	2.25	1.18	42,950.39	14,511.50	90.4%	196.0%	6,988	25
Professional	4998168270098493590	3.81	2.02	1,474.05	735.37	88.4%	100.4%	18,448	24,101
Outpatient	10491	4.51	2.4	11,148.18	1,193.27	88.4%	834.3%	34,928	812
Outpatient	4867	2.12	1.14	3,487.88	1,004.54	86.5%	247.2%	16,363	7,591
Outpatient	3758	3.73	26.51	9,416.43	10,621.65	-85.9%	-11.3%	27,885	27,586
Outpatient	23670	2.61	1.41	3,313.13	2,308.43	85.2%	43.5%	4,403	7,585
Professional	-5921861567391991961	3.26	1.77	1,724.44	703.57	84.7%	145.1%	22,900	21,177
Outpatient	8821	3.26	1.77	4,516.37	791.06	84.5%	470.9%	18,088	3,336
Inpatient	5746	2.53	1.37	25,562.57	16,480.20	84.4%	55.1%	151	425
Professional	-4686671424433735316	1.17	6.95	33.78	187.19	-83.2%	-82.0%	626	1,188
Professional	8641311238288994466	1	5.84	30.36	220.57	-82.9%	-86.2%	665	1,309
Outpatient	2184	4.48	24.78	10,153.81	10,326.69	-81.9%	-1.7%	46,029	38,795
Professional	-1900485449687794169	2.17	1.2	745.67	501.90	81.5%	48.6%	20,943	22,442

Showing 50 of 19749 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_2.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_1.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: 85508 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
5,750,867,047,671,580,791	23670	3.88	0.51	3,043.32	777.89	653.9%	291.2%	1,056	1,099
1,391,056,274,517,336,075	-7889417332880783629	7.2	1.27	3,744.95	448.14	468.2%	735.7%	2,976	3,553
-3,711,121,367,896,011,833	28366	3.8	0.71	2,739.01	860.76	437.2%	218.2%	2,681	3,359
-3,711,121,367,896,011,833	28093	3.77	0.71	2,670.71	853.00	434.1%	213.1%	2,688	3,359
1,391,056,274,517,336,075	8032902644883987048	6.35	1.25	3,573.28	423.56	408.9%	743.6%	3,009	3,553
1,017,413,876,722,652,424	6423	5.15	1.03	20,500.31	27,102.73	398.5%	-24.4%	5,952	1,630
1,391,056,274,517,336,075	4998168270098493590	6	1.25	3,291.45	423.56	381.0%	677.1%	3,066	3,553
-2,162,498,661,799,343,237	24591	4.16	0.88	2,825.72	709.56	373.1%	298.2%	2,771	3,254
-2,162,498,661,799,343,237	28093	3.37	0.72	3,389.58	940.68	365.3%	260.3%	2,565	3,360
-3,711,121,367,896,011,833	-8138245102499391979	4.57	0.99	133.81	486.04	360.1%	-72.5%	878	2,769
-3,711,121,367,896,011,833	8194905691709659689	4.57	0.99	133.81	486.04	360.1%	-72.5%	878	2,769
-2,162,498,661,799,343,237	-9095320300903158231	4.75	1.04	1,745.90	402.87	356.1%	333.4%	3,758	3,893
-2,162,498,661,799,343,237	31433	3.31	0.73	2,205.94	738.13	355.3%	198.9%	2,829	3,351
-3,776,001,016,975,145,508	-6952577638134067614	5.78	1.3	2,503.94	588.24	344.7%	325.7%	2,411	2,824
2,119,715,416,692,563,737	31433	3.22	0.73	1,990.92	737.52	343.2%	169.9%	2,797	3,350
3,269,223,941,459,339,425	832	4.39	1.02	48,750.39	21,242.76	331.2%	129.5%	5,537	1,189
5,075,764,097,723,629,059	832	4.39	1.02	48,750.39	21,242.76	331.2%	129.5%	5,537	1,189
-558,178,834,365,778,978	832	4.39	1.02	48,750.39	21,242.76	331.2%	129.5%	5,537	1,189
5,075,764,097,723,629,059	328	4.39	1.02	48,750.42	21,242.76	331.1%	129.5%	5,537	1,189
3,269,223,941,459,339,425	328	4.39	1.02	48,750.42	21,242.76	331.1%	129.5%	5,537	1,189
-558,178,834,365,778,978	328	4.39	1.02	48,750.42	21,242.76	331.1%	129.5%	5,537	1,189
-3,776,001,016,975,145,508	-7501019620232847899	4.6	1.12	2,030.56	487.50	312.0%	316.5%	2,534	2,804
8,361,580,493,441,765,265	10423	4.62	1.13	5,807.63	1,938.51	307.7%	199.6%	3,157	1,278
-6,543,868,296,982,650,519	-1810962326894746364	4.75	1.18	2,066.63	504.67	301.4%	309.5%	2,401	2,812
8,611,629,356,743,080,984	22813	2.87	0.72	2,180.00	754.54	298.5%	188.9%	2,945	3,347
-2,162,498,661,799,343,237	22813	2.87	0.72	2,180.00	754.54	298.5%	188.9%	2,945	3,347
1,391,056,274,517,336,075	-3092120643892762062	4.92	1.25	2,090.60	423.56	294.2%	393.6%	3,033	3,553
-2,162,498,661,799,343,237	8717763333513152914	3.78	0.96	390.64	349.64	294.1%	11.7%	1,827	3,939
-3,776,001,016,975,145,508	9478	2.03	0.52	5,388.55	100.37	293.1%	5268.9%	6,053	661
1,391,056,274,517,336,075	-903552902167683634	4.84	1.25	2,219.27	423.56	288.3%	424.0%	3,099	3,553
2,119,715,416,692,563,737	328	3.94	1.03	25,406.51	545.52	284.1%	4557.3%	7,480	1,145
-3,776,001,016,975,145,508	-1810962326894746364	4.49	1.18	2,100.07	504.67	279.2%	316.1%	2,564	2,812
3,133,716,806,509,821,992	-5376155650011534666	3.59	0.95	741.17	395.36	277.9%	87.5%	1,956	3,389
-2,162,498,661,799,343,237	5910532371753807288	3.4	0.92	805.84	390.42	269.6%	106.4%	3,254	3,397
3,133,716,806,509,821,992	-4941959983514493199	3.49	0.95	711.97	395.36	266.8%	80.1%	2,292	3,389
3,133,716,806,509,821,992	3484174468606655219	3.49	0.95	711.97	395.36	266.8%	80.1%	2,292	3,389
3,133,716,806,509,821,992	2359365618068298426	3.49	0.95	711.97	395.36	266.8%	80.1%	2,292	3,389
6,037,342,850,284,539,624	-7130550629439088387	6.43	1.76	267.07	710.38	264.8%	-62.4%	1,328	2,797
3,376,187,909,306,580,799	-7130550629439088387	6.43	1.76	267.07	710.38	264.8%	-62.4%	1,328	2,797
6,037,342,850,284,539,624	-4721414937564048981	6.43	1.76	267.07	710.38	264.8%	-62.4%	1,328	2,797
3,376,187,909,306,580,799	-4721414937564048981	6.43	1.76	267.07	710.38	264.8%	-62.4%	1,328	2,797
3,133,716,806,509,821,992	-5157829882714626371	3.44	0.95	643.74	395.26	262.6%	62.9%	1,678	3,389
-6,486,550,968,171,266,793	-8644475514158813762	3.66	1.02	1,923.16	437.92	260.4%	339.2%	2,585	2,846
6,974,535,101,123,239,131	-8779466681210528249	4.52	1.3	1,494.17	564.48	248.1%	164.7%	1,933	2,816
6,037,342,850,284,539,624	-6644368353908611628	6.89	2	301.12	801.25	244.5%	-62.4%	1,286	2,796
3,376,187,909,306,580,799	-6644368353908611628	6.89	2	301.12	801.25	244.5%	-62.4%	1,286	2,796
2,119,715,416,692,563,737	3193278596207345254	3.34	0.99	866.49	448.90	237.0%	93.0%	3,559	3,415
2,119,715,416,692,563,737	3977896946543268693	3.34	0.99	866.49	448.90	237.0%	93.0%	3,559	3,415
-2,162,498,661,799,343,237	-3567900254009656668	3.42	1.01	1,248.65	452.53	236.8%	175.9%	3,772	3,415
8,361,580,493,441,765,265	9328	3.5	1.05	10,605.60	601.03	233.8%	1664.6%	6,726	1,202

Showing 50 of 85508 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_2.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_1.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: 89320 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,750,867,047,671,580,791	23670	Outpatient	3.88	0.51	3,043.32	777.89	653.9%	291.2%	1,056	1,099
7,750,147,370,859,442,459	891	Outpatient	4.37	0.65	10,528.88	1,344.30	572.1%	683.2%	6,689	4,035
-6,588,387,606,928,155,169	891	Outpatient	4.35	0.65	10,520.37	1,344.30	570.1%	682.6%	6,701	4,035
1,391,056,274,517,336,075	-7889417332880783629	Professional	7.2	1.27	3,744.95	448.14	468.2%	735.7%	2,976	3,553
7,750,147,370,859,442,459	899	Outpatient	3.98	0.7	9,931.28	1,344.42	468.1%	638.7%	6,742	4,035
-6,588,387,606,928,155,169	899	Outpatient	3.95	0.7	9,943.27	1,344.42	463.6%	639.6%	6,330	4,035
-3,711,121,367,896,011,833	28366	Outpatient	3.8	0.71	2,739.01	860.76	437.2%	218.2%	2,681	3,359
-3,711,121,367,896,011,833	28093	Outpatient	3.77	0.71	2,670.71	853.00	434.1%	213.1%	2,688	3,359
1,391,056,274,517,336,075	8032902644883987048	Professional	6.35	1.25	3,573.28	423.56	408.9%	743.6%	3,009	3,553
1,391,056,274,517,336,075	4998168270098493590	Professional	6	1.25	3,291.45	423.56	381.0%	677.1%	3,066	3,553
-7,225,588,104,346,557,715	891	Outpatient	3.09	0.65	5,876.55	1,344.30	374.8%	337.1%	6,265	4,035
-2,162,498,661,799,343,237	24591	Outpatient	4.16	0.88	2,825.72	709.56	373.1%	298.2%	2,771	3,254
-7,304,296,722,942,665,713	992	Outpatient	3.05	0.66	6,151.20	1,351.58	365.7%	355.1%	6,244	4,320
-2,162,498,661,799,343,237	28093	Outpatient	3.37	0.72	3,389.58	940.68	365.3%	260.3%	2,565	3,360
7,750,147,370,859,442,459	977	Outpatient	3.02	0.66	5,282.37	1,351.73	360.2%	290.8%	6,248	4,319
-6,588,387,606,928,155,169	977	Outpatient	3.02	0.66	5,282.37	1,351.73	360.2%	290.8%	6,248	4,319
-3,711,121,367,896,011,833	8194905691709659689	Professional	4.57	0.99	133.81	486.04	360.1%	-72.5%	878	2,769
-3,711,121,367,896,011,833	-8138245102499391979	Professional	4.57	0.99	133.81	486.04	360.1%	-72.5%	878	2,769
-7,225,588,104,346,557,715	992	Outpatient	3.02	0.66	6,149.13	1,351.57	359.8%	355.0%	6,233	4,320
7,750,147,370,859,442,459	992	Outpatient	2.99	0.66	5,302.51	1,351.57	356.7%	292.3%	6,247	4,320
-6,588,387,606,928,155,169	992	Outpatient	2.99	0.66	5,302.50	1,351.57	356.7%	292.3%	6,247	4,320
-2,162,498,661,799,343,237	-9095320300903158231	Professional	4.75	1.04	1,745.90	402.87	356.1%	333.4%	3,758	3,893
-7,225,588,104,346,557,715	977	Outpatient	2.99	0.66	6,303.91	1,351.73	355.5%	366.4%	6,229	4,319
-7,225,588,104,346,557,715	954	Outpatient	3.17	0.7	5,530.63	1,426.51	355.4%	287.7%	6,241	4,373
-2,162,498,661,799,343,237	31433	Outpatient	3.31	0.73	2,205.94	738.13	355.3%	198.9%	2,829	3,351
-7,304,296,722,942,665,713	977	Outpatient	2.98	0.66	6,303.68	1,351.75	354.8%	366.3%	6,229	4,319
-6,293,294,949,651,930,973	1910	Outpatient	4.82	1.08	19,515.86	21,139.06	347.7%	-7.7%	4,475	1,200
-3,776,001,016,975,145,508	-6952577638134067614	Professional	5.78	1.3	2,503.94	588.24	344.7%	325.7%	2,411	2,824
2,119,715,416,692,563,737	31433	Outpatient	3.22	0.73	1,990.92	737.52	343.2%	169.9%	2,797	3,350
-3,776,001,016,975,145,508	9099	Outpatient	4.75	1.08	18,762.06	592.74	341.1%	3065.3%	5,752	1,157
-3,776,001,016,975,145,508	8400	Outpatient	2.82	0.65	5,907.20	269.58	337.2%	2091.2%	3,158	765
-6,588,387,606,928,155,169	954	Outpatient	3.01	0.69	5,306.87	1,420.17	336.4%	273.7%	6,244	4,373
7,750,147,370,859,442,459	954	Outpatient	3.01	0.69	5,306.86	1,420.17	336.4%	273.7%	6,244	4,373
4,043,190,250,846,473,271	9231	Inpatient	4.44	1.04	50,419.56	34,068.58	327.2%	48.0%	1,664	287
8,361,580,493,441,765,265	10423	Outpatient	4.71	1.13	5,240.79	670.06	317.6%	682.1%	3,070	1,206
-3,776,001,016,975,145,508	-7501019620232847899	Professional	4.6	1.12	2,030.56	487.50	312.0%	316.5%	2,534	2,804
-6,543,868,296,982,650,519	-1810962326894746364	Professional	4.75	1.18	2,066.63	504.67	301.4%	309.5%	2,401	2,812
-2,162,498,661,799,343,237	22813	Outpatient	2.87	0.72	2,180.00	754.54	298.5%	188.9%	2,945	3,347
8,611,629,356,743,080,984	22813	Outpatient	2.87	0.72	2,180.00	754.54	298.5%	188.9%	2,945	3,347
2,119,715,416,692,563,737	328	Outpatient	4.07	1.03	7,814.98	545.52	297.2%	1332.6%	5,776	1,145
1,391,056,274,517,336,075	-3092120643892762062	Professional	4.92	1.25	2,090.60	423.56	294.2%	393.6%	3,033	3,553
-2,162,498,661,799,343,237	8717763333513152914	Professional	3.78	0.96	390.64	349.64	294.1%	11.7%	1,827	3,939
-3,776,001,016,975,145,508	9478	Outpatient	2.03	0.52	5,388.55	100.37	293.1%	5268.9%	6,053	661
1,391,056,274,517,336,075	-903552902167683634	Professional	4.84	1.25	2,219.27	423.56	288.3%	424.0%	3,099	3,553
-7,225,588,104,346,557,715	989	Outpatient	8.69	2.26	33,427.37	346.00	284.6%	9561.1%	6,013	2,438
-7,225,588,104,346,557,715	329	Outpatient	9.66	2.53	31,783.54	346.00	282.2%	9086.0%	6,171	2,438
-3,776,001,016,975,145,508	-1810962326894746364	Professional	4.49	1.18	2,100.07	504.67	279.2%	316.1%	2,564	2,812
3,133,716,806,509,821,992	-5376155650011534666	Professional	3.59	0.95	741.17	395.36	277.9%	87.5%	1,956	3,389
5,075,764,097,723,629,059	328	Outpatient	3.84	1.02	13,989.19	21,242.76	277.1%	-34.1%	3,936	1,189
-558,178,834,365,778,978	328	Outpatient	3.84	1.02	13,989.19	21,242.76	277.1%	-34.1%	3,936	1,189

Showing 50 of 89320 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_2.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_1.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: 140 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,954,532,594,486,231,313	3.24	2.22	14,765.35	7,140.17	46.3%	106.8%	760,703	1,730,866
-7,227,420,687,598,643,435	2.14	1.48	6,835.12	5,967.82	44.2%	14.5%	837,448	658,971
-2,685,661,158,361,719,346	2.32	1.68	8,790.18	3,853.40	38.0%	128.1%	625,370	1,606,631
-8,973,307,920,338,611,544	3.04	2.34	13,604.14	7,887.23	30.3%	72.5%	745,642	1,307,405
6,974,535,101,123,239,131	2.65	2.11	7,837.62	4,862.80	25.8%	61.2%	583,345	939,352
-2,596,917,860,902,475,653	2.29	1.84	7,487.38	6,408.30	24.3%	16.8%	1,024,751	891,072
3,902,096,239,283,128,733	2.29	3.01	1,902.59	1,392.21	-23.8%	36.7%	435,194	731,374
-7,428,563,680,805,185,856	1.51	1.92	5,579.20	3,752.84	-21.6%	48.7%	32,695	67,789
-4,027,592,970,285,917,801	2.26	1.87	7,961.26	5,034.48	21.1%	58.1%	852,962	1,506,519
-2,865,465,467,459,569,344	3.19	4.03	6,333.72	4,598.46	-20.8%	37.7%	458,754	675,848
3,376,187,909,306,580,799	2.75	2.29	2,674.20	2,343.09	19.9%	14.1%	1,251,804	1,710,469
8,635,146,956,337,329,045	1.4	1.74	1,627.49	5,015.55	-19.7%	-67.6%	952,811	331,671
-7,695,283,351,826,393,948	3	2.51	9,621.70	6,638.75	19.4%	44.9%	758,726	1,147,608
-4,564,247,599,614,740,658	2.15	2.65	6,868.09	12,359.97	-19.0%	-44.4%	520,020	283,352
5,697,578,418,236,370,479	1.91	2.33	4,724.57	3,630.24	-18.2%	30.1%	516,908	677,443
1,164,822,038,571,945,223	2.36	2.87	5,511.14	3,953.72	-17.8%	39.4%	453,555	713,395
1,134,341,852,115,164,929	1.9	1.63	7,620.57	5,179.06	16.8%	47.1%	801,609	1,242,998
-8,822,629,802,732,873,104	2.22	1.9	8,902.77	5,575.45	16.8%	59.7%	852,244	1,549,231
4,683,160,466,464,628,412	2.87	2.46	10,820.21	8,126.25	16.7%	33.2%	1,154,376	1,528,119
6,037,342,850,284,539,624	2.82	2.42	3,673.29	3,003.92	16.7%	22.3%	1,369,266	1,824,393
4,911,047,023,988,898,996	2.6	2.23	11,671.18	7,971.49	16.5%	46.4%	1,798,404	2,482,879
-1,777,933,741,358,895,430	2.8	3.33	7,777.83	6,204.56	-16.0%	25.4%	1,181,081	1,585,600
2,039,367,776,086,447,454	2.38	2.06	5,665.71	4,370.51	15.9%	29.6%	1,186,952	1,595,988
112,829,016,471,021,748	2.59	2.24	8,662.39	6,019.40	15.6%	43.9%	317,081	463,680
-6,543,868,296,982,650,519	2.54	2.2	10,987.11	7,422.10	15.5%	48.0%	2,619,326	4,087,131
4,711,821,234,192,922,644	2.43	2.87	1,902.07	1,863.95	-15.5%	2.0%	838,418	1,201,425
-6,588,387,606,928,155,169	3.05	3.59	13,056.75	7,960.43	-15.0%	64.0%	1,273,347	1,512,044
6,355,182,945,120,798,897	2.38	2.08	8,331.34	5,683.80	14.7%	46.6%	286,309	436,807
7,750,147,370,859,442,459	3.05	3.58	13,024.86	7,727.45	-14.7%	68.6%	1,312,136	1,513,748
-4,944,393,768,879,386,509	3.83	4.48	16,986.18	20,296.53	-14.4%	-16.3%	521,055	1,027,781
-1,068,861,235,619,238,745	2.48	2.16	5,516.92	4,446.33	14.3%	24.1%	1,641,775	2,126,057
-7,572,382,112,705,938,029	2.02	2.36	5,772.49	4,347.17	-14.3%	32.8%	424,312	607,803
-4,541,460,228,936,150,437	2.32	2.03	10,643.53	6,402.05	14.1%	66.3%	1,782,226	3,089,875
-7,304,296,722,942,665,713	2.91	3.39	13,479.36	8,666.88	-14.1%	55.5%	1,386,853	1,677,784
-7,995,682,717,897,123,039	2.92	3.39	12,147.39	8,652.86	-13.9%	40.4%	5,106,336	7,368,975
-7,225,588,104,346,557,715	3.09	3.58	13,335.45	7,740.51	-13.7%	72.3%	1,285,084	1,518,890
7,058,457,158,512,878,544	3.07	2.71	13,041.70	10,079.94	13.3%	29.4%	1,495,143	1,902,552
4,485,705,121,095,584,470	2.39	2.74	3,619.18	2,700.08	-13.0%	34.0%	1,122,302	1,908,515
-6,486,550,968,171,266,793	2.69	2.39	10,881.53	7,627.89	12.9%	42.7%	1,948,054	2,864,206
3,478,392,755,490,109,147	2.13	1.89	7,003.63	4,597.10	12.8%	52.3%	310,974	451,990
3,267,736,702,885,179,114	2.45	2.17	8,786.06	6,513.57	12.8%	34.9%	1,943,405	2,605,934
4,043,190,250,846,473,271	1.95	1.73	6,709.98	4,624.10	12.8%	45.1%	1,426,606	1,970,552
-2,510,676,791,703,302,876	2.45	2.18	9,592.58	7,129.60	12.2%	34.5%	1,705,870	2,233,266
-3,949,448,820,262,323,978	2.43	2.17	8,097.37	6,274.96	12.0%	29.0%	1,053,941	1,403,956
6,091,386,345,134,788,986	2.84	2.54	9,703.83	7,267.00	12.0%	33.5%	239,368	347,124
6,352,083,177,318,679,706	2.28	2.58	2,254.81	1,724.21	-11.7%	30.8%	179,099	498,833
1,178,549,910,471,784,015	2.59	2.32	9,783.48	7,548.06	11.7%	29.6%	556,617	734,835
4,299,532,402,194,607,519	3.43	3.07	14,851.03	11,392.01	11.6%	30.4%	1,521,198	1,893,532
-6,293,294,949,651,930,973	2.5	2.25	9,203.01	5,690.93	11.2%	61.7%	1,575,920	2,664,666
8,816,084,628,491,357,618	2.51	2.26	10,072.99	7,364.30	10.9%	36.8%	1,535,329	2,138,462

Showing 50 of 140 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_2.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_1.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: 24 categories with significant ROID count changes

service_line	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Transplant	73,521	339,333	-265,812	-78.3%
Infectious Disease	17,863,031	10,320,407	7,542,624	73.1%
Anesthesia	11,338,951	28,763,676	-17,424,725	-60.6%
Lab/Path	29,569,502	20,273,749	9,295,753	45.9%
Nephrology	1,065,180	1,800,671	-735,491	-40.8%
Dermatology	23,080,814	37,424,198	-14,343,384	-38.3%
MSK	54,736,929	87,754,367	-33,017,438	-37.6%
Cardiovascular	20,548,429	32,500,901	-11,952,472	-36.8%
Ophthalmology	13,889,230	21,623,583	-7,734,353	-35.8%
Obstetrics	6,374,108	9,506,854	-3,132,746	-33.0%
Neurology	16,597,664	24,650,914	-8,053,250	-32.7%
Gastrointestinal	21,132,700	30,630,308	-9,497,608	-31.0%
Behavioral Health	4,328,619	6,248,415	-1,919,796	-30.7%
Rehab	5,650,656	8,103,178	-2,452,522	-30.3%
Reproductive	8,647,020	12,186,145	-3,539,125	-29.0%
ENT	13,313,039	18,431,100	-5,118,061	-27.8%
Pulmonology	6,636,196	8,884,434	-2,248,238	-25.3%
Urology	9,729,672	12,894,175	-3,164,503	-24.5%
Hematology	1,717,054	2,246,767	-529,713	-23.6%
Endocrinology	1,683,663	2,109,999	-426,336	-20.2%
Trauma	1,681,704	2,031,110	-349,406	-17.2%
Radiology	43,491,626	52,253,125	-8,761,499	-16.8%
Diabetes	174,769	209,364	-34,595	-16.5%
Consultative and Preventative Care	8,580,349	10,008,693	-1,428,344	-14.3%

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_2.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_1.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: 2 categories with significant ROID count changes

provider_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Physician Group	153,542,945	273,683,261	-120,140,316	-43.9%
ASC	25,065,265	38,716,300	-13,651,035	-35.3%

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_2.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_1.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: 49 categories with significant ROID count changes

state	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
AK	609,886	1,092,571	-482,685	-44.2%
AZ	7,484,776	12,001,839	-4,517,063	-37.6%
HI	1,242,743	1,878,328	-635,585	-33.8%
AL	6,024,025	8,952,611	-2,928,586	-32.7%
DC	2,446,834	3,607,164	-1,160,330	-32.2%
WY	832,045	1,216,447	-384,402	-31.6%
GA	11,667,390	16,927,062	-5,259,672	-31.1%
NJ	10,842,719	15,559,783	-4,717,064	-30.3%
NH	2,301,389	3,300,489	-999,100	-30.3%
RI	973,443	1,382,843	-409,400	-29.6%
NV	2,846,212	4,022,300	-1,176,088	-29.2%
KY	4,918,879	6,886,991	-1,968,112	-28.6%
CT	2,912,281	4,065,687	-1,153,406	-28.4%
MA	10,894,409	15,151,534	-4,257,125	-28.1%
MI	11,813,666	16,311,888	-4,498,222	-27.6%
VA	8,452,176	11,648,087	-3,195,911	-27.4%
CA	43,991,189	60,221,785	-16,230,596	-27.0%
UT	2,028,198	2,752,760	-724,562	-26.3%
NM	1,747,882	2,338,540	-590,658	-25.3%
MS	4,661,930	6,223,191	-1,561,261	-25.1%
DE	825,496	1,100,284	-274,788	-25.0%
TN	8,065,778	10,747,927	-2,682,149	-25.0%
NY	37,274,618	49,486,196	-12,211,578	-24.7%
OR	5,103,204	6,773,415	-1,670,211	-24.7%
VT	508,507	674,308	-165,801	-24.6%
KS	5,291,078	7,012,535	-1,721,457	-24.5%
CO	5,847,665	7,742,134	-1,894,469	-24.5%
NC	9,526,717	12,586,249	-3,059,532	-24.3%
WA	8,534,310	11,234,688	-2,700,378	-24.0%
FL	21,350,365	27,985,903	-6,635,538	-23.7%
LA	8,716,598	11,384,733	-2,668,135	-23.4%
WV	1,741,896	2,268,661	-526,765	-23.2%
ME	2,338,562	3,024,429	-685,867	-22.7%
IN	8,351,748	10,692,236	-2,340,488	-21.9%
IA	5,491,927	7,022,287	-1,530,360	-21.8%
MD	4,291,404	5,482,496	-1,191,092	-21.7%
MN	7,702,954	9,781,660	-2,078,706	-21.3%
NE	3,317,381	4,198,367	-880,986	-21.0%
IL	12,967,506	16,400,193	-3,432,687	-20.9%
PA	19,322,050	24,150,522	-4,828,472	-20.0%
WI	7,866,096	9,829,528	-1,963,432	-20.0%
MT	2,009,110	2,499,635	-490,525	-19.6%
TX	37,972,028	47,006,270	-9,034,242	-19.2%
SC	4,078,554	4,998,252	-919,698	-18.4%
MO	7,418,917	9,089,225	-1,670,308	-18.4%
AR	4,450,948	5,414,333	-963,385	-17.8%
SD	1,787,289	2,153,125	-365,836	-17.0%
ID	2,939,145	3,514,150	-575,005	-16.4%
ND	1,347,092	1,564,956	-217,864	-13.9%

Click to see SQL

WITH
new AS (
    SELECT
        state,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_2_2.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_1.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: 137 categories with significant ROID count changes

payer_network_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Blue Cross Blue Shield of Maryland & DC (CareFirst) Preferred Provider Network (PPO)	952,811	331,671	621,140	187.3%
Regence Blue Cross Blue Shield ID PPO	520,020	283,352	236,668	83.5%
Mass General Brigham Health Plan Commercial PPO	179,099	498,833	-319,734	-64.1%
Blue Cross Blue Shield of Arizona HMO	625,370	1,606,631	-981,261	-61.1%
Anthem VA HMO	760,703	1,730,866	-970,163	-56.1%
MotivHealth Insurance Company MotivNet	32,695	67,789	-35,094	-51.8%
Premera Blue Cross Alaska Heritage Network	207,106	426,794	-219,688	-51.5%
Kaiser Permanente Kaiser Health Plan Mid Atlantic	521,055	1,027,781	-506,726	-49.3%
Blue Cross Blue Shield of New Jersey (Horizon) OMNIA	1,053,365	2,077,155	-1,023,790	-49.3%
Mass General Brigham Health Plan HMO	777,636	1,517,969	-740,333	-48.8%
Blue Cross Blue Shield of Arizona PPO	851,903	1,607,091	-755,188	-47.0%
Cigna NJ HMO	852,244	1,549,231	-696,987	-45.0%
Anthem CT Century Preferred PPO	329,298	594,464	-265,166	-44.6%
Blue Cross Blue Shield of Kansas City Preferredcare Blue	202,089	362,144	-160,055	-44.2%
Cigna AZ HMO	852,962	1,506,519	-653,557	-43.4%
Anthem IN HMO	745,642	1,307,405	-561,763	-43.0%
Blue Cross Blue Shield of New York (Excellus) Excellus Bluecross Blueshield	1,061,785	1,852,524	-790,739	-42.7%
Anthem NY PPO	1,782,226	3,089,875	-1,307,649	-42.3%
Health Alliance Plan (HAP) HMO	997,542	1,727,761	-730,219	-42.3%
MetroPlus Health Essential Plan	1,122,302	1,908,515	-786,213	-41.2%
Anthem GA OPEN ACCESS	1,575,920	2,664,666	-1,088,746	-40.9%
Univera Healthcare PPO	435,194	731,374	-296,180	-40.5%
Cigna NC HMO	1,035,569	1,736,942	-701,373	-40.4%
Health Alliance Plan (HAP) PPO	1,129,056	1,866,362	-737,306	-39.5%
Blue Cross Blue Shield of Wyoming PPO	239,299	394,097	-154,798	-39.3%
Cigna AL HMO	583,345	939,352	-356,007	-37.9%
Blue Cross Blue Shield of New Jersey (Horizon) PPO	1,365,729	2,160,326	-794,597	-36.8%
Network Health Plan PPO	397,259	627,693	-230,434	-36.7%
Cigna New England HMO	1,342,354	2,114,823	-772,469	-36.5%
Blue Cross of Idaho PPO	453,555	713,395	-259,840	-36.4%
Cigna CA HMO	2,619,326	4,087,131	-1,467,805	-35.9%
Blue Cross Blue Shield of Hawaii (HMSA) HMO	196,284	305,479	-109,195	-35.7%
Sutter Health Plus PPO	451,143	699,624	-248,481	-35.5%
Anthem KY PPO	801,609	1,242,998	-441,389	-35.5%
Blue Cross Blue Shield of Alabama Preferred PPO	1,203,453	1,859,537	-656,084	-35.3%
Premera Blue Cross HMO	638,479	974,445	-335,966	-34.5%
Anthem NH HMO	286,309	436,807	-150,498	-34.5%
Cigna NH HMO	230,066	349,555	-119,489	-34.2%
Blue Cross Blue Shield of Hawaii (HMSA) PPO	222,403	337,113	-114,710	-34.0%
Anthem CO Blue Preferred PPO	758,726	1,147,608	-388,882	-33.9%
Wellmark Blue Cross Blue Shield IA PPO	770,350	1,150,764	-380,414	-33.1%
Harvard Pilgrim Health Care HMO	1,002,368	1,497,259	-494,891	-33.1%
Kaiser Permanente Kaiser Health Plan (HI)	210,107	312,562	-102,455	-32.8%
Blue Cross Blue Shield of Rhode Island PPO	184,166	273,733	-89,567	-32.7%
Regence Blue Cross Blue Shield UT PPO	420,092	623,789	-203,697	-32.7%
Harvard Pilgrim Health Care Choicenet PPO	1,029,075	1,525,823	-496,748	-32.6%
Kaiser Permanente Kaiser Health Plan (GA)	913,366	1,352,081	-438,715	-32.4%
Moda Health Connexus	458,754	675,848	-217,094	-32.1%
Cigna NY HMO	1,948,054	2,864,206	-916,152	-32.0%
Anthem NH OPEN ACCESS	317,081	463,680	-146,599	-31.6%

Showing 50 of 137 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_2.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_1.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
398	Blue Cross Blue Shield of Tennessee	Preferred	hospital	253,329	0
398	Blue Cross Blue Shield of Tennessee	Preferred	payer	1,668,127	0
398	Blue Cross Blue Shield of Tennessee	Preferred	imputation	36,443	0
958	MetroPlus Health	Gold	imputation	1,177	98
791	MotivHealth Insurance Company	MotivNet	imputation	103	101
397	Blue Cross Blue Shield of Rhode Island	PPO	imputation	140	139
317	Tufts Health Plan	PPO MA	payer	612	258
628	Premera Blue Cross	Alaska Heritage Network	imputation	759	556
42	Anthem	NH HMO	imputation	1,551	570
53	Blue Cross Blue Shield of Oklahoma	Blue Preferred PPO	imputation	3,039	668

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_2.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_1.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: 277 categories with significant ROID count changes

provider_type	health_system_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Hospital	UMC Health System	101,041	56,033	45,008	80.3%
Hospital	University of Florida Health in Jacksonville	107,505	61,278	46,227	75.4%
Hospital	UVA Health System	104,551	60,506	44,045	72.8%
Hospital	Willis-Knighton Health System	213,266	124,897	88,369	70.8%
ASC	Sutter Health	121,700	411,392	-289,692	-70.4%
Hospital	Lake Health	142,432	85,081	57,351	67.4%
Hospital	Halifax Health	117,190	70,137	47,053	67.1%
Hospital	University of Missouri Health Care	171,010	104,647	66,363	63.4%
Physician Group	University of California Davis Health	47,025	123,704	-76,679	-62.0%
Hospital	Mohawk Valley Health System	110,383	68,481	41,902	61.2%
ASC	Banner Health	123,908	300,508	-176,600	-58.8%
Physician Group	Circle Health	82,363	186,881	-104,518	-55.9%
Physician Group	Munson Healthcare	189,559	122,339	67,220	54.9%
ASC	Monterey Peninsula Surgery Centers	51,409	111,774	-60,365	-54.0%
Physician Group	TMC Health	74,007	158,291	-84,284	-53.2%
Physician Group	California Pacific Medical Center	49,556	105,848	-56,292	-53.2%
ASC	Medbridge	47,396	101,030	-53,634	-53.1%
ASC	Baylor Scott & White Health	119,333	254,072	-134,739	-53.0%
Physician Group	Piedmont Healthcare	160,806	341,470	-180,664	-52.9%
Physician Group	Corewell Health West	49,622	105,249	-55,627	-52.9%
Physician Group	Cape Cod Healthcare	63,884	134,313	-70,429	-52.4%
Physician Group	ThedaCare	53,939	112,249	-58,310	-51.9%
Physician Group	North Mississippi Health Services	67,392	140,196	-72,804	-51.9%
Physician Group	Stony Brook Medicine	183,103	375,605	-192,502	-51.3%
Physician Group	HCA Capital Division - HCA Virginia Health System	58,209	117,090	-58,881	-50.3%
Physician Group	Providence Health & Services - Southern California	86,822	174,535	-87,713	-50.3%
Physician Group	Sutter Health	95,990	192,279	-96,289	-50.1%
ASC	Proliance Surgeons	58,531	116,741	-58,210	-49.9%
Physician Group	Scripps Health	132,647	259,640	-126,993	-48.9%
Physician Group	Atrium Health Floyd	65,884	126,416	-60,532	-47.9%
Physician Group	SUNY Upstate Medical University	89,524	170,751	-81,227	-47.6%
Physician Group	Huntsville Hospital Health System	77,842	148,263	-70,421	-47.5%
Physician Group	Cooper University Health	148,630	279,450	-130,820	-46.8%
Physician Group	Sentara Health	215,924	404,649	-188,725	-46.6%
Physician Group	Hoag Health System	112,150	207,393	-95,243	-45.9%
Physician Group	HCA South Atlantic Division	87,400	160,140	-72,740	-45.4%
Physician Group	BayCare Health System	104,235	190,891	-86,656	-45.4%
Physician Group	Mass General Brigham	220,253	400,249	-179,996	-45.0%
Physician Group	Catholic Health	489,035	883,982	-394,947	-44.7%
Physician Group	Kaleida Health	132,018	236,879	-104,861	-44.3%
Physician Group	Bryan Health	77,874	138,686	-60,812	-43.8%
ASC	Surgery Partners	679,266	1,204,870	-525,604	-43.6%
Physician Group	Atlantic Health System	131,850	233,005	-101,155	-43.4%
Physician Group	Baptist Health South Florida	68,063	120,041	-51,978	-43.3%
Physician Group	Asante	68,100	119,704	-51,604	-43.1%
ASC	Cedars-Sinai Health System	82,494	144,476	-61,982	-42.9%
Hospital	Premier Health	172,111	120,451	51,660	42.9%
Physician Group	Prime Healthcare Services	120,350	210,596	-90,246	-42.9%
ASC	MemorialCare Health System	89,862	157,005	-67,143	-42.8%
Physician Group	Methodist Health System	77,989	135,684	-57,695	-42.5%

Showing 50 of 277 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_2.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_1.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: 3 categories with significant ROID count changes

canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
2	22,552,058	13,164,651	9,387,407	71.3%
3	83,954,307	49,260,810	34,693,497	70.4%
4	270,594,940	443,935,538	-173,340,598	-39.0%

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_2.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_1.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 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	329,763	1,260,086	-930,323	-73.8%
ASC	3	233,180	793,763	-560,583	-70.6%
ASC	4	24,502,322	36,662,451	-12,160,129	-33.2%
Hospital	2	13,764,079	11,904,350	1,859,729	15.6%
Laboratory	2	330,753	27	330,726	1224911.1%
Laboratory	3	734,890	74	734,816	992994.6%
Laboratory	4	141,889	2,248,098	-2,106,209	-93.7%
Physician Group	3	31,614,381	62	31,614,319	50990837.1%
Physician Group	2	8,127,365	86	8,127,279	9450324.4%
Physician Group	4	113,254,853	273,009,930	-159,755,077	-58.5%
Physician Group	5	546,346	673,183	-126,837	-18.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_2_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    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_2_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    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
Transform	30,982,905	49,592,229	-18,609,324	-37.5%
Raw	293,970,068	420,120,227	-126,150,159	-30.0%
Impute	79,274,059	62,565,746	16,708,313	26.7%

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_2.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_1.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: 2 categories with significant ROID count changes

canonical_rate_source	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
payer	253,302,366	403,585,780	-150,283,414	-37.2%
imputation	79,274,059	62,565,746	16,708,313	26.7%

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_2.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_1.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: 1 categories with significant ROID count changes

canonical_contract_methodology	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Fee Schedule	202,973,140	329,583,120	-126,609,980	-38.4%

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_2.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_1.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: 1 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,056,271	1,631,770	-575,499	-35.3%

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_2.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_1.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: 2 categories with significant ROID count changes

canonical_gross_charge_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
mrf_gross_charge_cbsa_median	32,777,476	11,682,570	21,094,906	180.6%
mrf_gross_charge_state_median	83,181,458	101,666,444	-18,484,986	-18.2%

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_2.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_1.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

---