v2_2_2 → v2_3_0

Generated: 2025-11-27 17:24:31.
Filter: canonical_rate_score > 1.

---

1. Summary Statistics

Overview

• Total ROIDs: 401,858,174 → 378,780,950 (-23,077,224, -5.7%)

By Provider Type and Bill Type

provider_type	bill_type	new_distinct_roids	old_distinct_roids	new_distinct_networks	old_distinct_networks	new_distinct_providers	old_distinct_providers	new_distinct_billing_codes	old_distinct_billing_codes
ASC	Outpatient	15,194,451	25,049,884	143	139	5,590	5,583	3,369	3,371
Physician Group	Professional	150,471,614	152,596,549	167	167	17,335	16,500	3,781	2,838
Hospital	Inpatient	41,786,024	42,273,347	167	168	5,594	5,571	1,717	1,716
Laboratory	Professional	3,535,461	2,158,493	146	149	696	718	917	917
Imaging Center	Professional	2,980,187	3,028,849	150	151	2,976	2,987	172	172
Hospital	Outpatient	164,813,213	176,751,052	167	168	5,647	5,620	7,064	7,064

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_3_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_2_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: 1231 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT provider_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_3_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_2_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: 1 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT payer_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_3_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_2_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: 1 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT network_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_3_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_2_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: 8196 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_3_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_2_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: 12 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_3_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_2_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: 19465 entities with significant rate changes

bill_type	provider_id	new_avg_pct_of_medicare_rate	old_avg_pct_of_medicare_rate	new_avg_canonical_rate	old_avg_canonical_rate	medicare_rate_pct_change	canonical_rate_pct_change	new_roid_count	old_roid_count
Outpatient	9732	2.86	0.65	7,973.48	2,834.54	342.7%	181.3%	10,985	10,281
Inpatient	6076	6.62	1.75	111,634.92	32,695.22	278.3%	241.4%	101	1,497
Outpatient	23549	5.3	1.53	8,180.79	1,530.04	245.9%	434.7%	876	644
Outpatient	9960	3.02	0.9	7,057.04	1,252.20	234.3%	463.6%	8,130	11,494
Inpatient	5581	3.61	1.1	53,993.87	19,401.32	228.7%	178.3%	1,805	3,753
Inpatient	1592	5.49	1.75	58,349.67	29,629.84	213.0%	96.9%	482	2,559
Outpatient	26424	3	0.97	569.37	706.45	209.2%	-19.4%	1,562	11,456
Professional	-7698044148695228947	3.32	1.09	2,013.67	193.25	204.4%	942.0%	3,634	2,069
Outpatient	30859	2.01	0.66	1,912.05	877.40	203.9%	117.9%	513	4,051
Inpatient	9933	3.59	1.18	65,312.12	18,760.62	203.5%	248.1%	13,008	5,926
Outpatient	24355	2.17	0.72	1,067.68	979.41	202.9%	9.0%	562	3,128
Inpatient	6069	7.19	2.42	115,494.77	45,727.48	197.6%	152.6%	321	7,659
Outpatient	31614	5.69	1.92	2,348.41	556.02	195.6%	322.4%	902	6,140
Outpatient	6713	10.91	3.98	4,991.55	4,402.45	174.3%	13.4%	4,802	12,018
Outpatient	4162	7.64	2.86	5,977.86	2,842.82	166.8%	110.3%	3,981	8,774
Outpatient	1910	10.46	3.92	29,892.28	11,499.67	166.7%	159.9%	22,054	28,997
Outpatient	25872	2.33	0.87	1,740.06	755.14	166.2%	130.4%	517	6,263
Outpatient	22889	2.06	0.78	1,008.00	629.76	164.6%	60.1%	543	6,764
Professional	5730680757409679385	2.54	0.98	1,801.78	237.80	159.3%	657.7%	4,172	2,644
Outpatient	28370	1.68	0.65	2,768.34	1,226.79	159.1%	125.7%	920	3,268
Inpatient	6713	6.18	2.41	75,760.69	41,747.74	156.0%	81.5%	217	4,374
Outpatient	26527	2.24	0.89	784.50	812.71	152.8%	-3.5%	654	4,037
Outpatient	22714	2.1	0.84	468.16	502.72	151.3%	-6.9%	3,489	13,903
Professional	-4484641617725774088	2.42	0.96	2,005.04	173.32	151.0%	1056.8%	3,606	2,166
Outpatient	5764	1.65	0.67	734.59	693.37	147.9%	5.9%	2,773	9,601
Professional	-1938068338601073767	2.35	0.96	2,109.42	173.36	144.8%	1116.8%	3,406	1,962
Outpatient	25304	2.54	1.04	865.60	855.30	144.7%	1.2%	524	5,437
Outpatient	32398	2.4	0.99	1,515.51	998.28	143.5%	51.8%	706	2,882
Outpatient	21857	1.95	0.81	446.34	509.94	140.9%	-12.5%	4,392	16,321
Outpatient	26038	2.3	0.96	1,955.66	1,093.04	140.3%	78.9%	577	4,849
Outpatient	8981	1.12	0.47	297.02	472.62	139.9%	-37.2%	2,250	9,758
Outpatient	28836	2.15	0.91	5,781.50	1,750.43	135.7%	230.3%	940	2,488
Outpatient	9488	0.92	0.39	527.63	453.36	134.4%	16.4%	2,366	8,668
Inpatient	6558	6.14	2.62	103,457.00	44,508.25	134.2%	132.4%	615	2,824
Outpatient	28631	1.83	0.8	1,447.87	1,047.38	128.4%	38.2%	507	2,462
Outpatient	10266	1.12	0.49	300.72	415.61	126.7%	-27.6%	2,251	9,212
Outpatient	10661	1.12	0.49	299.36	413.14	126.6%	-27.5%	2,252	9,266
Professional	-4494441184012979231	2.67	1.18	1,972.49	222.12	126.6%	788.0%	1,240	697
Outpatient	5571	5.02	2.22	7,511.40	3,732.50	125.9%	101.2%	13,590	25,530
Outpatient	20379	6.52	2.89	3,840.66	1,564.44	125.5%	145.5%	909	3,843
Outpatient	8921	2.54	1.13	1,053.65	1,042.39	125.5%	1.1%	2,908	7,696
Professional	1873799107203658695	2.18	0.97	1,758.85	247.87	125.5%	609.6%	4,281	2,726
Professional	-3972253185612817143	3.61	1.61	969.68	196.86	124.5%	392.6%	1,028	13,119
Professional	3895627573700207023	2.27	1.01	1,688.11	233.76	124.4%	622.2%	1,486	932
Outpatient	8365	1.12	0.5	301.38	436.10	123.1%	-30.9%	2,245	8,625
Outpatient	25915	1.89	0.85	423.28	506.10	122.1%	-16.4%	1,860	5,468
Professional	-8952390584130972734	2.16	0.97	1,731.28	245.18	122.1%	606.1%	4,353	2,778
Outpatient	28399	1.63	0.74	2,531.85	1,141.38	120.4%	121.8%	614	2,790
Outpatient	22166	1.49	0.68	1,729.12	724.33	119.8%	138.7%	504	5,282
Outpatient	8922	1.96	0.89	5,799.71	1,899.05	119.7%	205.4%	9,340	15,868

Showing 50 of 19465 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_3_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_2_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: 81056 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
4,988,414,509,075,400,581	5571	5.93	1.3	9,143.47	8,035.47	356.2%	13.8%	1,854	7,719
-2,162,498,661,799,343,237	8981695266597257781	2.92	0.68	105.03	59.98	331.7%	75.1%	636	631
8,361,580,493,441,765,265	1910	7.23	1.79	21,087.94	2,015.35	303.5%	946.4%	4,790	6,524
4,988,414,509,075,400,581	31836	5.27	1.33	8,171.80	1,851.16	295.1%	341.4%	875	505
5,075,764,097,723,629,059	5473745582902726561	3.5	0.91	1,645.28	255.32	285.1%	544.4%	1,591	1,119
-558,178,834,365,778,978	5473745582902726561	3.5	0.91	1,645.28	255.32	285.1%	544.4%	1,591	1,119
-2,162,498,661,799,343,237	9732	2.42	0.65	9,986.69	2,837.10	274.2%	252.0%	5,983	5,143
4,988,414,509,075,400,581	28686	5.34	1.45	8,163.63	1,492.65	268.3%	446.9%	879	850
-2,162,498,661,799,343,237	3047	2.72	0.74	17,318.79	882.12	267.0%	1863.3%	5,431	787
-2,162,498,661,799,343,237	24599	2.53	0.71	1,297.40	534.61	253.9%	142.7%	2,222	838
-2,162,498,661,799,343,237	6713	12.56	3.56	9,417.44	14,850.17	252.9%	-36.6%	1,258	4,942
-3,776,001,016,975,145,508	6713	11.21	3.2	10,336.72	16,218.06	250.2%	-36.3%	1,087	4,940
5,075,764,097,723,629,059	-8612629535985160731	3	0.86	2,160.72	158.09	249.6%	1266.8%	1,110	649
-558,178,834,365,778,978	-8612629535985160731	3	0.86	2,160.72	158.09	249.6%	1266.8%	1,110	649
4,988,414,509,075,400,581	23549	5.3	1.53	8,180.79	1,530.04	245.9%	434.7%	876	644
-2,162,498,661,799,343,237	713	2.53	0.73	2,615.38	521.46	245.2%	401.6%	3,198	502
8,361,580,493,441,765,265	4023	4.84	1.44	20,665.44	15,038.59	236.4%	37.4%	7,685	3,173
-3,063,756,195,086,079,598	9842	5.41	1.61	22,178.21	28,064.91	236.1%	-21.0%	6,459	2,299
-2,162,498,661,799,343,237	3648	4.95	1.49	12,174.68	3,010.96	232.4%	304.3%	5,188	5,949
-7,940,170,945,989,807,859	6713	10.61	3.19	8,014.64	14,337.07	232.4%	-44.1%	1,734	5,544
5,075,764,097,723,629,059	2118729262235231070	3	0.9	2,214.92	163.29	232.2%	1256.4%	1,081	619
-558,178,834,365,778,978	2118729262235231070	3	0.9	2,214.92	163.29	232.2%	1256.4%	1,081	619
5,075,764,097,723,629,059	5651548239953530517	3.08	0.93	1,908.13	160.18	231.8%	1091.2%	1,274	768
-558,178,834,365,778,978	5651548239953530517	3.08	0.93	1,908.13	160.18	231.8%	1091.2%	1,274	768
-6,972,129,921,516,377,126	6354	4.1	1.25	12,740.21	847.69	229.4%	1402.9%	5,629	4,453
-2,162,498,661,799,343,237	4343	1.84	0.56	1,927.31	171.60	228.6%	1023.2%	3,791	574
-558,178,834,365,778,978	6902590622312144885	2.94	0.91	1,597.68	264.47	224.0%	504.1%	1,637	1,124
5,075,764,097,723,629,059	6902590622312144885	2.94	0.91	1,597.68	264.47	224.0%	504.1%	1,637	1,124
-2,162,498,661,799,343,237	31643	2.34	0.73	2,075.11	534.41	221.9%	288.3%	2,541	826
-4,541,460,228,936,150,437	5497648929473393777	3.65	1.15	2,065.01	210.61	218.7%	880.5%	1,188	667
-2,162,498,661,799,343,237	1317	3.4	1.07	14,880.79	6,043.47	217.1%	146.2%	8,298	6,661
-558,178,834,365,778,978	-4042515220149244083	3.03	0.96	2,108.36	174.90	214.9%	1105.4%	1,144	650
5,075,764,097,723,629,059	-4042515220149244083	3.03	0.96	2,108.36	174.90	214.9%	1105.4%	1,144	650
5,075,764,097,723,629,059	-7698044148695228947	3.3	1.06	2,010.30	187.16	211.7%	974.1%	1,211	689
-558,178,834,365,778,978	-7698044148695228947	3.3	1.06	2,010.30	187.16	211.7%	974.1%	1,211	689
4,281,965,180,796,802,413	5430	1.94	0.63	1,834.91	1,327.50	209.4%	38.2%	816	3,895
8,361,580,493,441,765,265	5430	1.94	0.63	1,834.91	1,327.50	209.4%	38.2%	816	3,895
4,988,414,509,075,400,581	29705	5.44	1.77	8,200.58	1,382.42	207.5%	493.2%	871	560
-2,162,498,661,799,343,237	9858	3.14	1.04	22,967.05	745.21	200.6%	2982.0%	4,379	1,712
3,973,810,847,438,814,798	1910	15.54	5.17	49,267.44	26,697.24	200.4%	84.5%	3,541	5,923
-558,178,834,365,778,978	8188998856188051201	2.72	0.91	1,786.33	230.38	200.1%	675.4%	1,405	912
5,075,764,097,723,629,059	8188998856188051201	2.72	0.91	1,786.33	230.38	200.1%	675.4%	1,405	912
-558,178,834,365,778,978	5497648929473393777	3.15	1.06	2,045.99	192.10	198.2%	965.1%	1,187	665
5,075,764,097,723,629,059	5497648929473393777	3.15	1.06	2,045.99	192.10	198.2%	965.1%	1,187	665
2,039,367,776,086,447,454	9858	3.15	1.06	21,056.67	758.33	197.3%	2676.7%	4,845	1,716
-2,162,498,661,799,343,237	1184	3.99	1.35	14,630.12	3,614.81	196.1%	304.7%	6,919	4,736
-7,995,682,717,897,123,039	23135	2.58	0.87	1,962.90	741.52	195.6%	164.7%	775	849
4,281,965,180,796,802,413	4023	4.04	1.37	17,651.95	13,736.66	194.6%	28.5%	7,685	3,173
-4,541,460,228,936,150,437	-8612629535985160731	3.4	1.16	2,000.78	212.77	192.3%	840.4%	1,228	707
-2,162,498,661,799,343,237	1910	14.35	4.92	45,972.91	25,573.92	191.3%	79.8%	3,772	5,797

Showing 50 of 81056 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_3_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_2_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: 87286 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
4,988,414,509,075,400,581	5571	Outpatient	5.93	1.21	9,143.47	1,918.72	390.9%	376.5%	1,854	6,043
-2,162,498,661,799,343,237	8981695266597257781	Professional	2.92	0.68	105.03	59.98	331.7%	75.1%	636	631
8,361,580,493,441,765,265	1910	Outpatient	7.24	1.78	20,787.22	1,771.01	305.9%	1073.7%	4,764	6,492
-2,162,498,661,799,343,237	1317	Outpatient	3.93	0.97	13,151.22	674.64	305.5%	1849.4%	6,582	4,945
-3,776,001,016,975,145,508	6076	Inpatient	7.11	1.76	121,350.48	33,163.45	305.0%	265.9%	91	1,457
8,361,580,493,441,765,265	4023	Outpatient	5.83	1.47	18,931.90	1,217.32	295.5%	1455.2%	5,969	1,457
4,988,414,509,075,400,581	31836	Outpatient	5.27	1.33	8,171.80	1,851.16	295.1%	341.4%	875	505
5,075,764,097,723,629,059	5473745582902726561	Professional	3.5	0.91	1,645.28	255.32	285.1%	544.4%	1,591	1,119
-558,178,834,365,778,978	5473745582902726561	Professional	3.5	0.91	1,645.28	255.32	285.1%	544.4%	1,591	1,119
-2,162,498,661,799,343,237	3047	Outpatient	2.81	0.74	6,335.15	882.12	279.0%	618.2%	3,870	787
-6,293,294,949,651,930,973	1964	Outpatient	5.28	1.41	18,050.29	17,269.36	273.4%	4.5%	4,340	1,550
-2,162,498,661,799,343,237	9732	Outpatient	2.41	0.65	9,038.46	2,837.10	272.0%	218.6%	5,881	5,143
4,988,414,509,075,400,581	28686	Outpatient	5.34	1.45	8,163.63	1,492.65	268.3%	446.9%	879	850
5,373,876,635,451,223,842	9933	Inpatient	4.5	1.23	74,006.75	25,664.83	265.0%	188.4%	780	152
-6,972,129,921,516,377,126	9933	Inpatient	4.5	1.23	74,006.75	25,664.83	265.0%	188.4%	780	152
-3,063,756,195,086,079,598	9842	Outpatient	6.82	1.9	16,468.32	605.77	259.6%	2618.6%	4,742	583
4,988,414,509,075,400,581	5581	Inpatient	3.74	1.04	56,340.90	20,052.27	258.1%	181.0%	1,716	1,623
-2,162,498,661,799,343,237	24599	Outpatient	2.53	0.71	1,297.40	534.61	253.9%	142.7%	2,222	838
-558,178,834,365,778,978	-8612629535985160731	Professional	3	0.86	2,160.72	158.09	249.6%	1266.8%	1,110	649
5,075,764,097,723,629,059	-8612629535985160731	Professional	3	0.86	2,160.72	158.09	249.6%	1266.8%	1,110	649
4,988,414,509,075,400,581	23549	Outpatient	5.3	1.53	8,180.79	1,530.04	245.9%	434.7%	876	644
-2,162,498,661,799,343,237	713	Outpatient	2.53	0.73	2,615.38	521.46	245.2%	401.6%	3,198	502
-2,162,498,661,799,343,237	3546	Inpatient	3.73	1.1	45,157.74	8,634.57	238.4%	423.0%	511	39
-2,162,498,661,799,343,237	3648	Outpatient	5.01	1.49	12,145.47	2,999.48	236.0%	304.9%	5,115	5,939
5,075,764,097,723,629,059	2118729262235231070	Professional	3	0.9	2,214.92	163.29	232.2%	1256.4%	1,081	619
-558,178,834,365,778,978	2118729262235231070	Professional	3	0.9	2,214.92	163.29	232.2%	1256.4%	1,081	619
5,075,764,097,723,629,059	5651548239953530517	Professional	3.08	0.93	1,908.13	160.18	231.8%	1091.2%	1,274	768
-558,178,834,365,778,978	5651548239953530517	Professional	3.08	0.93	1,908.13	160.18	231.8%	1091.2%	1,274	768
-6,972,129,921,516,377,126	6354	Outpatient	4.11	1.25	12,743.48	841.59	229.8%	1414.2%	5,620	4,450
-6,588,387,606,928,155,169	926	Outpatient	5.76	1.75	5,672.71	563.16	229.6%	907.3%	881	613
-2,162,498,661,799,343,237	4343	Outpatient	1.84	0.56	1,912.70	163.30	229.3%	1071.3%	3,784	573
4,281,965,180,796,802,413	4023	Outpatient	4.83	1.47	15,728.68	1,217.32	228.0%	1192.1%	5,969	1,457
-3,776,001,016,975,145,508	6713	Outpatient	11.54	3.52	6,661.05	5,043.44	227.4%	32.1%	1,025	3,465
8,361,580,493,441,765,265	9933	Inpatient	3.73	1.15	64,041.45	17,277.53	224.2%	270.7%	891	112
4,281,965,180,796,802,413	9933	Inpatient	3.73	1.15	64,041.45	17,277.53	224.2%	270.7%	891	112
-558,178,834,365,778,978	6902590622312144885	Professional	2.94	0.91	1,597.68	264.47	224.0%	504.1%	1,637	1,124
5,075,764,097,723,629,059	6902590622312144885	Professional	2.94	0.91	1,597.68	264.47	224.0%	504.1%	1,637	1,124
-2,162,498,661,799,343,237	6713	Outpatient	12.93	4.01	5,752.15	4,768.46	222.2%	20.6%	1,191	3,564
-2,162,498,661,799,343,237	31643	Outpatient	2.34	0.73	2,075.11	534.41	221.9%	288.3%	2,541	826
-3,776,001,016,975,145,508	1592	Inpatient	5.94	1.86	63,024.52	31,787.21	218.9%	98.3%	238	1,306
-4,541,460,228,936,150,437	5497648929473393777	Professional	3.65	1.15	2,065.01	210.61	218.7%	880.5%	1,188	667
-558,178,834,365,778,978	-4042515220149244083	Professional	3.03	0.96	2,108.36	174.90	214.9%	1105.4%	1,144	650
5,075,764,097,723,629,059	-4042515220149244083	Professional	3.03	0.96	2,108.36	174.90	214.9%	1105.4%	1,144	650
4,281,965,180,796,802,413	5430	Outpatient	1.95	0.62	1,220.80	1,215.86	214.0%	0.4%	789	3,870
8,361,580,493,441,765,265	5430	Outpatient	1.95	0.62	1,220.80	1,215.86	214.0%	0.4%	789	3,870
5,075,764,097,723,629,059	-7698044148695228947	Professional	3.3	1.06	2,010.30	187.16	211.7%	974.1%	1,211	689
-558,178,834,365,778,978	-7698044148695228947	Professional	3.3	1.06	2,010.30	187.16	211.7%	974.1%	1,211	689
-7,940,170,945,989,807,859	6713	Outpatient	10.79	3.47	5,218.29	4,138.84	211.2%	26.1%	1,666	4,069
-8,389,359,264,395,369,979	1106	Inpatient	5.23	1.7	95,741.70	30,213.26	208.6%	216.9%	1,605	1,708
-2,162,498,661,799,343,237	9858	Outpatient	3.21	1.04	6,249.50	745.21	207.7%	738.6%	2,751	1,712

Showing 50 of 87286 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_3_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_2_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: 59 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	1.25	2.29	2,635.24	1,902.59	-45.7%	38.5%	392,049	435,194
-8,506,106,384,867,378,430	1.48	2.42	3,072.36	10,154.42	-38.8%	-69.7%	662,036	179,939
-2,865,465,467,459,569,344	1.98	3.19	7,699.32	6,333.72	-38.0%	21.6%	515,344	458,754
3,376,187,909,306,580,799	1.93	2.75	3,732.38	2,674.20	-29.6%	39.6%	1,157,073	1,251,804
6,037,342,850,284,539,624	2.11	2.82	4,625.92	3,673.29	-25.3%	25.9%	1,265,211	1,369,266
-1,275,781,459,639,371,202	2.28	3.04	4,872.94	5,665.74	-25.0%	-14.0%	87,457	97,319
-6,588,387,606,928,155,169	2.3	3.05	14,559.00	13,056.75	-24.5%	11.5%	1,243,363	1,273,347
-7,225,588,104,346,557,715	2.34	3.09	14,639.59	13,335.45	-24.2%	9.8%	1,272,496	1,285,084
7,750,147,370,859,442,459	2.32	3.05	14,600.03	13,024.86	-24.0%	12.1%	1,254,375	1,312,136
-5,617,272,627,028,270,639	2.25	2.95	9,031.36	8,322.84	-23.8%	8.5%	725,342	729,747
5,605,126,640,932,710,930	2.44	1.99	34,651.41	26,129.35	22.9%	32.6%	235,887	323,065
-1,777,933,741,358,895,430	2.16	2.8	7,994.96	7,777.83	-22.8%	2.8%	1,136,619	1,181,081
-7,304,296,722,942,665,713	2.29	2.91	15,381.36	13,479.36	-21.4%	14.1%	1,302,742	1,386,853
1,164,822,038,571,945,223	1.86	2.36	5,482.86	5,511.14	-20.9%	-0.5%	436,296	453,555
1,481,239,756,837,705,768	2.33	2.88	6,466.17	14,028.74	-19.1%	-53.9%	832,000	360,457
1,391,056,274,517,336,075	2.46	3.03	9,545.47	8,425.45	-18.8%	13.3%	1,013,127	1,196,679
-4,944,049,946,733,699,762	3.38	3.94	12,784.94	10,646.49	-14.2%	20.1%	436,958	451,143
-137,228,703,119,221,502	2.03	1.8	6,245.11	6,063.85	12.8%	3.0%	884,401	851,903
-6,639,294,638,665,975,096	2.28	2.61	3,501.08	5,641.09	-12.3%	-37.9%	290,118	410,080
5,170,047,179,706,197,777	2.72	3.1	10,437.76	11,809.14	-12.2%	-11.6%	342,537	307,821
-7,160,850,762,653,242,095	2.6	2.92	9,974.14	11,416.00	-11.0%	-12.6%	328,694	290,216
1,579,516,467,629,270,577	2.67	2.97	9,785.28	9,743.09	-10.2%	0.4%	279,803	284,453
-7,995,682,717,897,123,039	2.67	2.92	15,060.60	12,147.39	-8.4%	24.0%	4,799,866	5,106,336
6,974,535,101,123,239,131	2.87	2.65	9,198.88	7,837.62	8.1%	17.4%	547,471	583,345
-1,016,849,053,752,795,150	2.81	3.05	9,662.00	10,758.76	-7.7%	-10.2%	387,166	397,259
-7,572,382,112,705,938,029	2.16	2.02	6,554.15	5,772.49	6.9%	13.5%	411,394	424,312
-4,445,518,905,666,343,797	2.16	2.03	18,337.46	14,297.72	6.3%	28.3%	183,602	185,851
7,616,836,552,481,822,175	2.4	2.26	9,807.31	8,258.19	6.1%	18.8%	4,041,670	4,222,895
1,017,413,876,722,652,424	2.26	2.13	8,718.79	7,315.35	6.0%	19.2%	3,717,730	3,855,691
5,177,932,399,303,345,046	1.94	1.83	6,733.20	5,857.74	5.7%	14.9%	1,500,206	1,664,157
-4,138,824,164,451,705,107	2.02	2.14	6,990.75	10,889.59	-5.6%	-35.8%	282,325	337,700
-3,711,121,367,896,011,833	2.42	2.31	7,533.38	6,730.67	5.0%	11.9%	2,580,155	2,831,652
-434,992,428,366,718,724	2.48	2.36	6,514.30	5,780.04	5.0%	12.7%	1,197,419	1,310,748
-4,061,814,287,107,819,804	2.27	2.38	3,648.81	4,269.90	-4.8%	-14.5%	133,500	179,439
-7,942,674,608,514,495,282	2.12	2.22	8,364.63	7,367.15	-4.3%	13.5%	1,046,664	1,053,365
-4,564,247,599,614,740,658	2.06	2.15	6,093.83	6,868.09	-4.2%	-11.3%	506,348	520,020
-1,205,712,746,611,102,743	3.18	3.32	7,752.59	10,407.50	-4.0%	-25.5%	483,298	538,619
5,781,103,079,387,890,636	1.73	1.66	4,310.34	3,582.48	3.7%	20.3%	1,214,529	1,203,453
-8,403,435,045,225,894,954	1.91	1.98	5,488.34	6,842.79	-3.6%	-19.8%	392,050	420,092
1,222,051,438,589,625,017	2.48	2.57	9,869.67	12,541.82	-3.6%	-21.3%	786,186	991,273
515,753,131,988,643,145	1.96	1.9	6,256.35	5,421.85	3.2%	15.4%	1,395,886	1,251,379
-3,111,440,664,317,846,556	1.85	1.8	7,846.13	7,072.99	2.9%	10.9%	225,701	240,106
-7,973,680,817,747,944,938	1.84	1.88	6,251.93	5,512.39	-2.4%	13.4%	993,550	1,002,368
-7,365,202,427,208,089,985	2.24	2.19	8,762.12	7,954.84	2.3%	10.1%	627,011	638,479
707,343,854,139,029,255	1.88	1.92	6,549.29	5,837.89	-2.2%	12.2%	1,026,084	1,029,075
5,645,818,248,035,806,861	1.92	1.88	7,826.61	9,226.36	1.8%	-15.2%	703,219	770,350
-4,630,321,108,541,466,571	1.77	1.74	7,225.40	8,393.66	1.6%	-13.9%	809,596	892,136
7,900,428,585,805,602,831	1.76	1.73	5,505.38	4,122.99	1.4%	33.5%	181,985	210,107
8,611,629,356,743,080,984	2.58	2.54	9,316.95	7,856.55	1.3%	18.6%	3,919,386	4,114,148
-6,543,868,296,982,650,519	2.57	2.54	12,281.18	10,987.11	1.3%	11.8%	2,464,266	2,619,326

Showing 50 of 59 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_3_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_2_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: 9 categories with significant ROID count changes

service_line	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
MSK	45,505,157	54,439,715	-8,934,558	-16.4%
Oncology	8,590,032	7,497,029	1,093,003	14.6%
ENT	11,334,770	13,233,544	-1,898,774	-14.3%
Dental	370,136	425,799	-55,663	-13.1%
Ophthalmology	12,089,164	13,802,176	-1,713,012	-12.4%
Gastrointestinal	18,608,157	21,014,376	-2,406,219	-11.5%
Transplant	65,016	72,833	-7,817	-10.7%
Reproductive	7,688,347	8,595,436	-907,089	-10.6%
Urology	8,651,530	9,671,884	-1,020,354	-10.5%

Click to see SQL

WITH
new AS (
    SELECT
        service_line,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_3_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_2_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: 2 categories with significant ROID count changes

provider_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Laboratory	3,535,461	2,158,493	1,376,968	63.8%
ASC	15,194,451	25,049,884	-9,855,433	-39.3%

Click to see SQL

WITH
new AS (
    SELECT
        provider_type,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_3_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_2_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: 12 categories with significant ROID count changes

state	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
SD	1,379,892	1,787,289	-407,397	-22.8%
ND	1,080,809	1,347,092	-266,283	-19.8%
MT	1,643,642	2,009,110	-365,468	-18.2%
AK	504,206	609,886	-105,680	-17.3%
VT	423,941	508,507	-84,566	-16.6%
WY	695,031	832,045	-137,014	-16.5%
DE	705,421	825,496	-120,075	-14.5%
IA	4,763,064	5,491,927	-728,863	-13.3%
UT	1,763,309	2,028,198	-264,889	-13.1%
NE	2,954,837	3,317,381	-362,544	-10.9%
KS	4,748,811	5,291,078	-542,267	-10.2%
MN	6,927,143	7,702,954	-775,811	-10.1%

Click to see SQL

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

payer_network_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Blue Cross Blue Shield of Maryland & DC (CareFirst) HMO	662,036	179,939	482,097	267.9%
Regence Blue Cross Blue Shield OR PPO	832,000	360,457	471,543	130.8%
Blue Cross Blue Shield of Montana Blue Preferred PPO	290,118	410,080	-119,962	-29.3%
EmblemHealth GHI / Anthem CBP	235,887	323,065	-87,178	-27.0%
Avera Health Plans Avera Health	133,500	179,439	-45,939	-25.6%
Sanford Health Plan PPO	786,186	991,273	-205,087	-20.7%
Anthem NH HMO	335,159	286,309	48,850	17.1%
MetroPlus Health Gold	700,725	838,418	-137,693	-16.4%
Wellmark Blue Cross Blue Shield SD PPO	282,325	337,700	-55,375	-16.4%
Cigna MO HMO	379,525	451,022	-71,497	-15.9%
WPS PPO	1,013,127	1,196,679	-183,552	-15.3%
Premera Blue Cross Alaska Heritage Network	175,626	207,106	-31,480	-15.2%
Anthem NH OPEN ACCESS	364,781	317,081	47,700	15.0%
Kaiser Permanente Kaiser Health Plan (HI)	181,985	210,107	-28,122	-13.4%
Anthem ME HMO	328,694	290,216	38,478	13.3%
Moda Health Connexus	515,344	458,754	56,590	12.3%
Blue Cross Blue Shield of Tennessee PPO	1,395,886	1,251,379	144,507	11.5%
Anthem ME Blue Choice PPO	342,537	307,821	34,716	11.3%
Blue Cross Blue Shield of North Dakota PPO	321,165	361,451	-40,286	-11.1%
Cigna VA HMO	496,551	556,617	-60,066	-10.8%
UnitedHealthcare HMO	55,175,818	61,644,316	-6,468,498	-10.5%
Anthem GA OPEN ACCESS	1,738,523	1,575,920	162,603	10.3%
Kaiser Permanente Kaiser Health Plan (CO)	483,298	538,619	-55,321	-10.3%
Blue Cross Blue Shield of Vermont PPO	87,457	97,319	-9,862	-10.1%
UnitedHealthcare Choice Plus	57,213,715	63,623,745	-6,410,030	-10.1%

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_3_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_2_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
791	MotivHealth Insurance Company	MotivNet	payer	32,594	0
317	Tufts Health Plan	HMO	payer	1,092	0
317	Tufts Health Plan	PPO MA	payer	258	0
791	MotivHealth Insurance Company	MotivNet	imputation	101	0
397	Blue Cross Blue Shield of Rhode Island	PPO	imputation	139	50
458	Blue Cross Blue Shield of North Dakota	PPO	imputation	3,119	89
628	Premera Blue Cross	Alaska Heritage Network	imputation	556	534
42	Anthem	NH HMO	imputation	570	586
53	Blue Cross Blue Shield of Oklahoma	Blue Preferred PPO	imputation	668	625
462	Blue Cross Blue Shield of Vermont	PPO	imputation	4,010	1,048

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_3_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_2_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: 131 categories with significant ROID count changes

provider_type	health_system_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Hospital	PAM Health	127,423	228,674	-101,251	-44.3%
Hospital	CHRISTUS Health Shreveport - Bossier Health System	138,141	96,559	41,582	43.1%
ASC	Baylor Scott & White Health	74,069	119,333	-45,264	-37.9%
ASC	AMSURG	362,557	583,004	-220,447	-37.8%
Hospital	TMC Health	127,509	93,338	34,171	36.6%
ASC	Texas Health Resources	66,699	104,889	-38,190	-36.4%
ASC	Surgery Partners	439,584	679,266	-239,682	-35.3%
Hospital	Avera Health	643,775	969,890	-326,115	-33.6%
ASC	Virtua Health	94,971	141,747	-46,776	-33.0%
ASC	United Surgical Partners International	2,027,638	3,004,028	-976,390	-32.5%
ASC	SCA Health	817,702	1,190,790	-373,088	-31.3%
ASC	Ambulatory Surgical Centers of America	71,434	102,754	-31,320	-30.5%
Hospital	Ernest Health	101,950	146,508	-44,558	-30.4%
ASC	Inova	82,288	117,800	-35,512	-30.1%
ASC	Memorial Hermann Health System	88,405	126,512	-38,107	-30.1%
Hospital	UnityPoint Health Fort Dodge	108,126	153,281	-45,155	-29.5%
Hospital	OakBend Medical Center Health System	72,940	103,041	-30,101	-29.2%
Hospital	CHI St Alexius Health Bismarck	131,340	184,957	-53,617	-29.0%
Hospital	Logan Health	94,784	133,093	-38,309	-28.8%
Physician Group	Franciscan Missionaries of Our Lady Health System	98,566	138,134	-39,568	-28.6%
Hospital	FirstHealth of the Carolinas	100,777	140,080	-39,303	-28.1%
Physician Group	Albany MED Health System	212,411	294,603	-82,192	-27.9%
Physician Group	Beth Israel Lahey Health	119,960	166,294	-46,334	-27.9%
Hospital	Encompass Health Corporation	820,725	1,116,644	-295,919	-26.5%
Physician Group	John Muir Health	76,979	104,046	-27,067	-26.0%
Physician Group	BayCare Health System	77,297	104,235	-26,938	-25.8%
ASC	Dignity Health	118,103	159,183	-41,080	-25.8%
Physician Group	Aurora Health Care	134,939	180,964	-46,025	-25.4%
Physician Group	Kaleida Health	96,592	129,374	-32,782	-25.3%
Hospital	Billings Clinic Health System	233,997	312,910	-78,913	-25.2%
Hospital	MercyOne	651,406	869,378	-217,972	-25.1%
Hospital	Great Plains Health Alliance	557,861	727,103	-169,242	-23.3%
Hospital	Mayo Clinic	165,754	134,588	31,166	23.2%
Hospital	CHI Saint Joseph Health	236,230	192,136	44,094	22.9%
Physician Group	Aspirus Health	237,511	307,561	-70,050	-22.8%
Hospital	HCA North Carolina Division	180,535	233,119	-52,584	-22.6%
Physician Group	UMass Memorial Health Care	97,318	123,643	-26,325	-21.3%
ASC	HCA Surgery Center Division	679,028	860,938	-181,910	-21.1%
Hospital	Sanford Health	715,011	898,740	-183,729	-20.4%
Physician Group	Sentara Health	172,832	215,924	-43,092	-20.0%
Physician Group	Wellstar MCG Health	82,530	102,852	-20,322	-19.8%
Physician Group	Covenant Health	92,438	114,357	-21,919	-19.2%
Hospital	One Brooklyn Health System	229,437	283,799	-54,362	-19.2%
Physician Group	Providence Health & Services - Oregon and Southwest Washington	196,210	164,729	31,481	19.1%
Hospital	Northwest Texas Healthcare System	101,458	125,174	-23,716	-18.9%
Hospital	CentraCare Health System	254,864	313,673	-58,809	-18.7%
Hospital	Shannon Health	157,948	133,300	24,648	18.5%
Physician Group	Duke LifePoint Healthcare	102,239	124,731	-22,492	-18.0%
Physician Group	Salem Health	104,141	88,410	15,731	17.8%
Hospital	HCA Continental Division - Wesley Healthcare	93,184	113,255	-20,071	-17.7%

Showing 50 of 131 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_3_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_2_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: 3 categories with significant ROID count changes

canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
3	181,296,149	83,808,134	97,488,015	116.3%
2	42,035,969	22,264,856	19,771,113	88.8%
4	127,692,004	268,722,492	-141,030,488	-52.5%

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_3_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_2_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: 12 categories with significant ROID count changes

provider_type	canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
ASC	2	174,837	329,312	-154,475	-46.9%
ASC	4	14,781,255	24,487,408	-9,706,153	-39.6%
Hospital	4	108,442,836	128,476,272	-20,033,436	-15.6%
Hospital	3	58,842,617	51,281,223	7,561,394	14.7%
Laboratory	5	1,725,375	952,334	773,041	81.2%
Laboratory	4	223,078	141,848	81,230	57.3%
Laboratory	3	1,102,616	733,853	368,763	50.3%
Laboratory	2	484,392	330,458	153,934	46.6%
Physician Group	3	121,112,197	31,559,540	89,552,657	283.8%
Physician Group	2	27,164,668	7,902,222	19,262,446	243.8%
Physician Group	4	1,265,107	112,588,567	-111,323,460	-98.9%
Physician Group	5	929,642	546,220	383,422	70.2%

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_3_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_2_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: 2 categories with significant ROID count changes

canonical_rate_class	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Impute	62,027,994	79,160,748	-17,132,754	-21.6%
Transform	24,960,532	30,956,297	-5,995,765	-19.4%

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_3_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_2_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: 1 categories with significant ROID count changes

canonical_rate_source	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
imputation	62,027,994	79,160,748	-17,132,754	-21.6%

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_3_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_2_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: 2 categories with significant ROID count changes

canonical_contract_methodology	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Per Diem	4,148,724	3,431,956	716,768	20.9%
Case Rate	85,591,208	104,246,229	-18,655,021	-17.9%

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_3_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_2_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: 5 categories with significant ROID count changes

canonical_crosswalk_method	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Crosswalked From CSTM-ALL to APR-DRG	726,342	939,160	-212,818	-22.7%
Crosswalked From NDC to HCPCS	841,012	1,044,354	-203,342	-19.5%
Crosswalked From CSTM-ALL to MS-DRG	611,444	744,246	-132,802	-17.8%
Crosswalked From RC-FAMILY to MS-DRG	752,632	660,900	91,732	13.9%
Crosswalked From RC-FAMILY to APR-DRG	964,179	859,290	104,889	12.2%

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_3_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_2_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: 2 categories with significant ROID count changes

canonical_gross_charge_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
mrf_gross_charge_cbsa_median	18,310,739	32,430,284	-14,119,545	-43.5%
komodo_gross_charge_state_median	10,893,878	9,514,742	1,379,136	14.5%

Click to see SQL

WITH
new AS (
    SELECT
        canonical_gross_charge_type,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_3_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_2_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

---