v2_3_2 → v2_4_0

Generated: 2026-02-03 21:32:29.
Filter: canonical_rate_score > 1.

---

1. Summary Statistics

Overview

• Total ROIDs: 423,207,546 → 423,563,165 (+355,619, +0.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
ASC	Outpatient	14,469,409	14,469,411	137	137	5,533	5,533	3,154	3,154
Hospital	Outpatient	182,556,632	182,614,542	176	176	5,708	5,702	7,512	7,512
Imaging Center	Professional	2,964,629	2,967,765	159	159	2,898	2,898	171	171
Laboratory	Professional	11,292,016	11,292,016	167	167	3,710	3,710	916	916
Physician Group	Professional	168,018,318	167,535,830	174	174	21,113	21,157	3,790	3,790
Hospital	Inpatient	44,262,161	44,327,982	173	173	5,630	5,622	1,711	1,717

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_0.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_3_2.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: 129 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT provider_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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_3_2.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_0.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_3_2.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: 0 dropped

No dropoffs detected.

Click to see SQL

WITH
new AS (
    SELECT DISTINCT network_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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_3_2.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: 945 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_0.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_3_2.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: 6 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_0.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_3_2.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: 974 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	2.61	43,276.38	43,103.32	inf%	0.4%	10,662	10,718
Inpatient	9194	2.84	0.97	65,201.33	12,919.54	193.8%	404.7%	5,348	1,836
Outpatient	8819	2.87	1.12	3,965.80	373.06	156.3%	963.0%	23,836	3,164
Professional	-836335150426181903	2.47	0.98	874.35	246.34	151.2%	254.9%	17,668	3,491
Professional	8188513161227882061	2.69	1.23	1,052.92	798.75	119.3%	31.8%	22,945	8,819
Professional	-6851583214794458581	2.4	1.11	970.72	323.03	116.1%	200.5%	21,907	6,881
Professional	1232554689514378546	2.4	1.11	970.72	323.03	116.1%	200.5%	21,907	6,881
Professional	3888249385205433521	2.49	1.18	1,280.44	388.86	111.5%	229.3%	19,305	6,191
Professional	-2718255887486666313	4.8	2.32	159.20	489.49	106.9%	-67.5%	1,168	19,987
Inpatient	9615	2.74	1.34	51,815.51	14,834.39	104.3%	249.3%	5,078	40
Professional	3146678585810448215	2.68	1.35	1,110.99	178.77	98.2%	521.5%	26,539	1,036
Professional	1322444020174116057	1.91	0.98	744.62	173.72	95.7%	328.6%	22,327	4,102
Professional	-4981199337068856322	1.97	1.02	984.66	69.95	93.4%	1307.7%	22,823	2,019
Professional	-5437767336796664563	1.52	0.8	322.90	124.51	90.6%	159.3%	13,813	7,200
Professional	-6133714838765654096	1.71	0.9	642.04	157.18	90.0%	308.5%	22,672	7,576
Professional	-3810762884485338268	1.83	1.01	616.53	164.41	81.9%	275.0%	10,752	5,807
Professional	4978895573036116190	2.07	1.14	816.92	2,123.72	81.2%	-61.5%	13,384	1,060
Professional	-8305421019031779700	1.44	0.81	102.09	57.63	77.9%	77.2%	1,294	652
Inpatient	1982	2.81	1.62	42,922.79	42,987.68	73.7%	-0.2%	5,081	5,105
Professional	-1921451032501448557	2.27	1.34	1,101.18	350.45	69.4%	214.2%	11,041	1,443
Professional	2248000190198140170	2.68	1.59	1,110.51	201.12	68.5%	452.2%	26,540	1,554
Professional	-6408596399631400851	3.12	1.86	65.04	609.86	68.1%	-89.3%	510	12,858
Inpatient	4578	1.96	1.17	22,806.80	21,674.35	67.0%	5.2%	2,557	949
Professional	-4920025790801888438	0.96	2.79	287.57	341.80	-65.8%	-15.9%	758	12,200
Professional	2910058001791040718	1.66	1	294.13	270.94	65.1%	8.6%	1,391	1,118
Professional	-5683826982460411512	1.84	1.12	725.44	216.33	64.2%	235.3%	16,767	5,042
Professional	5885214640145681187	2.03	1.24	534.88	284.64	64.1%	87.9%	16,806	8,074
Professional	84010924170621954	4.36	2.68	2,246.43	1,157.23	62.3%	94.1%	3,619	38,282
Professional	-6234007227817619430	1.69	1.07	735.43	306.75	58.4%	139.7%	15,505	5,283
Professional	-7387564607477196829	1.64	1.05	441.42	243.28	56.4%	81.4%	21,838	7,777
Professional	-2648721785021500476	2.18	1.4	2,784.37	661.12	56.1%	321.2%	1,443	11,915
Professional	5625682880764874991	0.87	1.97	500.39	650.41	-55.9%	-23.1%	6,119	15,909
Professional	-3042454224713298398	1.53	0.98	859.70	203.72	55.6%	322.0%	12,649	3,606
Professional	-4574870027018923225	1.74	1.13	730.57	245.19	53.9%	198.0%	15,667	6,628
Professional	-6400922911578805274	0.91	1.94	2,502.50	1,079.89	-53.4%	131.7%	891	2,827
Professional	-6926825246419731108	1.6	1.05	439.79	243.28	52.6%	80.8%	21,796	7,777
Professional	-2605073546659075007	1.6	1.05	435.19	243.28	52.4%	78.9%	21,822	7,777
Professional	5158588235412839481	1.6	1.05	435.00	243.28	52.3%	78.8%	21,822	7,777
Professional	2921504196734275648	2.25	1.48	808.40	1,330.46	52.1%	-39.2%	10,980	3,457
Inpatient	3289	1.45	2.99	22,903.59	44,350.05	-51.6%	-48.4%	1,709	1,478
Professional	7523560528638047674	1.66	1.1	568.26	498.29	51.6%	14.0%	19,122	6,877
Inpatient	5707	1.39	2.86	17,520.97	26,407.30	-51.5%	-33.7%	997	213
Professional	5286336238657233263	0.97	1.98	254.79	634.35	-50.8%	-59.8%	4,503	15,484
Professional	-8636591443144557196	1.84	1.22	960.92	1,851.06	50.3%	-48.1%	17,765	5,568
Outpatient	3289	1.43	2.87	5,893.49	5,154.52	-50.1%	14.3%	8,940	4,314
Professional	-2373159150839077741	1.45	0.97	338.18	53.90	49.1%	527.4%	19,634	2,066
Professional	-1646437048826357785	1.37	0.92	296.30	152.09	48.9%	94.8%	11,860	6,604
Inpatient	9621	2.25	1.51	41,041.12	29,173.32	48.6%	40.7%	8,572	8,239
Professional	-3748790053026016885	1.71	1.15	431.93	401.57	48.4%	7.6%	11,637	11,078
Inpatient	8494	2.4	1.62	41,834.13	30,061.11	47.9%	39.2%	8,534	8,186

Showing 50 of 974 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_0.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_3_2.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: 5108 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
311,448,953,162,816,591	527	inf	1.15	3,953.33	3,869.71	inf%	2.2%	6,249	6,236
-3,711,121,367,896,011,833	527	inf	2.08	4,732.36	4,733.78	inf%	-0.0%	3,907	3,922
-5,146,581,664,284,924,254	527	inf	2.51	22,941.43	22,954.28	inf%	-0.1%	4,414	4,420
4,281,965,180,796,802,413	527	inf	3.08	14,104.72	14,198.50	inf%	-0.7%	8,270	8,240
-2,162,498,661,799,343,237	527	inf	2.03	9,397.67	9,399.83	inf%	-0.0%	5,686	5,698
8,361,580,493,441,765,265	527	inf	3.51	19,162.50	19,171.01	inf%	-0.0%	6,133	6,140
4,857,361,656,543,545,479	527	inf	1.55	8,966.99	8,975.47	inf%	-0.1%	8,208	8,221
-3,776,001,016,975,145,508	527	inf	2.34	15,723.78	15,727.04	inf%	-0.0%	8,176	8,182
-6,543,868,296,982,650,519	6647	3.72	1.05	16,796.77	293.24	253.0%	5628.0%	3,209	660
-3,776,001,016,975,145,508	-836335150426181903	3.31	1.06	1,106.37	176.42	211.3%	527.1%	2,826	705
8,361,580,493,441,765,265	8188513161227882061	2.5	0.83	938.13	127.09	200.4%	638.2%	3,802	1,296
4,281,965,180,796,802,413	8188513161227882061	2.5	0.83	938.13	127.09	200.4%	638.2%	3,802	1,296
4,911,047,023,988,898,996	2192	6.55	2.27	36,350.60	21,729.62	188.3%	67.3%	8,055	8,043
-2,162,498,661,799,343,237	-3501643872011304286	7.65	2.67	167.39	975.66	186.9%	-82.8%	523	2,069
-3,776,001,016,975,145,508	3888249385205433521	2.87	1.04	1,056.72	396.58	175.8%	166.5%	2,900	1,244
-3,776,001,016,975,145,508	2192	6.61	2.4	36,452.78	22,116.71	175.2%	64.8%	8,278	8,263
-2,162,498,661,799,343,237	-8636591443144557196	2.42	0.9	1,275.76	2,793.15	170.0%	-54.3%	1,895	742
4,615,092,770,909,199,842	8819	2.72	1.02	16,675.21	359.53	166.9%	4538.1%	4,793	818
-3,776,001,016,975,145,508	8819	2.7	1.02	15,069.28	359.53	165.2%	4091.4%	5,516	818
-2,162,498,661,799,343,237	8188513161227882061	2.71	1.04	885.57	263.21	161.5%	236.4%	3,914	1,831
-3,776,001,016,975,145,508	-6234007227817619430	2.46	1	1,090.43	330.34	144.7%	230.1%	3,004	854
4,281,965,180,796,802,413	5936	2.65	1.09	6,343.81	2,224.58	142.6%	185.2%	2,659	1,289
8,361,580,493,441,765,265	2406489005694322764	4.69	1.97	92.00	421.74	138.2%	-78.2%	524	2,554
-2,162,498,661,799,343,237	1232554689514378546	2.01	0.85	576.98	233.73	136.1%	146.9%	3,665	1,455
-2,162,498,661,799,343,237	-6851583214794458581	2.01	0.85	576.98	233.73	136.1%	146.9%	3,665	1,455
-3,776,001,016,975,145,508	7617470739916395063	2.15	0.91	152.83	275.27	135.7%	-44.5%	1,884	764
-8,822,629,802,732,873,104	-6851583214794458581	2.84	1.23	764.95	453.97	131.2%	68.5%	2,432	744
-8,822,629,802,732,873,104	1232554689514378546	2.84	1.23	764.95	453.97	131.2%	68.5%	2,432	744
8,361,580,493,441,765,265	-6851583214794458581	2.27	0.99	767.03	227.00	129.4%	237.9%	3,620	1,278
8,361,580,493,441,765,265	1232554689514378546	2.27	0.99	767.03	227.00	129.4%	237.9%	3,620	1,278
-3,776,001,016,975,145,508	8188513161227882061	3.02	1.36	962.65	512.62	121.4%	87.8%	3,135	829
3,133,716,806,509,821,992	-2718255887486666313	4.86	2.24	160.17	422.84	117.4%	-62.1%	575	3,514
8,361,580,493,441,765,265	7616637007288554694	1.77	0.81	448.75	121.10	117.4%	270.6%	1,625	1,294
8,361,580,493,441,765,265	-3810762884485338268	2	0.92	563.42	126.51	116.9%	345.4%	3,096	1,299
4,281,965,180,796,802,413	-3810762884485338268	2	0.92	563.42	126.51	116.9%	345.4%	3,096	1,299
-2,162,498,661,799,343,237	-2718255887486666313	4.86	2.24	160.17	422.19	116.8%	-62.1%	575	3,494
1,242,940,224,003,211,760	665	5.98	2.77	2,467.99	3,268.86	115.6%	-24.5%	1,490	5,184
-2,162,498,661,799,343,237	-6133714838765654096	1.64	0.77	1,202.66	62.96	113.6%	1810.1%	3,801	885
-3,776,001,016,975,145,508	3146678585810448215	2.87	1.35	1,216.22	178.77	111.9%	580.3%	2,845	1,036
-3,776,001,016,975,145,508	2248000190198140170	2.87	1.35	1,216.22	178.77	111.9%	580.3%	2,845	1,036
-2,162,498,661,799,343,237	942583572045281708	1.93	0.92	1,811.21	1,062.20	110.2%	70.5%	1,668	2,497
-2,162,498,661,799,343,237	1173525706844055882	1.93	0.92	1,811.21	1,062.20	110.2%	70.5%	1,668	2,497
-3,776,001,016,975,145,508	-5437767336796664563	1.87	0.89	368.34	229.37	110.2%	60.6%	2,025	726
1,295,090,440,394,638,876	3784	3.1	1.48	14,817.59	27,010.93	109.7%	-45.1%	4,599	1,704
-3,776,001,016,975,145,508	-1921451032501448557	2.63	1.26	800.44	369.38	109.6%	116.7%	1,108	590
-2,162,498,661,799,343,237	7141840494917718401	2.05	0.99	1,822.41	1,065.63	108.5%	71.0%	1,659	2,501
4,911,047,023,988,898,996	-1921451032501448557	2.57	1.24	655.65	357.11	107.6%	83.6%	917	509
-1,199,840,640,198,334,888	-6851583214794458581	2.66	1.28	1,689.81	310.79	107.4%	443.7%	2,828	590
-1,199,840,640,198,334,888	1232554689514378546	2.66	1.28	1,689.81	310.79	107.4%	443.7%	2,828	590
-3,776,001,016,975,145,508	1232554689514378546	2.55	1.23	843.33	535.55	107.2%	57.5%	2,885	958

Showing 50 of 5108 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_0.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_3_2.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: 6926 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
-2,162,498,661,799,343,237	527	Inpatient	inf	1.69	26,836.83	26,670.03	inf%	0.6%	1,468	1,482
-3,776,001,016,975,145,508	527	Inpatient	inf	3.49	57,773.78	57,687.58	inf%	0.1%	1,679	1,685
-3,711,121,367,896,011,833	527	Inpatient	inf	1.35	27,722.47	26,243.99	inf%	5.6%	189	203
4,857,361,656,543,545,479	527	Inpatient	inf	1.85	30,839.63	30,684.85	inf%	0.5%	1,629	1,643
-5,146,581,664,284,924,254	527	Inpatient	inf	3.28	54,787.95	54,703.64	inf%	0.2%	1,646	1,652
311,448,953,162,816,591	527	Inpatient	inf	1.41	18,535.92	18,051.31	inf%	2.7%	739	727
4,281,965,180,796,802,413	527	Inpatient	inf	2.82	48,511.98	48,412.72	inf%	0.2%	1,656	1,663
8,361,580,493,441,765,265	527	Inpatient	inf	3.09	51,522.91	51,416.76	inf%	0.2%	1,656	1,663
4,911,047,023,988,898,996	2192	Outpatient	6.81	1.43	24,446.71	6,676.92	376.1%	266.1%	6,346	6,327
-3,776,001,016,975,145,508	2192	Outpatient	6.82	1.54	24,238.90	6,676.82	343.7%	263.0%	6,568	6,547
-7,995,682,717,897,123,039	9194	Inpatient	4.11	0.94	101,996.43	10,880.00	339.4%	837.5%	1,692	346
-6,543,868,296,982,650,519	6647	Outpatient	3.73	1.05	11,345.59	293.24	254.3%	3769.0%	2,928	660
-3,776,001,016,975,145,508	-836335150426181903	Professional	3.31	1.06	1,106.37	176.42	211.3%	527.1%	2,826	705
4,281,965,180,796,802,413	8188513161227882061	Professional	2.5	0.83	938.13	127.09	200.4%	638.2%	3,802	1,296
8,361,580,493,441,765,265	8188513161227882061	Professional	2.5	0.83	938.13	127.09	200.4%	638.2%	3,802	1,296
-2,162,498,661,799,343,237	-3501643872011304286	Professional	7.65	2.67	167.39	975.66	186.9%	-82.8%	523	2,069
4,615,092,770,909,199,842	8819	Outpatient	2.91	1.02	3,078.11	359.53	185.6%	756.2%	3,104	818
-3,776,001,016,975,145,508	8819	Outpatient	2.86	1.02	3,567.17	359.53	181.3%	892.2%	3,818	818
-3,776,001,016,975,145,508	3888249385205433521	Professional	2.87	1.04	1,056.72	396.58	175.8%	166.5%	2,900	1,244
-7,995,682,717,897,123,039	6690	Inpatient	2.94	1.09	58,086.52	20,675.35	170.7%	180.9%	1,706	1,716
-2,162,498,661,799,343,237	-8636591443144557196	Professional	2.42	0.9	1,275.76	2,793.15	170.0%	-54.3%	1,895	742
5,373,876,635,451,223,842	6380	Inpatient	6.23	2.38	110,253.17	63,246.64	162.5%	74.3%	1,213	464
311,448,953,162,816,591	9621	Inpatient	3.64	1.39	52,728.97	24,757.21	161.9%	113.0%	1,680	1,568
-2,162,498,661,799,343,237	8188513161227882061	Professional	2.71	1.04	885.57	263.21	161.5%	236.4%	3,914	1,831
4,281,965,180,796,802,413	5936	Outpatient	2.69	1.08	5,669.59	643.90	148.9%	780.5%	2,583	1,217
-3,776,001,016,975,145,508	-6234007227817619430	Professional	2.46	1	1,090.43	330.34	144.7%	230.1%	3,004	854
8,361,580,493,441,765,265	2406489005694322764	Professional	4.69	1.97	92.00	421.74	138.2%	-78.2%	524	2,554
-2,162,498,661,799,343,237	-6851583214794458581	Professional	2.01	0.85	576.98	233.73	136.1%	146.9%	3,665	1,455
-2,162,498,661,799,343,237	1232554689514378546	Professional	2.01	0.85	576.98	233.73	136.1%	146.9%	3,665	1,455
-3,776,001,016,975,145,508	7617470739916395063	Professional	2.15	0.91	152.83	275.27	135.7%	-44.5%	1,884	764
-8,822,629,802,732,873,104	-6851583214794458581	Professional	2.84	1.23	764.95	453.97	131.2%	68.5%	2,432	744
-8,822,629,802,732,873,104	1232554689514378546	Professional	2.84	1.23	764.95	453.97	131.2%	68.5%	2,432	744
8,361,580,493,441,765,265	1232554689514378546	Professional	2.27	0.99	767.03	227.00	129.4%	237.9%	3,620	1,278
8,361,580,493,441,765,265	-6851583214794458581	Professional	2.27	0.99	767.03	227.00	129.4%	237.9%	3,620	1,278
-6,972,129,921,516,377,126	6647	Inpatient	3.56	1.59	80,211.92	44,199.23	123.6%	81.5%	530	301
5,373,876,635,451,223,842	6647	Inpatient	3.56	1.59	80,211.92	44,199.23	123.6%	81.5%	530	301
-3,776,001,016,975,145,508	8188513161227882061	Professional	3.02	1.36	962.65	512.62	121.4%	87.8%	3,135	829
-2,162,498,661,799,343,237	8819	Outpatient	2.71	1.23	4,475.46	387.56	119.8%	1054.8%	3,782	764
1,242,940,224,003,211,760	665	Outpatient	6.04	2.78	2,183.27	3,188.80	117.4%	-31.5%	1,469	5,162
3,133,716,806,509,821,992	-2718255887486666313	Professional	4.86	2.24	160.17	422.84	117.4%	-62.1%	575	3,514
8,361,580,493,441,765,265	7616637007288554694	Professional	1.77	0.81	448.75	121.10	117.4%	270.6%	1,625	1,294
4,281,965,180,796,802,413	-3810762884485338268	Professional	2	0.92	563.42	126.51	116.9%	345.4%	3,096	1,299
8,361,580,493,441,765,265	-3810762884485338268	Professional	2	0.92	563.42	126.51	116.9%	345.4%	3,096	1,299
-2,162,498,661,799,343,237	-2718255887486666313	Professional	4.86	2.24	160.17	422.19	116.8%	-62.1%	575	3,494
4,281,965,180,796,802,413	9384	Inpatient	3.45	1.6	84,531.00	33,785.15	116.3%	150.2%	1,711	103
-2,162,498,661,799,343,237	-6133714838765654096	Professional	1.64	0.77	1,202.66	62.96	113.6%	1810.1%	3,801	885
4,281,965,180,796,802,413	8079	Inpatient	3.17	1.48	55,760.56	26,310.68	113.3%	111.9%	1,706	1,685
-3,776,001,016,975,145,508	3146678585810448215	Professional	2.87	1.35	1,216.22	178.77	111.9%	580.3%	2,845	1,036
-3,776,001,016,975,145,508	2248000190198140170	Professional	2.87	1.35	1,216.22	178.77	111.9%	580.3%	2,845	1,036
8,361,580,493,441,765,265	8679	Inpatient	3.35	1.58	64,461.38	27,813.72	111.8%	131.8%	1,703	1,521

Showing 50 of 6926 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_0.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_3_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
)
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: 8 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
4,281,965,180,796,802,413	inf	2.12	5,554.47	5,576.39	inf%	-0.4%	59,911,722	60,268,433
-5,146,581,664,284,924,254	inf	2.44	9,956.37	9,986.56	inf%	-0.3%	2,809,911	2,797,930
311,448,953,162,816,591	inf	1.73	6,642.58	6,607.71	inf%	0.5%	5,053,705	5,057,935
-3,776,001,016,975,145,508	inf	2.43	7,713.05	7,689.18	inf%	0.3%	50,351,926	50,162,588
-3,711,121,367,896,011,833	inf	2.4	7,668.71	7,693.22	inf%	-0.3%	2,990,849	2,954,524
8,361,580,493,441,765,265	inf	2.14	5,697.73	5,713.90	inf%	-0.3%	62,102,719	62,362,453
4,857,361,656,543,545,479	inf	2.05	7,721.11	7,718.00	inf%	0.0%	5,711,962	5,713,406
-2,162,498,661,799,343,237	inf	2.36	6,884.71	6,885.61	inf%	-0.0%	55,689,381	55,546,303

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_0.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_3_2.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: 0 categories with significant ROID count changes

No significant ROID count swings detected.

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_0.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_3_2.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: 0 categories with significant ROID count changes

No significant ROID count swings detected.

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_0.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_3_2.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

Count: 0 categories with significant ROID count changes

No significant ROID count swings detected.

Click to see SQL

WITH
new AS (
    SELECT
        state,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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_3_2.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

By Payer Network Name

Count: 1 categories with significant ROID count changes

payer_network_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Medical Mutual Of Ohio SuperMed	775,475	635,817	139,658	22.0%

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_0.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_3_2.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
397	Blue Cross Blue Shield of Rhode Island	PPO	imputation	181	183
780	The Alliance	Comprehensive Network	payer_hospital	204	204
47	Blue Cross Blue Shield of Kansas City	Preferredcare Blue	payer_hospital	216	233
168	Blue Cross Blue Shield of South Carolina	HMO	payer_hospital	503	505
458	Blue Cross Blue Shield of North Dakota	PPO	imputation	609	624
76	Cigna	GA HMO	payer_hospital	666	664
286	MVP Health Care	HMO	payer_hospital	787	787
286	MVP Health Care	MVP EPO / PPO	payer_hospital	920	920
728	Sutter Health Plus	PPO	payer_hospital	869	925
784	UHA Health Insurance	PPO	payer_hospital	938	1,139

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_0.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_3_2.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: 50 categories with significant ROID count changes

provider_type	health_system_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Physician Group	Natividad Medical Center	365,295	5,352	359,943	6725.4%
Physician Group	Ochsner Health System	246,999	34,584	212,415	614.2%
Physician Group	California Pacific Medical Center	154,993	48,055	106,938	222.5%
Physician Group	University of California Davis Health	100,992	42,423	58,569	138.1%
Physician Group	OSF HealthCare	87,041	353,778	-266,737	-75.4%
Physician Group	Indiana University Health	65,071	222,972	-157,901	-70.8%
Physician Group	Stony Brook Medicine	268,148	184,801	83,347	45.1%
Physician Group	Boston Medical Center Corporation	225,172	384,162	-158,990	-41.4%
Physician Group	SUNY Upstate Medical University	117,873	83,550	34,323	41.1%
Physician Group	Banner Health	117,384	185,120	-67,736	-36.6%
Physician Group	St Lawrence Health System	94,381	143,003	-48,622	-34.0%
Physician Group	St Vincent Health	103,311	79,705	23,606	29.6%
Physician Group	University of Michigan - Sparrow	76,231	106,101	-29,870	-28.2%
Hospital	College Health Enterprises	113,343	90,893	22,450	24.7%
Physician Group	Bon Secours Health System	128,187	170,183	-41,996	-24.7%
Physician Group	Community Health Systems	78,650	102,145	-23,495	-23.0%
Physician Group	LifePoint Health	177,562	144,697	32,865	22.7%
Physician Group	University of Maryland Medical System	80,577	103,337	-22,760	-22.0%
Physician Group	Penn Medicine	87,522	107,274	-19,752	-18.4%
Physician Group	Maimonides Health	161,570	137,173	24,397	17.8%
Physician Group	Cleveland Clinic Health System	107,456	130,611	-23,155	-17.7%
Physician Group	Northwell Health	613,826	739,506	-125,680	-17.0%
Physician Group	Saint Lukes Health System	151,350	129,460	21,890	16.9%
Physician Group	Beth Israel Lahey Health	139,460	119,335	20,125	16.9%
Physician Group	OhioHealth	85,879	103,142	-17,263	-16.7%
Physician Group	MedStar Health	388,329	333,039	55,290	16.6%
Hospital	The MetroHealth System	129,631	111,234	18,397	16.5%
Physician Group	Munson Healthcare	245,515	212,665	32,850	15.4%
Hospital	Charleston Area Medical Center Health System	127,065	110,551	16,514	14.9%
Physician Group	UCI Health	432,421	376,717	55,704	14.8%
Hospital	Ohio State University Wexner Medical Center	250,810	218,562	32,248	14.8%
Physician Group	Piedmont Healthcare	193,571	169,436	24,135	14.2%
Hospital	Wellstar MCG Health	164,690	145,183	19,507	13.4%
Physician Group	Tower Health	123,218	142,106	-18,888	-13.3%
Physician Group	Northern Light Health	126,108	111,597	14,511	13.0%
Physician Group	Hospital Sisters Health System	154,188	177,134	-22,946	-13.0%
Physician Group	Mount Sinai Health System	275,420	315,360	-39,940	-12.7%
Hospital	The Queens Health Systems	138,534	123,142	15,392	12.5%
Hospital	Loyola University Health System	220,749	196,931	23,818	12.1%
Physician Group	Vanderbilt Health	141,367	160,639	-19,272	-12.0%
Physician Group	Baptist Memorial Health Care Corporation	117,370	105,229	12,141	11.5%
Hospital	NewYork-Presbyterian Healthcare System	854,380	766,015	88,365	11.5%
Physician Group	Avera Health	248,814	223,963	24,851	11.1%
Physician Group	UCHealth	100,456	90,548	9,908	10.9%
Physician Group	Corewell Health East	147,207	165,246	-18,039	-10.9%
Physician Group	Valley Health	168,389	188,508	-20,119	-10.7%
Physician Group	MyMichigan Health	119,602	108,096	11,506	10.6%
Hospital	UAB Medicine	271,437	246,148	25,289	10.3%
Hospital	The University of Kansas Health System	212,320	192,666	19,654	10.2%
Physician Group	Providence Health & Services - Oregon and Southwest Washington	143,978	160,182	-16,204	-10.1%

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_0.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_3_2.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: 0 categories with significant ROID count changes

No significant ROID count swings detected.

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_0.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_3_2.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: 0 categories with significant ROID count changes

No significant ROID count swings detected.

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_0.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_3_2.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: 0 categories with significant ROID count changes

No significant ROID count swings detected.

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_0.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_3_2.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: 0 categories with significant ROID count changes

No significant ROID count swings detected.

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_0.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_3_2.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: 0 categories with significant ROID count changes

No significant ROID count swings detected.

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_0.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_3_2.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: 0 categories with significant ROID count changes

No significant ROID count swings detected.

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_0.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_3_2.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_0.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_3_2.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

---