v2_1_4 → v2_2_0

Generated: 2025-10-15 10:33:24.
Filter: canonical_rate_score > 1.

---

1. Summary Statistics

Overview

• Total ROIDs: 473,729,868 → 493,633,402 (+19,903,534, +4.2%)

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
Critical Access Hospital	Outpatient	32,347,664	31,778,767	157	157	1,368	1,367	7,028	6,872
Laboratory	Professional	2,212,363	2,296,343	149	145	730	714	917	763
ASC	Outpatient	38,620,904	39,981,399	144	138	5,569	5,566	3,371	3,265
Short Term Acute Care Hospital	Outpatient	126,445,823	123,675,202	170	170	3,551	3,549	7,028	6,872
Childrens Hospital	Inpatient	1,160,296	1,171,276	139	140	157	158	1,716	1,716
Critical Access Hospital	Inpatient	5,002,443	5,084,539	152	152	1,045	1,045	1,716	1,716
Physician Group	Professional	242,779,612	224,687,156	169	166	16,448	10,193	2,388	3,283
Rehabilitation Hospital	Outpatient	3,869,885	3,762,902	140	136	345	343	7,028	6,872
Childrens Hospital	Outpatient	4,582,819	4,538,427	145	145	177	178	7,028	6,872
Imaging Center	Professional	3,044,821	3,074,379	151	151	3,004	3,004	172	172
Rehabilitation Hospital	Inpatient	531,779	536,028	124	124	373	373	1,716	1,716
Short Term Acute Care Hospital	Inpatient	33,034,993	33,143,450	170	170	3,441	3,441	1,716	1,716

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_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY provider_type, bill_type
),
old 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_1_4.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY provider_type, bill_type
)
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: 2584 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT provider_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_2_0.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_1_4.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_0.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_1_4.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: 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_2_0.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_1_4.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: 21979 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_0.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_1_4.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: 897 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_0.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_1_4.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: 9267 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	8088041443124310097	3.25	0.69	670.30	134.77	371.8%	397.4%	2,676	1,981
Professional	-2412050789005673363	4.65	1.14	693.58	162.83	309.3%	325.9%	1,750	898
Professional	7638876575735747566	4.91	1.28	242.99	179.62	282.8%	35.3%	3,194	1,205
Professional	-5162447071478212878	2.1	0.57	333.04	95.54	269.1%	248.6%	4,336	3,804
Professional	-4920025790801888438	3.68	1.07	1,042.12	588.35	243.2%	77.1%	19,715	3,844
Professional	-3701484308954829917	3.25	1.07	560.03	152.19	205.1%	268.0%	4,094	3,326
Professional	2484492513105983813	3.32	1.14	1,049.32	482.46	192.2%	117.5%	18,913	16,552
Professional	-7601151906753748579	2.15	0.77	413.12	116.19	179.3%	255.6%	2,692	1,937
Professional	2881667926747797058	3.53	1.27	1,273.08	542.35	178.4%	134.7%	16,051	14,190
Professional	-5230393382076590107	5.32	1.95	2,238.68	625.55	173.1%	257.9%	21,000	9,672
Professional	-9053401100274626026	2.15	0.81	381.79	151.95	164.8%	151.3%	4,803	4,253
Professional	-7835328115903121672	5.29	2.01	2,231.75	633.81	163.1%	252.1%	20,961	9,821
Professional	1983437024576180913	2.83	1.09	611.85	549.88	159.9%	11.3%	19,794	3,827
Professional	2387314851583189457	8.99	3.47	3,815.62	129.12	159.1%	2855.1%	11,022	1,672
Professional	4757145891381675689	9.36	3.63	209.89	515.00	158.0%	-59.2%	2,228	10,101
Professional	-7329175403650319118	5.31	2.08	2,234.34	658.58	156.0%	239.3%	20,977	9,820
Professional	-5842460810864184112	2.08	0.82	328.94	123.73	155.2%	165.8%	4,885	4,467
Professional	-1081484020411420435	3.08	1.25	1,168.84	533.33	145.5%	119.2%	13,356	8,828
Professional	2406489005694322764	5.42	2.22	2,309.08	484.39	144.3%	376.7%	20,972	5,820
Professional	5895296295649851758	2.62	1.08	411.68	430.31	141.8%	-4.3%	16,478	11,097
Professional	-7185072041828529864	3.02	1.25	479.98	426.80	141.3%	12.5%	13,966	9,224
Professional	5895404092590549085	5.3	2.22	2,226.91	484.38	139.1%	359.7%	20,922	5,820
Professional	6033096952179644873	3.16	1.38	556.33	630.30	128.7%	-11.7%	11,494	7,463
Professional	325936593663273632	3.61	1.63	1,099.22	640.53	121.8%	71.6%	29,207	19,052
Professional	-768911876826764372	2.68	1.21	975.20	491.45	120.6%	98.4%	13,015	4,422
Professional	8604849606415211303	4.09	1.88	2,016.07	821.95	117.2%	145.3%	12,060	12,732
Professional	7040464947922505555	4.22	1.96	1,420.70	831.93	114.8%	70.8%	17,828	16,687
Professional	4629723516159419184	3.16	1.48	556.33	711.09	113.1%	-21.8%	11,494	7,594
Professional	8641311238288994466	12.87	6.06	254.91	408.74	112.3%	-37.6%	534	1,939
Professional	-7259655293849955330	1.95	0.93	571.79	408.13	110.7%	40.1%	19,562	12,525
Professional	3565199733945696002	3.47	1.65	1,122.68	703.83	110.7%	59.5%	23,714	20,416
Professional	-8828878432945080228	4.11	1.97	1,342.66	765.24	108.9%	75.5%	18,237	15,042
Professional	7310144103911463741	2.75	1.32	870.39	517.47	107.8%	68.2%	13,912	13,417
Professional	8144818945658960263	3.6	1.76	1,187.93	810.67	104.6%	46.5%	9,529	8,935
Professional	7499724989419050232	3.6	1.76	1,187.93	810.34	104.3%	46.6%	9,529	8,958
Professional	-821191574134880055	3.16	1.55	990.01	597.53	104.3%	65.7%	12,014	11,700
Professional	5682712980993211997	2.15	1.05	743.38	531.37	104.2%	39.9%	16,888	8,841
Professional	5764669255102682050	3.28	1.62	551.57	671.38	103.0%	-17.8%	11,547	4,433
Professional	-784488003980147874	3.22	1.64	973.44	617.31	96.2%	57.7%	12,826	12,682
Professional	-1600103374996111667	1.43	0.73	441.12	400.96	95.2%	10.0%	20,410	3,661
Professional	6798457611697853136	3.03	1.55	842.68	868.63	95.1%	-3.0%	19,602	3,756
Professional	-6752444152034386287	2.18	1.13	826.65	476.40	92.7%	73.5%	18,899	4,432
Professional	2888404131221889105	2.18	1.13	826.65	476.40	92.7%	73.5%	18,899	4,432
Professional	4816847671873265132	2.18	1.13	823.10	476.40	92.7%	72.8%	18,871	4,432
Professional	-4408946952756149239	3.26	1.71	542.94	582.13	90.7%	-6.7%	13,213	7,066
Professional	8290448115270824851	2.67	1.4	783.42	461.07	90.6%	69.9%	16,038	17,196
Professional	1265333677384097685	3.08	1.62	1,132.45	661.67	90.3%	71.2%	27,680	29,721
Professional	1571568012687156373	2.44	1.29	651.87	534.13	88.9%	22.0%	19,511	17,092
Outpatient	31614	5.23	2.77	2,316.23	1,039.29	88.7%	122.9%	3,248	8,486
Professional	-1135767843327001985	3.14	1.67	753.60	610.12	88.1%	23.5%	19,318	15,003

Showing 50 of 9267 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_0.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_1_4.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: 48404 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
-7,580,909,833,117,018,375	4207465643062211506	14.25	1.17	289.25	248.55	1114.4%	16.4%	576	607
-2,162,498,661,799,343,237	6498564416457219675	12.54	1.04	257.30	240.8	1105.8%	6.9%	552	841
-2,162,498,661,799,343,237	1795459432863388602	14.74	1.25	473.23	231.58	1076.1%	104.3%	623	1,394
-2,162,498,661,799,343,237	-4641611426792023981	15.38	1.42	308.45	284.51	985.5%	8.4%	552	1,819
-2,162,498,661,799,343,237	-3789808428734884955	12.4	1.18	259.96	365.92	947.4%	-29.0%	598	1,578
-7,580,909,833,117,018,375	6088358956141118302	13.05	1.29	334.70	332.1	913.0%	0.8%	660	1,978
-2,162,498,661,799,343,237	6088358956141118302	13.05	1.29	334.70	332.1	913.0%	0.8%	660	1,978
-2,162,498,661,799,343,237	-3672825996592569132	13.25	1.33	271.79	163.88	897.3%	65.9%	552	543
-2,162,498,661,799,343,237	2445671902110055086	15.45	1.55	299.06	408.77	895.0%	-26.8%	561	989
-2,162,498,661,799,343,237	3285219334302878922	11.53	1.17	377.99	395.79	884.0%	-4.5%	601	1,624
-2,162,498,661,799,343,237	-3508723620191861858	10.84	1.12	242.94	232.79	868.3%	4.4%	740	1,364
-2,162,498,661,799,343,237	-2815379622068570354	12.95	1.4	347.36	215.28	827.7%	61.4%	647	570
2,039,367,776,086,447,454	-2815379622068570354	12.92	1.4	339.10	215.28	825.3%	57.5%	647	570
-7,580,909,833,117,018,375	-2178284160103193077	13.05	1.49	334.70	414.12	777.0%	-19.2%	660	1,880
-2,162,498,661,799,343,237	-2178284160103193077	13.05	1.49	334.70	414.12	777.0%	-19.2%	660	1,880
-7,580,909,833,117,018,375	-5990478459781628369	13.05	1.71	334.70	541.68	662.6%	-38.2%	660	3,120
-2,162,498,661,799,343,237	-5990478459781628369	13.05	1.71	334.70	541.82	662.5%	-38.2%	660	3,119
-2,162,498,661,799,343,237	2387314851583189457	9.47	1.29	4,231.54	191.46	632.7%	2110.1%	1,673	624
-2,162,498,661,799,343,237	4093988838131079581	7.57	1.05	335.11	365.58	618.7%	-8.3%	960	1,714
8,611,629,356,743,080,984	4093988838131079581	7.57	1.07	335.11	394.73	609.1%	-15.1%	960	1,900
-2,162,498,661,799,343,237	8290448115270824851	7.11	1.01	2,181.28	203.98	601.0%	969.4%	3,385	527
-3,711,121,367,896,011,833	2586632428107525754	9.65	1.38	265.36	365.89	600.4%	-27.5%	582	2,853
-2,162,498,661,799,343,237	-6231078513145602235	8.15	1.16	246.10	142.31	600.0%	72.9%	1,122	702
-2,162,498,661,799,343,237	2881667926747797058	6.85	1.02	2,186.71	243.19	574.5%	799.2%	3,385	869
8,611,629,356,743,080,984	-2166609934926329624	6.83	1.07	2,190.04	220.39	541.6%	893.7%	3,385	740
-2,162,498,661,799,343,237	-2166609934926329624	6.83	1.07	2,188.15	220.39	541.4%	892.9%	3,385	740
8,611,629,356,743,080,984	4536163065128173822	6.83	1.07	2,190.04	224.26	540.9%	876.6%	3,385	756
-2,162,498,661,799,343,237	4536163065128173822	6.83	1.07	2,188.15	233.23	540.8%	838.2%	3,385	786
-1,068,861,235,619,238,745	2881667926747797058	6.43	1.02	2,529.86	243.19	532.6%	940.3%	3,280	869
2,119,715,416,692,563,737	-774994608006226853	7.38	1.17	2,185.52	249.31	528.9%	776.6%	3,385	747
-2,162,498,661,799,343,237	-774994608006226853	7.18	1.17	2,181.59	249.31	512.1%	775.1%	3,385	747
-2,162,498,661,799,343,237	7314454361167236675	9.55	1.61	260.47	272.07	493.2%	-4.3%	667	927
5,177,932,399,303,345,046	6536597113014946830	6.73	1.15	2,140.04	297.9	485.5%	618.4%	3,301	672
-2,162,498,661,799,343,237	-1480553295643851709	7.29	1.25	2,181.28	276.03	481.4%	690.2%	3,385	512
-7,572,382,112,705,938,029	7040464947922505555	7.94	1.38	2,560.05	468.27	473.7%	446.7%	3,293	1,606
5,177,932,399,303,345,046	-3586731365974281447	9.59	1.86	288.48	480.97	416.5%	-40.0%	950	656
-2,162,498,661,799,343,237	-3586731365974281447	9.59	1.86	288.48	480.97	416.5%	-40.0%	950	656
8,611,629,356,743,080,984	-2731650012927996138	14.93	2.9	311.06	514.62	415.0%	-39.6%	552	3,885
-2,162,498,661,799,343,237	-7141560153209773702	13.98	2.75	276.88	421.42	407.7%	-34.3%	552	3,884
-2,162,498,661,799,343,237	-3635642007527898444	13.49	2.72	276.88	423.63	395.5%	-34.6%	552	3,884
2,039,367,776,086,447,454	-3950792584988177046	15.36	3.12	325.06	641.69	392.9%	-49.3%	561	4,994
-3,776,001,016,975,145,508	3473058361425885795	5.22	1.07	1,926.26	148.61	390.1%	1196.2%	3,377	1,194
-7,580,909,833,117,018,375	1423411510821700539	15.17	3.11	302.92	586.2	387.0%	-48.3%	552	4,188
-2,162,498,661,799,343,237	1423411510821700539	15.17	3.11	302.92	586.2	387.0%	-48.3%	552	4,188
-2,162,498,661,799,343,237	-4414611568672585566	15.36	3.17	307.97	430.99	385.3%	-28.5%	552	3,600
-2,162,498,661,799,343,237	5620639211081377557	15.31	3.16	302.92	556.39	385.1%	-45.6%	552	4,219
8,611,629,356,743,080,984	5598064285725613169	6.83	1.41	2,190.04	340.49	385.0%	543.2%	3,385	1,042
-2,162,498,661,799,343,237	7725494870565750717	13.99	2.89	390.29	525.92	384.9%	-25.8%	658	4,465
-2,162,498,661,799,343,237	724935445021148629	15.05	3.1	308.79	426.91	384.7%	-27.7%	552	3,543
1,998,605,818,287,672,800	112221256536407786	15.58	3.23	302.58	939.91	382.0%	-67.8%	594	6,285

Showing 50 of 48404 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_0.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_1_4.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: 52771 entities with significant rate changes

network_id	provider_id	bill_type	new_avg_pct_of_medicare_rate	old_avg_pct_of_medicare_rate	new_avg_canonical_rate	old_avg_canonical_rate	medicare_rate_pct_change	canonical_rate_pct_change	new_roid_count	old_roid_count
-7,580,909,833,117,018,375	4207465643062211506	Professional	14.25	1.17	289.25	248.55	1114.4%	16.4%	576	607
-2,162,498,661,799,343,237	6498564416457219675	Professional	12.54	1.04	257.30	240.8	1105.8%	6.9%	552	841
-2,162,498,661,799,343,237	1795459432863388602	Professional	14.74	1.25	473.23	231.58	1076.1%	104.3%	623	1,394
-2,162,498,661,799,343,237	-4641611426792023981	Professional	15.38	1.42	308.45	284.51	985.5%	8.4%	552	1,819
-2,162,498,661,799,343,237	-3789808428734884955	Professional	12.4	1.18	259.96	365.92	947.4%	-29.0%	598	1,578
-7,580,909,833,117,018,375	6088358956141118302	Professional	13.05	1.29	334.70	332.1	913.0%	0.8%	660	1,978
-2,162,498,661,799,343,237	6088358956141118302	Professional	13.05	1.29	334.70	332.1	913.0%	0.8%	660	1,978
-2,162,498,661,799,343,237	-3672825996592569132	Professional	13.25	1.33	271.79	163.88	897.3%	65.9%	552	543
-2,162,498,661,799,343,237	2445671902110055086	Professional	15.45	1.55	299.06	408.77	895.0%	-26.8%	561	989
-2,162,498,661,799,343,237	3285219334302878922	Professional	11.53	1.17	377.99	395.79	884.0%	-4.5%	601	1,624
-2,162,498,661,799,343,237	-3508723620191861858	Professional	10.84	1.12	242.94	232.79	868.3%	4.4%	740	1,364
-2,162,498,661,799,343,237	-2815379622068570354	Professional	12.95	1.4	347.36	215.28	827.7%	61.4%	647	570
2,039,367,776,086,447,454	-2815379622068570354	Professional	12.92	1.4	339.10	215.28	825.3%	57.5%	647	570
-2,162,498,661,799,343,237	-2178284160103193077	Professional	13.05	1.49	334.70	414.12	777.0%	-19.2%	660	1,880
-7,580,909,833,117,018,375	-2178284160103193077	Professional	13.05	1.49	334.70	414.12	777.0%	-19.2%	660	1,880
-7,580,909,833,117,018,375	-5990478459781628369	Professional	13.05	1.71	334.70	541.68	662.6%	-38.2%	660	3,120
-2,162,498,661,799,343,237	-5990478459781628369	Professional	13.05	1.71	334.70	541.82	662.5%	-38.2%	660	3,119
-2,162,498,661,799,343,237	2387314851583189457	Professional	9.47	1.29	4,231.54	191.46	632.7%	2110.1%	1,673	624
-2,162,498,661,799,343,237	4093988838131079581	Professional	7.57	1.05	335.11	365.58	618.7%	-8.3%	960	1,714
8,611,629,356,743,080,984	4093988838131079581	Professional	7.57	1.07	335.11	394.73	609.1%	-15.1%	960	1,900
-2,162,498,661,799,343,237	8290448115270824851	Professional	7.11	1.01	2,181.28	203.98	601.0%	969.4%	3,385	527
-3,711,121,367,896,011,833	2586632428107525754	Professional	9.65	1.38	265.36	365.89	600.4%	-27.5%	582	2,853
-2,162,498,661,799,343,237	-6231078513145602235	Professional	8.15	1.16	246.10	142.31	600.0%	72.9%	1,122	702
-2,162,498,661,799,343,237	2881667926747797058	Professional	6.85	1.02	2,186.71	243.19	574.5%	799.2%	3,385	869
8,611,629,356,743,080,984	-2166609934926329624	Professional	6.83	1.07	2,190.04	220.39	541.6%	893.7%	3,385	740
-2,162,498,661,799,343,237	-2166609934926329624	Professional	6.83	1.07	2,188.15	220.39	541.4%	892.9%	3,385	740
8,611,629,356,743,080,984	4536163065128173822	Professional	6.83	1.07	2,190.04	224.26	540.9%	876.6%	3,385	756
-2,162,498,661,799,343,237	4536163065128173822	Professional	6.83	1.07	2,188.15	233.23	540.8%	838.2%	3,385	786
-1,068,861,235,619,238,745	2881667926747797058	Professional	6.43	1.02	2,529.86	243.19	532.6%	940.3%	3,280	869
2,119,715,416,692,563,737	-774994608006226853	Professional	7.38	1.17	2,185.52	249.31	528.9%	776.6%	3,385	747
-2,162,498,661,799,343,237	-774994608006226853	Professional	7.18	1.17	2,181.59	249.31	512.1%	775.1%	3,385	747
-2,162,498,661,799,343,237	7314454361167236675	Professional	9.55	1.61	260.47	272.07	493.2%	-4.3%	667	927
5,177,932,399,303,345,046	6536597113014946830	Professional	6.73	1.15	2,140.04	297.9	485.5%	618.4%	3,301	672
-2,162,498,661,799,343,237	-1480553295643851709	Professional	7.29	1.25	2,181.28	276.03	481.4%	690.2%	3,385	512
-7,572,382,112,705,938,029	7040464947922505555	Professional	7.94	1.38	2,560.05	468.27	473.7%	446.7%	3,293	1,606
5,177,932,399,303,345,046	-3586731365974281447	Professional	9.59	1.86	288.48	480.97	416.5%	-40.0%	950	656
-2,162,498,661,799,343,237	-3586731365974281447	Professional	9.59	1.86	288.48	480.97	416.5%	-40.0%	950	656
8,611,629,356,743,080,984	-2731650012927996138	Professional	14.93	2.9	311.06	514.62	415.0%	-39.6%	552	3,885
-2,162,498,661,799,343,237	-7141560153209773702	Professional	13.98	2.75	276.88	421.42	407.7%	-34.3%	552	3,884
-2,162,498,661,799,343,237	-3635642007527898444	Professional	13.49	2.72	276.88	423.63	395.5%	-34.6%	552	3,884
2,039,367,776,086,447,454	-3950792584988177046	Professional	15.36	3.12	325.06	641.69	392.9%	-49.3%	561	4,994
-3,776,001,016,975,145,508	3473058361425885795	Professional	5.22	1.07	1,926.26	148.61	390.1%	1196.2%	3,377	1,194
-7,580,909,833,117,018,375	1423411510821700539	Professional	15.17	3.11	302.92	586.2	387.0%	-48.3%	552	4,188
-2,162,498,661,799,343,237	1423411510821700539	Professional	15.17	3.11	302.92	586.2	387.0%	-48.3%	552	4,188
-2,162,498,661,799,343,237	-4414611568672585566	Professional	15.36	3.17	307.97	430.99	385.3%	-28.5%	552	3,600
-2,162,498,661,799,343,237	5620639211081377557	Professional	15.31	3.16	302.92	556.39	385.1%	-45.6%	552	4,219
8,611,629,356,743,080,984	5598064285725613169	Professional	6.83	1.41	2,190.04	340.49	385.0%	543.2%	3,385	1,042
-2,162,498,661,799,343,237	7725494870565750717	Professional	13.99	2.89	390.29	525.92	384.9%	-25.8%	658	4,465
-2,162,498,661,799,343,237	724935445021148629	Professional	15.05	3.1	308.79	426.91	384.7%	-27.7%	552	3,543
1,998,605,818,287,672,800	112221256536407786	Professional	15.58	3.23	302.58	939.91	382.0%	-67.8%	594	6,285

Showing 50 of 52771 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_0.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_1_4.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: 78 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,902,096,239,283,128,733	3.27	2.71	1,418.19	1,939.48	20.9%	-26.9%	636,157	430,650
3,376,187,909,306,580,799	2.31	2.91	2,234.94	2,426.22	-20.7%	-7.9%	1,660,193	1,527,458
-7,428,563,680,805,185,856	2	1.69	4,121.86	5,169.27	18.5%	-20.3%	62,178	45,817
5,605,126,640,932,710,930	2.13	2.56	24,538.27	23,026.31	-16.9%	6.6%	341,180	365,468
6,037,342,850,284,539,624	2.48	2.97	3,073.65	3,221.46	-16.8%	-4.6%	1,759,120	1,636,691
8,530,564,294,955,512,747	2.71	2.33	4,385.70	4,916.64	16.3%	-10.8%	1,464,433	1,324,167
-8,508,828,306,067,810,497	3.12	2.68	2,830.40	3,106.59	16.1%	-8.9%	1,512,149	1,386,655
-8,403,435,045,225,894,954	3.3	2.86	4,101.05	6,418.51	15.7%	-36.1%	598,076	445,365
8,635,146,956,337,329,045	1.63	1.41	4,694.36	3,832.48	15.4%	22.5%	306,202	426,173
1,017,413,876,722,652,424	2.96	2.59	5,823.33	6,151.16	14.3%	-5.3%	5,162,601	5,010,756
311,448,953,162,816,591	2.21	1.94	4,950.52	5,152.10	14.1%	-3.9%	5,891,044	5,742,555
5,697,578,418,236,370,479	2.46	2.16	3,773.56	4,636.95	13.9%	-18.6%	609,189	511,809
7,616,836,552,481,822,175	3.04	2.67	6,349.94	6,605.76	13.8%	-3.9%	5,597,668	5,561,512
-3,063,756,195,086,079,598	3.15	2.78	4,429.77	4,877.76	13.4%	-9.2%	2,347,206	2,196,593
-2,596,917,860,902,475,653	2.74	2.42	6,931.44	7,773.45	13.3%	-10.8%	806,842	715,020
-7,942,674,608,514,495,282	3.49	3.08	5,855.95	6,246.80	13.1%	-6.3%	1,946,028	1,895,559
7,750,147,370,859,442,459	3.72	3.3	8,196.63	9,217.95	13.0%	-11.1%	1,424,538	1,266,158
-7,225,588,104,346,557,715	3.73	3.3	8,199.43	9,239.30	12.9%	-11.3%	1,429,197	1,270,820
-6,588,387,606,928,155,169	3.74	3.31	8,476.34	9,502.54	12.9%	-10.8%	1,422,458	1,264,158
5,781,103,079,387,890,636	2.97	2.63	2,884.34	3,112.16	12.7%	-7.3%	1,724,357	1,608,675
-7,365,202,427,208,089,985	3.16	2.82	5,293.66	6,579.53	12.3%	-19.5%	890,833	764,387
-3,954,532,594,486,231,313	2.22	2.52	7,158.48	8,925.05	-11.8%	-19.8%	1,685,810	1,307,284
8,611,629,356,743,080,984	3.29	2.95	7,410.76	7,604.54	11.7%	-2.5%	4,380,977	4,471,055
-4,944,049,946,733,699,762	4.5	4.03	8,089.68	11,088.78	11.6%	-27.0%	603,874	425,964
-7,227,420,687,598,643,435	2.26	2.02	6,656.52	7,561.44	11.6%	-12.0%	610,653	537,648
-5,617,272,627,028,270,639	3.32	2.98	7,303.89	8,085.19	11.3%	-9.7%	815,938	722,016
1,998,605,818,287,672,800	3.66	3.29	5,156.08	4,310.40	11.1%	19.6%	728,635	921,292
4,857,361,656,543,545,479	2.57	2.32	6,087.94	6,388.52	10.9%	-4.7%	6,649,139	6,343,476
-4,061,814,287,107,819,804	2.69	2.43	4,329.09	4,654.80	10.8%	-7.0%	169,968	182,081
-7,304,296,722,942,665,713	3.54	3.2	9,199.05	10,119.17	10.7%	-9.1%	1,575,352	1,440,156
-6,639,294,638,665,975,096	3.13	2.83	3,686.17	4,806.57	10.6%	-23.3%	449,723	434,745
1,222,051,438,589,625,017	2.72	2.47	9,336.64	9,720.02	10.4%	-3.9%	875,135	875,165
-1,199,840,640,198,334,888	3.42	3.09	6,405.39	6,830.82	10.4%	-6.2%	2,079,588	1,975,639
1,481,239,756,837,705,768	2.94	3.25	13,353.60	5,782.52	-9.6%	130.9%	326,713	867,379
8,180,198,595,049,150,616	1.73	1.59	3,867.70	4,359.83	8.6%	-11.3%	238,610	206,471
-7,695,283,351,826,393,948	2.5	2.73	6,571.54	7,935.01	-8.2%	-17.2%	1,130,129	918,629
6,352,083,177,318,679,706	2.62	2.43	1,940.91	2,325.21	8.0%	-16.5%	418,996	331,525
211,050,470,199,744,924	2.01	2.17	5,610.06	7,622.15	-7.5%	-26.4%	581,215	403,592
-1,016,849,053,752,795,150	3.19	3.45	8,137.76	17,171.53	-7.3%	-52.6%	529,768	464,504
6,355,182,945,120,798,897	2.1	2.26	5,590.95	6,963.34	-7.3%	-19.7%	424,353	327,155
-2,105,332,499,153,538,685	3.89	3.63	9,176.45	11,372.31	7.1%	-19.3%	860,858	708,056
-3,711,121,367,896,011,833	2.69	2.51	5,545.00	6,166.70	7.1%	-10.1%	3,039,067	2,868,411
-530,429,337,716,425,230	1.86	1.75	4,406.07	4,983.80	6.7%	-11.6%	942,390	793,716
-163,892,009,874,547,167	2.45	2.63	12,733.85	14,548.77	-6.6%	-12.5%	415,602	385,716
1,134,341,852,115,164,929	1.69	1.8	5,466.72	7,026.04	-6.5%	-22.2%	1,109,267	876,752
-1,068,861,235,619,238,745	2.59	2.77	4,795.80	6,266.12	-6.5%	-23.5%	1,918,709	1,505,089
-1,595,830,227,154,592,941	1.92	1.81	4,621.43	5,290.16	6.3%	-12.6%	943,695	782,648
6,437,583,333,460,157,798	2.39	2.25	7,390.94	8,275.81	6.2%	-10.7%	961,738	871,772
-7,940,170,945,989,807,859	3.39	3.2	12,279.78	13,782.57	6.2%	-10.9%	335,333	325,072
6,638,296,964,362,884,623	3.05	3.23	8,676.94	10,611.75	-5.8%	-18.2%	162,893	156,094

Showing 50 of 78 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_0.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_1_4.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: 11 categories with significant ROID count changes

service_line	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Diabetes	191,427	47,697	143,730	301.3%
Anesthesia	28,710,611	18,994,000	9,716,611	51.2%
Consultative and Preventative Care	9,086,731	6,119,228	2,967,503	48.5%
Lab/Path	9,596,644	7,225,268	2,371,376	32.8%
Behavioral Health	5,903,019	4,459,597	1,443,422	32.4%
Infectious Disease	3,654,986	2,983,984	671,002	22.5%
Emergency	573,921	479,672	94,249	19.6%
Dental	454,189	553,740	-99,551	-18.0%
Endocrinology	2,029,238	2,300,194	-270,956	-11.8%
Transplant	324,916	366,983	-42,067	-11.5%
Radiology	49,711,929	44,786,942	4,924,987	11.0%

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_0.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_1_4.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: 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_2_0.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_1_4.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY provider_type
)
SELECT
    provider_type,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (provider_type)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY ABS(roid_count_pct_change) DESC

By State

Count: 14 categories with significant ROID count changes

state	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
DC	2,986,006	2,274,097	711,909	31.3%
MD	4,960,649	4,087,023	873,626	21.4%
KY	6,303,351	5,342,973	960,378	18.0%
ND	1,468,492	1,260,937	207,555	16.5%
WV	2,073,241	1,784,243	288,998	16.2%
RI	1,279,552	1,109,879	169,673	15.3%
TN	10,337,680	8,984,275	1,353,405	15.1%
MN	8,628,837	7,600,288	1,028,549	13.5%
NH	3,153,594	2,788,863	364,731	13.1%
ID	3,086,975	3,519,269	-432,294	-12.3%
NC	11,811,247	10,559,091	1,252,156	11.9%
NE	3,761,551	4,244,345	-482,794	-11.4%
OR	6,017,592	6,782,236	-764,644	-11.3%
ME	2,893,309	2,611,092	282,217	10.8%

Click to see SQL

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

payer_network_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Regence Blue Cross Blue Shield OR PPO	326,713	867,379	-540,666	-62.3%
Regence Blue Cross Blue Shield ID PPO	234,086	528,676	-294,590	-55.7%
Regence Blue Cross Blue Shield WA PPO	481,344	1,059,469	-578,125	-54.6%
Univera Healthcare PPO	636,157	430,650	205,507	47.7%
Anthem CT Century Preferred PPO	581,215	403,592	177,623	44.0%
Sutter Health Plus PPO	603,874	425,964	177,910	41.8%
Ambetter HMO	1,701,624	1,235,466	466,158	37.7%
MotivHealth Insurance Company MotivNet	62,178	45,817	16,361	35.7%
Regence Blue Cross Blue Shield UT PPO	598,076	445,365	152,711	34.3%
Anthem NH HMO	424,353	327,155	97,198	29.7%
Anthem VA HMO	1,685,810	1,307,284	378,526	29.0%
Blue Cross Blue Shield of Maryland & DC (CareFirst) Preferred Provider Network (PPO)	306,202	426,173	-119,971	-28.2%
Aetna MD HMO	1,918,709	1,505,089	413,620	27.5%
MetroPlus Health Gold	1,007,793	793,582	214,211	27.0%
Anthem KY PPO	1,109,267	876,752	232,515	26.5%
Mass General Brigham Health Plan Commercial PPO	418,996	331,525	87,471	26.4%
MetroPlus Health Essential Plan	1,637,787	1,310,428	327,359	25.0%
Anthem CO Blue Preferred PPO	1,130,129	918,629	211,500	23.0%
Blue Cross Blue Shield of Pennsylvania (Capital Blue Cross) PPO	860,858	708,056	152,802	21.6%
PreferredOne PPO	402,549	331,614	70,935	21.4%
Aetna NC HMO	1,213,498	1,002,622	210,876	21.0%
Blue Cross Blue Shield of Nebraska Networkblue PPO	728,635	921,292	-192,657	-20.9%
Blue Cross Blue Shield of Massachusetts PPO	943,695	782,648	161,047	20.6%
Aetna VA HMO	1,434,713	1,193,785	240,928	20.2%
Blue Cross Blue Shield of Minnesota Aware PPO	1,733,190	1,454,065	279,125	19.2%
Healthfirst NY Essential Plan	609,189	511,809	97,380	19.0%
Blue Cross Blue Shield of Massachusetts HMO	942,390	793,716	148,674	18.7%
Blue Cross Blue Shield of Minnesota HMO	1,540,579	1,299,532	241,047	18.5%
Anthem GA OPEN ACCESS	2,602,011	2,207,895	394,116	17.9%
Anthem NY PPO	2,957,477	2,523,357	434,120	17.2%
Cigna TN HMO	1,010,718	863,237	147,481	17.1%
Anthem NH OPEN ACCESS	449,821	384,802	65,019	16.9%
Blue Cross Blue Shield of North Dakota PPO	374,173	320,346	53,827	16.8%
Premera Blue Cross HMO	890,833	764,387	126,446	16.5%
Blue Cross Blue Shield of Rhode Island PPO	238,610	206,471	32,139	15.6%
Network Health Plan PPO	529,768	464,504	65,264	14.1%
Anthem MO Blue Access PPO	1,351,220	1,187,721	163,499	13.8%
Blue Cross Blue Shield of Oklahoma HMO	610,653	537,648	73,005	13.6%
Providence Health Plan PEBB Choice	815,938	722,016	93,922	13.0%
Blue Cross Blue Shield of Oklahoma Blue Preferred PPO	806,842	715,020	91,822	12.8%
EmblemHealth GHI HMO	1,422,458	1,264,158	158,300	12.5%
EmblemHealth HIP HMO	1,424,538	1,266,158	158,380	12.5%
EmblemHealth HIP Prime POS	1,429,197	1,270,820	158,377	12.5%
Blue Cross Blue Shield of Tennessee Preferred	1,875,689	1,668,282	207,407	12.4%
Blue Cross Blue Shield of Pennsylvania (Independence) KHPE Commercial HMO/POS	422,386	378,499	43,887	11.6%
Harvard Pilgrim Health Care Choicenet PPO	1,246,963	1,120,627	126,336	11.3%
Harvard Pilgrim Health Care HMO	1,218,770	1,097,494	121,276	11.1%
UPMC Health Plan Premium	1,717,029	1,549,569	167,460	10.8%
Anthem WI Blue Access PPO	1,413,957	1,278,081	135,876	10.6%
Blue Cross Blue Shield of Kansas Blue Choice	1,464,433	1,324,167	140,266	10.6%

Showing 50 of 53 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_0.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_1_4.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
643	United Healthcare	Choice Plus	imputation	7,774,145	0
643	United Healthcare	HMO	hospital	8,924,223	0
643	United Healthcare	HMO	payer	55,487,535	0
643	United Healthcare	Choice Plus	payer	56,506,424	0
643	United Healthcare	HMO	imputation	7,500,121	0
643	United Healthcare	Choice Plus	hospital	9,478,948	0
791	MotivHealth Insurance Company	MotivNet	imputation	103	103
397	Blue Cross Blue Shield of Rhode Island	PPO	imputation	126	126
317	Tufts Health Plan	PPO MA	payer	749	325
42	Anthem	NH HMO	imputation	458	431

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_0.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_1_4.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: 142 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	HCA South Atlantic Division	151,723	8,128	143,595	1766.7%
Physician Group	Sentara Health	367,517	21,681	345,836	1595.1%
Physician Group	Indiana University Health	328,372	20,965	307,407	1466.3%
Physician Group	HonorHealth	306,095	21,455	284,640	1326.7%
Physician Group	Providence Swedish	318,746	22,881	295,865	1293.1%
Physician Group	Piedmont Healthcare	305,705	26,685	279,020	1045.6%
Physician Group	Mercy	767,505	70,490	697,015	988.8%
Physician Group	PeaceHealth	316,287	29,756	286,531	962.9%
Physician Group	AdventHealth	380,215	38,896	341,319	877.5%
Physician Group	Carle Health System	206,608	21,494	185,114	861.2%
Physician Group	Franciscan Missionaries of Our Lady Health System	197,896	23,205	174,691	752.8%
Physician Group	Providence Health & Services - Oregon and Southwest Washington	180,058	21,585	158,473	734.2%
Physician Group	Medical University of South Carolina Health System	378,214	46,293	331,921	717.0%
Physician Group	Billings Clinic Health System	101,835	12,807	89,028	695.2%
Physician Group	LifePoint Health	208,273	27,558	180,715	655.8%
Physician Group	Baptist Memorial Health Care Corporation	128,174	17,463	110,711	634.0%
Physician Group	Adventist Health	361,670	54,662	307,008	561.6%
Physician Group	UPMC Central Pennsylvania	247,675	37,712	209,963	556.8%
Physician Group	UCLA Health System	588,421	93,625	494,796	528.5%
Physician Group	McLaren Health Care Corporation	174,817	28,373	146,444	516.1%
Physician Group	Northern Light Health	196,509	31,902	164,607	516.0%
Physician Group	Vanderbilt Health	148,622	25,660	122,962	479.2%
Physician Group	Methodist Health System	124,680	22,116	102,564	463.8%
Physician Group	OSF HealthCare	313,510	56,455	257,055	455.3%
Physician Group	Huntsville Hospital Health System	138,350	25,289	113,061	447.1%
Physician Group	TMC Health	154,935	28,757	126,178	438.8%
Physician Group	Penn Medicine Lancaster General Health	162,512	30,574	131,938	431.5%
Physician Group	University of California San Diego Health System	238,571	46,082	192,489	417.7%
Physician Group	Saint Francis Health System	113,923	22,140	91,783	414.6%
Physician Group	HCA North Carolina Division	129,210	25,514	103,696	406.4%
Physician Group	SSM Health in Wisconsin	106,890	21,763	85,127	391.2%
Physician Group	Orlando Health	105,540	22,411	83,129	370.9%
Physician Group	Hawaii Pacific Health	105,435	23,027	82,408	357.9%
Physician Group	Penn Highlands Healthcare	166,676	37,611	129,065	343.2%
Physician Group	WellSpan Health	163,294	37,007	126,287	341.3%
Physician Group	Franciscan Alliance Inc	109,265	24,970	84,295	337.6%
Physician Group	Maimonides Health	221,518	51,029	170,489	334.1%
Physician Group	St Lawrence Health System	192,475	46,656	145,819	312.5%
Physician Group	Boston Medical Center Corporation	433,610	109,439	324,171	296.2%
Physician Group	Corewell Health East	227,185	57,440	169,745	295.5%
Physician Group	UMass Memorial Health Care	152,603	39,429	113,174	287.0%
Physician Group	CoxHealth	162,053	41,978	120,075	286.0%
Physician Group	Baptist Health	235,764	61,144	174,620	285.6%
Physician Group	Banner Health	279,801	76,461	203,340	265.9%
Physician Group	Stony Brook Medicine	322,033	88,046	233,987	265.8%
Physician Group	Bon Secours Health System	221,367	61,684	159,683	258.9%
Physician Group	St Lukes University Health Network	346,518	99,376	247,142	248.7%
Physician Group	Ascension Wisconsin	197,983	57,008	140,975	247.3%
Physician Group	Trinity Health Michigan	212,987	62,303	150,684	241.9%
Physician Group	SUNY Upstate Medical University	144,777	43,673	101,104	231.5%

Showing 50 of 142 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_0.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_1_4.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY 1,2
)
SELECT
    provider_type,
    health_system_name,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (provider_type, health_system_name)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
AND (
    new.roid_count > 100000 OR old.roid_count > 100000
)
ORDER BY ABS(roid_count_pct_change) DESC

By Canonical Rate Score

Count: 2 categories with significant ROID count changes

canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
2	3,441,727	13,353,886	-9,912,159	-74.2%
3	59,388,568	47,514,341	11,874,227	25.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_0.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_1_4.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    GROUP BY canonical_rate_score
)
SELECT
    canonical_rate_score,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (canonical_rate_score)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY ABS(roid_count_pct_change) DESC

By Canonical Rate Class

Count: 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_2_0.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_1_4.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: 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_2_0.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_1_4.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: 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_2_0.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_1_4.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: 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_2_0.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_1_4.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
komodo_gross_charge_provider	1,715,508	1,457,545	257,963	17.7%
mrf_gross_charge_cbsa_median	10,293,463	11,981,506	-1,688,043	-14.1%

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

---