v2_4_0 → v2_4_1

Generated: 2026-03-11 19:29:35.
Filter: canonical_rate_score > 1.

---

1. Summary Statistics

Overview

• Total ROIDs: 423,563,165 → 642,842,847 (+219,279,682, +51.8%)

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
Laboratory	Professional	12,774,428	11,292,016	171	167	3,711	3,710	916	916
Hospital	Inpatient	59,364,628	44,262,161	177	173	5,968	5,630	1,711	1,711
Physician Group	Professional	296,243,211	168,018,318	177	174	21,740	21,113	3,830	3,790
ASC	Outpatient	21,462,464	14,469,409	145	137	5,578	5,533	3,154	3,154
Hospital	Outpatient	248,960,622	182,556,632	180	176	5,999	5,708	7,512	7,512
Imaging Center	Professional	4,037,494	2,964,629	163	159	2,930	2,898	171	171

Click to see SQL

WITH
new AS (
    SELECT
        provider_type,
        bill_type,
        COUNT(DISTINCT roid) AS distinct_roids,
        COUNT(DISTINCT payer_id) AS distinct_payers,
        COUNT(DISTINCT network_id) AS distinct_networks,
        COUNT(DISTINCT provider_id) AS distinct_providers,
        COUNT(DISTINCT billing_code) AS distinct_billing_codes
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY 1,2
),
old AS (
    SELECT
        CASE
            WHEN provider_type LIKE '%Hospital%' THEN 'Hospital'
            ELSE provider_type
        END AS provider_type,
        bill_type,
        COUNT(DISTINCT roid) AS distinct_roids,
        COUNT(DISTINCT payer_id) AS distinct_payers,
        COUNT(DISTINCT network_id) AS distinct_networks,
        COUNT(DISTINCT provider_id) AS distinct_providers,
        COUNT(DISTINCT billing_code) AS distinct_billing_codes
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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: 174 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT provider_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
),
old AS (
    SELECT DISTINCT provider_id, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
)
SELECT
    DISTINCT
    provider_id
FROM new
FULL OUTER JOIN old USING (provider_id)
WHERE new.source IS NULL AND old.source IS NOT NULL

Payer Dropoffs

Count: 0 dropped

No dropoffs detected.

Click to see SQL

WITH
new AS (
    SELECT DISTINCT payer_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
),
old AS (
    SELECT DISTINCT payer_id, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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: 2 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT network_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
),
old AS (
    SELECT DISTINCT network_id, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
)
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: 7269 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT provider_id, network_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
),
old AS (
    SELECT DISTINCT provider_id, network_id, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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: 0 dropped

No dropoffs detected.

Click to see SQL

WITH
new AS (
    SELECT DISTINCT bill_type, billing_code, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
),
old AS (
    SELECT DISTINCT bill_type, billing_code, 'old' as source
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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: 19139 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	-497507422268688027	3.14	0.93	1,261.17	687.54	238.0%	83.4%	35,761	4,407
Professional	72977	3.29	1.1	417.00	139.08	200.3%	199.8%	925	625
Professional	3197309463706723792	2.77	0.94	1,529.77	202.51	194.9%	655.4%	19,361	5,384
Professional	2348343882328603116	2.29	0.8	806.43	156.12	185.4%	416.5%	43,061	1,700
Professional	-6087829591143767549	2.87	1.05	1,368.71	382.16	173.8%	258.1%	28,114	4,697
Professional	6706650634653838980	2.8	1.05	968.88	253.81	166.3%	281.7%	28,127	3,706
Professional	8452609278095661910	1.79	0.68	718.73	143.08	163.4%	402.3%	36,757	3,316
Professional	-3245206910240182907	2.01	0.8	700.53	268.65	151.4%	160.8%	34,867	2,773
Professional	-1668630353830312289	3.07	1.22	1,383.16	402.70	151.0%	243.5%	54,665	1,029
Inpatient	9042	5.04	2.07	124,484.89	61,298.12	143.1%	103.1%	1,656	1,710
Outpatient	9229	3.98	1.64	4,112.55	3,529.52	142.2%	16.5%	3,285	652
Professional	-6135834785170660572	2.14	0.89	1,010.34	1,203.41	140.3%	-16.0%	36,304	4,429
Professional	-7981684897601570688	3.02	1.27	923.55	296.17	138.9%	211.8%	25,570	786
Professional	-481835967384438876	2.07	0.87	760.57	257.80	137.7%	195.0%	38,373	6,559
Professional	2181079439032466755	2.42	1.02	974.94	218.22	137.1%	346.8%	2,949	1,218
Professional	-3148982206069435370	2.81	1.2	1,162.85	1,135.64	134.9%	2.4%	39,771	3,146
Professional	269308854051602210	1.96	0.85	916.82	158.17	131.4%	479.6%	36,077	2,432
Professional	-1585322645111161870	2.13	0.92	668.15	172.24	131.1%	287.9%	28,296	2,268
Professional	-9042619131261616552	1.92	0.84	743.28	161.73	130.1%	359.6%	28,128	3,180
Professional	-4835358952042409261	2.09	0.92	739.32	386.50	127.4%	91.3%	25,503	1,628
Professional	-7605361859288417447	3.21	1.44	1,175.66	1,946.57	124.0%	-39.6%	20,574	2,609
Professional	-871232198895717967	2.2	0.99	712.40	132.91	122.3%	436.0%	32,391	1,158
Professional	5013976330844736771	2.84	1.29	956.24	2,583.72	120.8%	-63.0%	29,421	943
Professional	4419669643179954538	1.86	0.84	638.00	139.55	120.4%	357.2%	24,300	3,029
Professional	-7439582313149223993	2.46	1.13	824.99	207.48	117.5%	297.6%	42,033	3,074
Professional	-7259655293849955330	1.78	0.82	644.38	187.60	116.7%	243.5%	29,505	1,434
Professional	77431168054907305	1.97	0.91	804.18	780.75	115.6%	3.0%	32,301	3,634
Outpatient	26786	7.43	3.45	2,026.71	791.63	115.2%	156.0%	1,556	714
Professional	5494568643463806400	1.81	0.86	632.98	161.70	110.2%	291.5%	31,450	1,772
Inpatient	5969	2.15	1.02	26,918.90	16,458.15	109.8%	63.6%	308	18
Professional	-4820926513593666878	1.95	0.93	758.43	1,134.59	109.5%	-33.2%	39,945	4,680
Professional	-6400922911578805274	1.89	0.91	691.99	2,502.50	108.2%	-72.3%	16,076	891
Professional	-7246600252420665856	2.09	1.01	761.53	207.47	107.6%	267.1%	27,795	1,516
Professional	847633022085053020	1.73	0.83	649.16	2,701.83	107.0%	-76.0%	27,944	892
Professional	7990536601939131671	1.83	0.89	120.70	115.96	105.0%	4.1%	502	2,264
Professional	-5886876689385235540	2.13	1.04	688.31	128.94	104.9%	433.8%	23,447	2,639
Professional	-2988256152933249565	1.59	0.78	795.60	154.29	104.6%	415.7%	14,208	2,774
Professional	3727336487756877175	1.82	0.89	121.06	128.44	104.5%	-5.7%	508	2,361
Professional	5786437384847345079	1.81	0.89	119.63	115.92	103.2%	3.2%	516	2,266
Professional	-5935998671356214154	2.1	1.05	424.93	142.59	99.9%	198.0%	24,450	1,495
Professional	-6010649751900700952	2.24	1.12	778.33	3,634.57	99.8%	-78.6%	51,582	2,093
Professional	-3682256659151263117	1.92	0.96	752.89	153.36	99.4%	390.9%	23,340	3,485
Professional	1286100398742578208	1.92	0.97	745.16	249.42	98.3%	198.8%	37,128	503
Professional	-9021429009303548093	2.09	1.06	997.79	630.28	97.4%	58.3%	41,635	5,128
Professional	-7968734964571683480	1.67	0.85	677.80	163.50	96.3%	314.6%	20,620	2,237
Professional	6744056187255001629	2.24	1.14	1,067.70	4,185.63	96.2%	-74.5%	16,330	1,656
Professional	-3765834510674975424	1.8	0.93	935.25	922.83	94.0%	1.3%	48,080	8,546
Outpatient	5243	6.16	3.24	1,258.45	7,717.44	90.5%	-83.7%	892	11,934
Professional	6356772039186191744	2.45	1.29	869.97	177.35	89.5%	390.6%	34,420	3,926
Professional	1476457437666902992	1.74	0.92	628.27	217.60	89.4%	188.7%	31,303	2,197

Showing 50 of 19139 total rows

Click to see SQL

WITH
new AS (
    SELECT
        bill_type,
        provider_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY bill_type, provider_id
),
old AS (
    SELECT
        bill_type,
        provider_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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: 71558 entities with significant rate changes

network_id	provider_id	new_avg_pct_of_medicare_rate	old_avg_pct_of_medicare_rate	new_avg_canonical_rate	old_avg_canonical_rate	medicare_rate_pct_change	canonical_rate_pct_change	new_roid_count	old_roid_count
311,448,953,162,816,591	527	inf	inf	4,530.17	3,953.33		14.6%	6,219	6,249
8,361,580,493,441,765,265	-497507422268688027	4.04	0.74	840.73	150.20	444.3%	459.7%	3,266	884
4,281,965,180,796,802,413	-497507422268688027	3.93	0.74	735.19	150.20	428.6%	389.5%	3,070	884
-3,776,001,016,975,145,508	-1519822265826934383	4.02	0.77	2,243.78	157.56	420.8%	1324.1%	573	1,512
8,361,580,493,441,765,265	-6135834785170660572	2.85	0.62	1,020.87	149.88	358.0%	581.1%	4,565	697
4,281,965,180,796,802,413	-6135834785170660572	2.8	0.62	942.87	149.88	349.9%	529.1%	4,224	697
-3,776,001,016,975,145,508	8452609278095661910	3.29	0.76	1,118.69	211.20	333.4%	429.7%	2,615	698
8,361,580,493,441,765,265	-6087829591143767549	3.15	0.73	672.80	266.53	329.4%	152.4%	3,020	785
4,281,965,180,796,802,413	-6087829591143767549	3.11	0.73	609.57	266.53	323.8%	128.7%	2,940	785
8,361,580,493,441,765,265	3197309463706723792	2.86	0.69	508.72	143.76	317.0%	253.9%	2,197	877
4,281,965,180,796,802,413	3197309463706723792	2.75	0.69	439.10	143.76	300.9%	205.4%	2,112	877
-3,776,001,016,975,145,508	-9042619131261616552	3.74	0.99	1,279.13	245.03	278.3%	422.0%	2,492	508
-5,230,720,519,108,079,751	4023	5.04	1.46	20,510.04	26,912.89	245.5%	-23.8%	7,824	1,710
-3,776,001,016,975,145,508	5910532371753807288	4.11	1.2	502.97	288.35	242.8%	74.4%	1,247	1,278
-3,776,001,016,975,145,508	-9208786563063963982	4.11	1.2	502.57	288.55	242.8%	74.2%	1,248	1,277
-3,776,001,016,975,145,508	1286100398742578208	3.17	0.97	1,044.50	249.42	226.9%	318.8%	2,123	503
8,361,580,493,441,765,265	7063415233901130033	2.18	0.67	583.02	262.25	225.0%	122.3%	4,485	780
-3,776,001,016,975,145,508	-3868263910251141097	4.87	1.52	1,727.29	1,425.24	221.4%	21.2%	845	909
-3,776,001,016,975,145,508	-7146320621286215096	3.72	1.16	2,038.21	471.23	220.3%	332.5%	656	758
4,281,965,180,796,802,413	6706650634653838980	2.81	0.88	846.15	265.33	217.3%	218.9%	4,501	1,388
8,361,580,493,441,765,265	6706650634653838980	2.8	0.88	839.83	265.33	216.1%	216.5%	4,514	1,388
-3,776,001,016,975,145,508	5348453081036961437	3.93	1.27	252.88	164.72	208.6%	53.5%	617	575
-2,162,498,661,799,343,237	-4985828612566508868	3.17	1.03	1,625.67	270.98	208.5%	499.9%	3,071	773
-2,162,498,661,799,343,237	-5529235617482564817	3.14	1.03	922.29	270.98	205.2%	240.3%	949	773
-3,776,001,016,975,145,508	8745986158877503234	4.17	1.37	1,470.02	1,455.11	205.1%	1.0%	1,306	948
7,616,836,552,481,822,175	-497507422268688027	2.95	0.98	1,487.65	2,661.66	201.7%	-44.1%	4,912	885
-3,776,001,016,975,145,508	-8086702650980756402	3.72	1.25	2,059.90	1,349.54	199.0%	52.6%	653	1,020
4,281,965,180,796,802,413	2348343882328603116	2.4	0.8	688.18	156.12	198.9%	340.8%	4,915	850
8,361,580,493,441,765,265	2348343882328603116	2.4	0.8	688.18	156.12	198.9%	340.8%	4,915	850
-3,776,001,016,975,145,508	768677613229314287	3.87	1.3	241.73	161.92	197.3%	49.3%	608	644
-3,776,001,016,975,145,508	1067587447723132204	3.82	1.29	220.72	868.84	197.1%	-74.6%	720	924
-3,776,001,016,975,145,508	3015510094204161181	3.82	1.29	220.72	868.84	197.1%	-74.6%	720	924
-3,776,001,016,975,145,508	7932838483455832294	3.43	1.16	400.62	268.10	196.7%	49.4%	1,462	1,459
8,361,580,493,441,765,265	-4835358952042409261	2.69	0.92	652.22	386.50	192.8%	68.7%	4,665	814
4,281,965,180,796,802,413	-9021429009303548093	2.21	0.75	716.11	151.56	192.4%	372.5%	4,832	884
8,361,580,493,441,765,265	-9021429009303548093	2.21	0.75	716.11	151.56	192.4%	372.5%	4,832	884
-3,776,001,016,975,145,508	1448230468311069262	2.88	0.99	1,050.58	250.28	190.9%	319.8%	1,881	501
-3,776,001,016,975,145,508	5671012178851870288	4.47	1.54	417.54	264.89	189.8%	57.6%	1,006	617
-3,776,001,016,975,145,508	9119600455817837599	3.23	1.12	357.42	604.44	188.8%	-40.9%	1,448	1,860
-3,776,001,016,975,145,508	340530510858151991	3.18	1.1	975.75	824.34	188.5%	18.4%	1,765	2,106
-3,776,001,016,975,145,508	3892441238895982114	3.18	1.1	975.75	825.74	188.2%	18.2%	1,765	2,106
-3,776,001,016,975,145,508	-1967423625411999638	3.92	1.37	1,282.64	1,455.11	186.2%	-11.9%	1,326	948
-3,776,001,016,975,145,508	4376411467288635212	3.97	1.39	1,288.70	1,451.13	184.5%	-11.2%	1,325	951
-3,776,001,016,975,145,508	-1129251442614503651	3.41	1.21	1,897.74	315.66	182.9%	501.2%	726	795
-3,776,001,016,975,145,508	5046591772473192223	4.19	1.48	454.30	334.61	182.4%	35.8%	1,107	1,131
4,281,965,180,796,802,413	7063415233901130033	2.04	0.73	545.22	267.29	181.0%	104.0%	4,509	765
8,361,580,493,441,765,265	8525801659323831681	1.99	0.71	717.32	146.31	180.6%	390.3%	4,723	882
-3,776,001,016,975,145,508	6246553284827218110	3.62	1.3	466.90	440.75	177.6%	5.9%	1,183	818
-2,162,498,661,799,343,237	-5587964766527951545	2.39	0.86	688.05	202.20	176.3%	240.3%	1,128	1,370
-2,162,498,661,799,343,237	-1994395531279373039	2.39	0.86	686.92	202.20	176.0%	239.7%	1,130	1,370

Showing 50 of 71558 total rows

Click to see SQL

WITH
new AS (
    SELECT
        network_id,
        provider_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY network_id, provider_id
),
old AS (
    SELECT
        network_id,
        provider_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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: 73135 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
8,361,580,493,441,765,265	-497507422268688027	Professional	4.04	0.74	840.73	150.20	444.3%	459.7%	3,266	884
4,281,965,180,796,802,413	-497507422268688027	Professional	3.93	0.74	735.19	150.20	428.6%	389.5%	3,070	884
-3,776,001,016,975,145,508	-1519822265826934383	Professional	4.02	0.77	2,243.78	157.56	420.8%	1324.1%	573	1,512
8,361,580,493,441,765,265	-6135834785170660572	Professional	2.85	0.62	1,020.87	149.88	358.0%	581.1%	4,565	697
4,281,965,180,796,802,413	-6135834785170660572	Professional	2.8	0.62	942.87	149.88	349.9%	529.1%	4,224	697
-3,776,001,016,975,145,508	8452609278095661910	Professional	3.29	0.76	1,118.69	211.20	333.4%	429.7%	2,615	698
8,361,580,493,441,765,265	-6087829591143767549	Professional	3.15	0.73	672.80	266.53	329.4%	152.4%	3,020	785
4,281,965,180,796,802,413	-6087829591143767549	Professional	3.11	0.73	609.57	266.53	323.8%	128.7%	2,940	785
8,361,580,493,441,765,265	3197309463706723792	Professional	2.86	0.69	508.72	143.76	317.0%	253.9%	2,197	877
4,281,965,180,796,802,413	3197309463706723792	Professional	2.75	0.69	439.10	143.76	300.9%	205.4%	2,112	877
-3,776,001,016,975,145,508	-9042619131261616552	Professional	3.74	0.99	1,279.13	245.03	278.3%	422.0%	2,492	508
-3,776,001,016,975,145,508	5910532371753807288	Professional	4.11	1.2	502.97	288.35	242.8%	74.4%	1,247	1,278
-3,776,001,016,975,145,508	-9208786563063963982	Professional	4.11	1.2	502.57	288.55	242.8%	74.2%	1,248	1,277
-3,776,001,016,975,145,508	1286100398742578208	Professional	3.17	0.97	1,044.50	249.42	226.9%	318.8%	2,123	503
8,361,580,493,441,765,265	7063415233901130033	Professional	2.18	0.67	583.02	262.25	225.0%	122.3%	4,485	780
-3,776,001,016,975,145,508	-3868263910251141097	Professional	4.87	1.52	1,727.29	1,425.24	221.4%	21.2%	845	909
-3,776,001,016,975,145,508	-7146320621286215096	Professional	3.72	1.16	2,038.21	471.23	220.3%	332.5%	656	758
4,281,965,180,796,802,413	6706650634653838980	Professional	2.81	0.88	846.15	265.33	217.3%	218.9%	4,501	1,388
-6,972,129,921,516,377,126	6272	Inpatient	8.39	2.65	100,368.02	61,722.92	217.1%	62.6%	16	349
5,373,876,635,451,223,842	6272	Inpatient	8.39	2.65	100,368.02	61,722.92	217.1%	62.6%	16	349
8,361,580,493,441,765,265	6706650634653838980	Professional	2.8	0.88	839.83	265.33	216.1%	216.5%	4,514	1,388
-3,776,001,016,975,145,508	5348453081036961437	Professional	3.93	1.27	252.88	164.72	208.6%	53.5%	617	575
-2,162,498,661,799,343,237	-4985828612566508868	Professional	3.17	1.03	1,625.67	270.98	208.5%	499.9%	3,071	773
-2,162,498,661,799,343,237	-5529235617482564817	Professional	3.14	1.03	922.29	270.98	205.2%	240.3%	949	773
-3,776,001,016,975,145,508	8745986158877503234	Professional	4.17	1.37	1,470.02	1,455.11	205.1%	1.0%	1,306	948
7,616,836,552,481,822,175	-497507422268688027	Professional	2.95	0.98	1,487.65	2,661.66	201.7%	-44.1%	4,912	885
-3,776,001,016,975,145,508	-8086702650980756402	Professional	3.72	1.25	2,059.90	1,349.54	199.0%	52.6%	653	1,020
4,281,965,180,796,802,413	2348343882328603116	Professional	2.4	0.8	688.18	156.12	198.9%	340.8%	4,915	850
8,361,580,493,441,765,265	2348343882328603116	Professional	2.4	0.8	688.18	156.12	198.9%	340.8%	4,915	850
-3,776,001,016,975,145,508	768677613229314287	Professional	3.87	1.3	241.73	161.92	197.3%	49.3%	608	644
-3,776,001,016,975,145,508	3015510094204161181	Professional	3.82	1.29	220.72	868.84	197.1%	-74.6%	720	924
-3,776,001,016,975,145,508	1067587447723132204	Professional	3.82	1.29	220.72	868.84	197.1%	-74.6%	720	924
-3,776,001,016,975,145,508	7932838483455832294	Professional	3.43	1.16	400.62	268.10	196.7%	49.4%	1,462	1,459
8,361,580,493,441,765,265	-4835358952042409261	Professional	2.69	0.92	652.22	386.50	192.8%	68.7%	4,665	814
8,361,580,493,441,765,265	-9021429009303548093	Professional	2.21	0.75	716.11	151.56	192.4%	372.5%	4,832	884
4,281,965,180,796,802,413	-9021429009303548093	Professional	2.21	0.75	716.11	151.56	192.4%	372.5%	4,832	884
-3,776,001,016,975,145,508	1448230468311069262	Professional	2.88	0.99	1,050.58	250.28	190.9%	319.8%	1,881	501
-3,776,001,016,975,145,508	5671012178851870288	Professional	4.47	1.54	417.54	264.89	189.8%	57.6%	1,006	617
-3,776,001,016,975,145,508	9119600455817837599	Professional	3.23	1.12	357.42	604.44	188.8%	-40.9%	1,448	1,860
-3,776,001,016,975,145,508	340530510858151991	Professional	3.18	1.1	975.75	824.34	188.5%	18.4%	1,765	2,106
-3,776,001,016,975,145,508	3892441238895982114	Professional	3.18	1.1	975.75	825.74	188.2%	18.2%	1,765	2,106
-3,776,001,016,975,145,508	-1967423625411999638	Professional	3.92	1.37	1,282.64	1,455.11	186.2%	-11.9%	1,326	948
-3,776,001,016,975,145,508	4376411467288635212	Professional	3.97	1.39	1,288.70	1,451.13	184.5%	-11.2%	1,325	951
4,301,155,135,282,901,848	2909	Inpatient	3.18	1.12	43,481.08	11,686.52	184.2%	272.1%	1,659	107
-3,776,001,016,975,145,508	-1129251442614503651	Professional	3.41	1.21	1,897.74	315.66	182.9%	501.2%	726	795
-3,776,001,016,975,145,508	5046591772473192223	Professional	4.19	1.48	454.30	334.61	182.4%	35.8%	1,107	1,131
4,281,965,180,796,802,413	7063415233901130033	Professional	2.04	0.73	545.22	267.29	181.0%	104.0%	4,509	765
8,361,580,493,441,765,265	8525801659323831681	Professional	1.99	0.71	717.32	146.31	180.6%	390.3%	4,723	882
-3,776,001,016,975,145,508	6246553284827218110	Professional	3.62	1.3	466.90	440.75	177.6%	5.9%	1,183	818
-6,972,129,921,516,377,126	6285	Inpatient	8.22	2.97	109,076.21	64,009.83	177.0%	70.4%	43	352

Showing 50 of 73135 total rows

Click to see SQL

WITH
new AS (
    SELECT
        network_id,
        provider_id,
        bill_type,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY network_id, provider_id, bill_type
),
old AS (
    SELECT
        network_id,
        provider_id,
        bill_type,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_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
)
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: 117 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
8,361,580,493,441,765,265	inf	inf	4,880.15	5,697.73		-14.3%	75,306,728	62,102,719
-3,776,001,016,975,145,508	inf	inf	9,972.13	7,713.05		29.3%	38,334,136	50,351,926
4,281,965,180,796,802,413	inf	inf	4,764.00	5,554.47		-14.2%	73,016,805	59,911,722
-8,499,759,224,271,609,810	2.1	4.49	196.55	557.98	-53.1%	-64.8%	357,727	60,019
5,605,126,640,932,710,930	1.87	2.4	16,559.87	30,500.65	-21.9%	-45.7%	1,115,632	272,868
1,481,239,756,837,705,768	2.3	2.85	5,118.74	13,583.26	-19.1%	-62.3%	1,023,693	387,412
8,180,198,595,049,150,616	1.91	1.6	10,473.94	5,055.55	19.0%	107.2%	77,723	196,804
4,043,190,250,846,473,271	1.66	2.04	4,739.30	6,994.38	-18.7%	-32.2%	1,612,273	1,486,425
-4,445,518,905,666,343,797	1.7	2.07	765.23	11,795.87	-17.6%	-93.5%	209,601	195,116
554,749,539,877,544,775	1.92	2.31	13,650.18	12,802.23	-16.8%	6.6%	882,101	1,410,318
-8,389,359,264,395,369,979	2.26	2.66	14,699.59	13,866.30	-15.3%	6.0%	371,926	499,911
-7,942,674,608,514,495,282	1.78	2.09	5,496.64	8,330.35	-15.0%	-34.0%	1,737,406	1,121,341
-2,803,535,769,510,843,135	1.88	2.19	12,108.52	11,661.47	-14.4%	3.8%	876,417	1,306,248
-1,199,840,640,198,334,888	1.89	2.21	7,115.93	10,008.56	-14.3%	-28.9%	2,113,353	1,477,539
1,391,056,274,517,336,075	2.37	2.73	9,142.14	10,993.49	-13.1%	-16.8%	1,125,821	1,029,273
-4,944,393,768,879,386,509	3.41	3.9	8,978.68	17,914.20	-12.5%	-49.9%	144,764	545,002
6,437,583,333,460,157,798	2.77	2.48	10,102.54	8,959.12	11.5%	12.8%	645,863	945,542
-163,892,009,874,547,167	2.21	2.48	13,381.65	14,085.92	-11.0%	-5.0%	460,369	501,454
1,998,605,818,287,672,800	2.44	2.73	5,135.19	9,038.41	-10.4%	-43.2%	711,405	457,003
1,134,341,852,115,164,929	1.69	1.89	5,303.66	7,053.12	-10.2%	-24.8%	991,207	929,484
6,974,535,101,123,239,131	3.05	2.77	10,563.28	8,445.14	10.2%	25.1%	503,462	606,115
6,091,386,345,134,788,986	3.11	2.83	10,917.22	8,484.17	9.9%	28.7%	180,644	232,032
6,037,342,850,284,539,624	2.09	2.3	3,267.82	5,284.81	-9.4%	-38.2%	1,024,733	932,329
2,039,367,776,086,447,454	2.15	2.36	5,255.82	6,115.56	-9.2%	-14.1%	1,655,499	1,281,449
-137,228,703,119,221,502	1.83	2.01	5,189.37	6,213.43	-9.0%	-16.5%	1,067,293	946,219
3,478,392,755,490,109,147	2.02	2.22	5,747.26	6,772.07	-8.8%	-15.1%	415,334	360,397
-6,293,294,949,651,930,973	2.32	2.54	6,366.96	9,258.28	-8.5%	-31.2%	2,005,789	1,840,973
-2,105,332,499,153,538,685	2.25	2.45	8,495.53	10,088.54	-8.4%	-15.8%	882,529	799,292
-4,027,592,970,285,917,801	2.6	2.4	10,116.95	8,056.49	8.2%	25.6%	746,263	919,884
1,178,549,910,471,784,015	2.74	2.53	12,153.27	10,299.45	8.1%	18.0%	496,310	585,546
-7,420,630,624,364,773,044	2.36	2.18	8,660.43	7,194.76	8.0%	20.4%	733,954	862,432
2,884,766,606,938,738,180	1.56	1.69	4,887.61	6,630.35	-7.7%	-26.3%	926,282	835,922
3,376,187,909,306,580,799	2.01	2.17	2,805.04	4,068.51	-7.6%	-31.1%	1,046,546	843,362
5,263,188,873,138,819,140	1.44	1.56	4,366.52	5,732.67	-7.3%	-23.8%	1,502,393	1,221,641
5,075,764,097,723,629,059	2.03	2.18	8,518.38	10,446.73	-7.1%	-18.5%	2,344,852	1,927,556
6,650,880,607,858,420,237	1.49	1.6	4,496.61	5,856.66	-7.0%	-23.2%	1,620,300	1,356,008
-8,506,106,384,867,378,430	1.35	1.45	2,145.21	3,097.19	-6.9%	-30.7%	954,624	733,509
7,616,836,552,481,822,175	2.12	2.28	6,135.32	8,753.10	-6.9%	-29.9%	5,296,836	4,743,803
-2,215,537,880,835,804,330	2.46	2.64	9,178.32	10,670.83	-6.8%	-14.0%	1,492,986	1,421,293
-558,178,834,365,778,978	2.03	2.17	8,516.67	10,449.52	-6.8%	-18.5%	2,337,424	1,916,552
1,017,413,876,722,652,424	2.01	2.16	5,451.84	7,828.38	-6.7%	-30.4%	4,826,583	4,323,665
-6,972,129,921,516,377,126	2.32	2.48	5,353.81	7,429.71	-6.2%	-27.9%	4,200,405	3,845,550
1,295,090,440,394,638,876	1.9	2.02	14,727.81	13,185.38	-6.2%	11.7%	183,458	233,390
-7,995,682,717,897,123,039	2.63	2.8	10,927.32	20,744.13	-6.1%	-47.3%	2,915,913	3,398,674
3,973,810,847,438,814,798	4.86	4.6	11,595.36	22,099.94	5.8%	-47.5%	755,860	948,917
-7,163,483,760,674,166,628	1.83	1.94	2,638.02	3,389.66	-5.7%	-22.2%	1,593,024	1,255,468
5,373,876,635,451,223,842	2.37	2.51	5,812.97	7,763.62	-5.7%	-25.1%	4,293,107	3,892,981
-5,874,281,272,803,523,303	1.82	1.93	2,602.60	3,349.46	-5.7%	-22.3%	1,591,921	1,253,703
-1,766,392,260,690,175,685	2.3	2.17	6,723.71	7,511.48	5.6%	-10.5%	1,615,701	1,514,382
-4,541,460,228,936,150,437	2.2	2.33	8,813.86	10,782.88	-5.6%	-18.3%	2,417,502	1,997,410

Showing 50 of 117 total rows

Click to see SQL

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

service_line	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Radiology	100,675,630	42,612,257	58,063,373	136.3%
Pulmonology	10,084,024	6,512,618	3,571,406	54.8%
Neurology	24,221,460	16,100,154	8,121,306	50.4%
Lab/Path	55,764,175	37,140,268	18,623,907	50.1%
Cardiovascular	31,153,672	20,848,838	10,304,834	49.4%
Transplant	97,655	67,023	30,632	45.7%
Ophthalmology	18,656,403	12,834,682	5,821,721	45.4%
Urology	13,439,244	9,256,999	4,182,245	45.2%
Gastrointestinal	28,050,824	19,585,234	8,465,590	43.2%
Dermatology	31,312,512	22,071,665	9,240,847	41.9%
Behavioral Health	6,469,633	4,573,663	1,895,970	41.5%
Infectious Disease	30,564,595	21,741,157	8,823,438	40.6%
Obstetrics	8,721,317	6,270,693	2,450,624	39.1%
Rehab	7,898,387	5,720,023	2,178,364	38.1%
MSK	65,824,942	47,815,959	18,008,983	37.7%
Consultative and Preventative Care	12,829,088	9,322,871	3,506,217	37.6%
Emergency	996,117	724,297	271,820	37.5%
Reproductive	11,125,621	8,094,687	3,030,934	37.4%
Trauma	2,332,214	1,698,602	633,612	37.3%
ENT	16,506,825	12,049,452	4,457,373	37.0%
Dental	528,237	386,407	141,830	36.7%
DME and Supplies	2,529,332	1,868,061	661,271	35.4%
Hematology	2,333,792	1,726,285	607,507	35.2%
Surgery	101,420	75,911	25,509	33.6%
Endocrinology	2,252,143	1,690,257	561,886	33.2%
Nephrology	1,494,526	1,123,458	371,068	33.0%
Anesthesia	18,481,666	13,991,408	4,490,258	32.1%
Oncology	13,740,835	10,422,074	3,318,761	31.8%
Diabetes	376,663	289,841	86,822	30.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_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY service_line
),
old AS (
    SELECT
        service_line,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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: 5 categories with significant ROID count changes

provider_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Physician Group	296,243,211	168,018,318	128,224,893	76.3%
ASC	21,462,464	14,469,409	6,993,055	48.3%
Imaging Center	4,037,494	2,964,629	1,072,865	36.2%
Hospital	308,325,250	226,818,793	81,506,457	35.9%
Laboratory	12,774,428	11,292,016	1,482,412	13.1%

Click to see SQL

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

state	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
WV	3,336,283	1,815,330	1,520,953	83.8%
WY	1,347,471	748,623	598,848	80.0%
CT	5,291,400	3,091,402	2,199,998	71.2%
AK	983,206	582,041	401,165	68.9%
SC	7,208,551	4,280,548	2,928,003	68.4%
NE	5,840,959	3,492,394	2,348,565	67.2%
DE	1,491,596	893,174	598,422	67.0%
MD	7,783,013	4,688,020	3,094,993	66.0%
KY	8,712,093	5,263,435	3,448,658	65.5%
UT	2,873,465	1,737,453	1,136,012	65.4%
DC	4,558,292	2,756,542	1,801,750	65.4%
NJ	18,524,980	11,208,368	7,316,612	65.3%
AL	10,073,697	6,203,235	3,870,462	62.4%
NV	4,764,858	2,936,794	1,828,064	62.2%
IN	14,074,360	8,680,229	5,394,131	62.1%
MS	7,572,722	4,696,350	2,876,372	61.2%
AZ	12,429,536	7,731,320	4,698,216	60.8%
CO	9,385,665	5,887,281	3,498,384	59.4%
OH	22,519,974	14,139,693	8,380,281	59.3%
ID	4,227,247	2,668,922	1,558,325	58.4%
LA	14,012,195	8,882,236	5,129,959	57.8%
NC	16,550,393	10,505,399	6,044,994	57.5%
FL	34,509,788	22,075,310	12,434,478	56.3%
OR	8,377,377	5,365,678	3,011,699	56.1%
RI	1,539,821	987,014	552,807	56.0%
KS	9,091,468	5,831,438	3,260,030	55.9%
VT	760,033	487,698	272,335	55.8%
GA	18,478,090	11,987,179	6,490,911	54.1%
MI	19,479,730	12,657,171	6,822,559	53.9%
TN	13,466,433	8,774,908	4,691,525	53.5%
OK	9,969,421	6,568,659	3,400,762	51.8%
MT	2,714,552	1,789,824	924,728	51.7%
NM	2,809,656	1,857,949	951,707	51.2%
IA	9,131,679	6,104,513	3,027,166	49.6%
AR	7,084,294	4,748,620	2,335,674	49.2%
MO	14,243,987	9,551,652	4,692,335	49.1%
PA	27,955,670	18,785,308	9,170,362	48.8%
IL	22,161,556	14,896,285	7,265,271	48.8%
WA	14,281,060	9,620,803	4,660,257	48.4%
TX	58,053,641	39,154,741	18,898,900	48.3%
WI	13,219,960	8,968,630	4,251,330	47.4%
NY	51,810,345	35,519,837	16,290,508	45.9%
ME	3,254,071	2,239,616	1,014,455	45.3%
VA	15,746,175	10,862,226	4,883,949	45.0%
ND	1,983,412	1,373,546	609,866	44.4%
CA	63,867,477	45,035,425	18,832,052	41.8%
NH	3,280,899	2,329,791	951,108	40.8%
SD	2,838,861	2,032,123	806,738	39.7%
MN	12,672,920	9,089,397	3,583,523	39.4%
MA	14,871,797	10,777,975	4,093,822	38.0%

Showing 50 of 51 total rows

Click to see SQL

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

payer_network_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
HealthSmart Preferred PPO	357,727	60,019	297,708	496.0%
EmblemHealth GHI / Anthem CBP	1,115,632	272,868	842,764	308.9%
Regence Blue Cross Blue Shield OR PPO	1,023,693	387,412	636,281	164.2%
Kaiser Permanente Kaiser Health Plan Mid Atlantic	144,764	545,002	-400,238	-73.4%
Harvard Pilgrim Health Care Choicenet PPO	316,059	1,051,798	-735,739	-70.0%
Blue Cross Blue Shield of Rhode Island PPO	77,723	196,804	-119,081	-60.5%
Blue Cross Blue Shield of Nebraska Networkblue PPO	711,405	457,003	254,402	55.7%
Blue Cross Blue Shield of New Jersey (Horizon) OMNIA	1,737,406	1,121,341	616,065	54.9%
Kaiser Permanente Kaiser Health Plan (CO)	265,726	570,378	-304,652	-53.4%
Baylor Scott & White Health Plan PPO	590,151	1,115,539	-525,388	-47.1%
Blue Cross Blue Shield of New Jersey (Horizon) PPO	2,113,353	1,477,539	635,814	43.0%
Sanford Health Plan PPO	536,633	933,593	-396,960	-42.5%
Kaiser Permanente Kaiser Health Plan Northwest	379,570	618,318	-238,748	-38.6%
HealthLink PPO	882,101	1,410,318	-528,217	-37.5%
Mass General Brigham Health Plan Commercial PPO	265,615	193,744	71,871	37.1%
Blue Cross Blue Shield of Maryland & DC (CareFirst) Preferred Provider Network (PPO)	1,455,337	1,062,628	392,709	37.0%
Aetna NC HMO	1,529,548	1,125,285	404,263	35.9%
Ambetter HMO	2,665,456	1,966,175	699,281	35.6%
The Alliance Comprehensive Network	241,054	178,137	62,917	35.3%
Univera Healthcare PPO	578,767	428,710	150,057	35.0%
Capital Health Plan PPO	24,165	36,817	-12,652	-34.4%
HealthLink HMO	876,417	1,306,248	-429,831	-32.9%
Blue Cross Blue Shield of Wyoming PPO	340,966	257,047	83,919	32.6%
Avera Health Plans Avera Health	99,381	146,232	-46,851	-32.0%
Blue Cross Blue Shield of Illinois HMO	645,863	945,542	-299,679	-31.7%
Blue Cross Blue Shield of Maryland & DC (CareFirst) HMO	954,624	733,509	221,115	30.1%
Aetna VA HMO	1,655,499	1,281,449	374,050	29.2%
Blue Cross Blue Shield of New York (Excellus) Excellus Bluecross Blueshield	1,576,867	1,223,283	353,584	28.9%
Harvard Pilgrim Health Care HMO	1,284,439	1,010,603	273,836	27.1%
Sentara Health Plans HMO	1,591,921	1,253,703	338,218	27.0%
Sentara Health Plans PPO	1,593,024	1,255,468	337,556	26.9%
Premera Blue Cross Alaska Heritage Network	250,460	197,656	52,804	26.7%
Medical Mutual Of Ohio SuperMed	572,380	775,475	-203,095	-26.2%
Blue Cross Blue Shield of Pennsylvania (Independence) KHPE Commercial HMO/POS	371,926	499,911	-127,985	-25.6%
Blue Cross Blue Shield of North Carolina HMO	345,526	458,028	-112,502	-24.6%
EmblemHealth DC37 Med-Team	1,845,678	1,482,664	363,014	24.5%
Cigna NH HMO	174,877	231,409	-56,532	-24.4%
Cigna New England HMO	1,022,228	1,350,059	-327,831	-24.3%
Cigna NC HMO	842,142	1,110,403	-268,261	-24.2%
MVP Health Care HMO	1,046,546	843,362	203,184	24.1%
Cigna National OAP	38,334,136	50,351,926	-12,017,790	-23.9%
Aetna GA HMO	1,635,883	1,320,925	314,958	23.8%
Health Alliance Plan (HAP) HMO	1,502,393	1,221,641	280,752	23.0%
Aetna CA HMO	5,206,929	4,258,596	948,333	22.3%
Cigna NJ HMO	704,877	906,422	-201,545	-22.2%
Cigna ME HMO	180,644	232,032	-51,388	-22.1%
Anthem CT HMO	464,336	380,416	83,920	22.1%
Aetna IL HMO	2,107,738	1,727,526	380,212	22.0%
Anthem Blue Access Gated EPO	2,337,424	1,916,552	420,872	22.0%
Mass General Brigham Health Plan HMO	938,454	769,553	168,901	21.9%

Showing 50 of 117 total rows

Click to see SQL

WITH
new AS (
    SELECT
        payer_network_name,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY payer_network_name
),
old AS (
    SELECT
        payer_network_name,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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
389	Blue Cross Blue Shield of Illinois	PPO Participating Provider Options	imputation	289,465	0
42	Anthem	WI Blue Access PPO	imputation	128,753	0
166	Blue Cross Blue Shield of New Mexico	HMO	imputation	9,966	0
42	Anthem	NH HMO	imputation	3,306	0
54	Blue Cross Blue Shield of Pennsylvania (Capital Blue Cross)	PPO	imputation	40,706	0
43	Blue Cross Blue Shield of Arizona	HMO	imputation	22,162	0
383	Blue Cross of Idaho	PPO	imputation	28,533	0
61	Regence Blue Cross Blue Shield	ID PPO	imputation	41,847	0
636	SelectHealth	Value/Care Tiered POS - UT	imputation	16,951	0
53	Blue Cross Blue Shield of Oklahoma	HMO	imputation	2,262	0

Click to see SQL

WITH
new AS (
    SELECT 
        payer_id,
        payer_name, 
        network_name, 
        canonical_rate_source, 
        count(distinct roid) AS n_new
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY 1,2,3,4
),
old AS (
    SELECT 
        payer_id,
        payer_name, 
        network_name, 
        canonical_rate_source, 
        count(distinct roid) AS n_old
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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: 783 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	Vibra Healthcare	310,464	48,607	261,857	538.7%
Hospital	Select Medical Corporation	784,937	180,131	604,806	335.8%
Hospital	PAM Health	338,214	142,222	195,992	137.8%
Physician Group	Billings Clinic Health System	135,329	62,182	73,147	117.6%
Hospital	ScionHealth	1,003,070	462,008	541,062	117.1%
Physician Group	WVU Medicine	138,293	64,826	73,467	113.3%
Physician Group	Appalachian Regional Healthcare	143,103	67,496	75,607	112.0%
Physician Group	Bryan Health	190,970	92,332	98,638	106.8%
Physician Group	Asante	138,954	68,433	70,521	103.1%
Hospital	Mosaic Life Care	204,446	101,392	103,054	101.6%
Hospital	Spaulding Rehabilitation Network	100,518	50,623	49,895	98.6%
Physician Group	St Charles Health System	146,268	75,957	70,311	92.6%
Physician Group	SSM Health in Wisconsin	117,068	60,834	56,234	92.4%
Physician Group	Marshall Health Network	112,570	58,627	53,943	92.0%
Physician Group	ThedaCare	142,190	74,936	67,254	89.7%
Hospital	Mon Health	102,466	54,115	48,351	89.3%
Hospital	Physicians Regional Healthcare System	210,405	111,568	98,837	88.6%
Physician Group	Novant Health	464,100	247,065	217,035	87.8%
Physician Group	Providence Health & Services - Oregon and Southwest Washington	268,990	143,978	125,012	86.8%
Physician Group	Ascension Wisconsin	315,746	169,744	146,002	86.0%
Physician Group	SUNY Upstate Medical University	217,664	117,873	99,791	84.7%
Physician Group	Baptist Health	322,687	175,005	147,682	84.4%
Physician Group	Parkview Health	144,059	78,474	65,585	83.6%
Physician Group	LifeBridge Health	200,001	108,987	91,014	83.5%
Physician Group	Norton Healthcare	102,486	55,947	46,539	83.2%
Physician Group	Bellin Health	490,024	267,660	222,364	83.1%
Hospital	Wellstar MCG Health	301,074	164,690	136,384	82.8%
Physician Group	Guthrie Clinic	112,198	61,467	50,731	82.5%
Physician Group	TMC Health	138,165	75,789	62,376	82.3%
Physician Group	Intermountain Health	462,347	253,653	208,694	82.3%
Physician Group	Hartford HealthCare	442,089	242,651	199,438	82.2%
Physician Group	Deaconess Health System	233,243	128,085	105,158	82.1%
Physician Group	Adventist Health Hanford	151,768	83,775	67,993	81.2%
Physician Group	Bon Secours Health System	232,175	128,187	103,988	81.1%
Physician Group	Powers Health	112,188	62,161	50,027	80.5%
Physician Group	AdventHealth	641,716	355,878	285,838	80.3%
Physician Group	CommonSpirit Health	131,423	73,144	58,279	79.7%
Physician Group	Indiana University Health	116,722	65,071	51,651	79.4%
Physician Group	Surgery Partners	107,024	59,777	47,247	79.0%
Physician Group	Legacy Health	119,839	67,011	52,828	78.8%
Physician Group	Cooper University Health	281,628	157,516	124,112	78.8%
Physician Group	Atlantic Health System	229,021	128,397	100,624	78.4%
Hospital	Cape Fear Valley Health System	202,751	113,938	88,813	77.9%
Physician Group	Allegheny Health Network	202,675	114,120	88,555	77.6%
Physician Group	Providence Health & Services - Washington	112,053	63,489	48,564	76.5%
Physician Group	UCHealth	177,167	100,456	76,711	76.4%
Hospital	Infirmary Health System	218,721	124,076	94,645	76.3%
Physician Group	St Lukes Health System	165,098	93,893	71,205	75.8%
Physician Group	PeaceHealth	510,377	290,379	219,998	75.8%
Physician Group	ProMedica Health System	317,818	181,856	135,962	74.8%

Showing 50 of 783 total rows

Click to see SQL

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

canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
3	329,745,102	203,570,381	126,174,721	62.0%
2	79,896,632	50,552,096	29,344,536	58.0%
4	189,350,804	137,012,361	52,338,443	38.2%
5	43,850,309	32,428,327	11,421,982	35.2%

Click to see SQL

WITH
new AS (
    SELECT
        canonical_rate_score,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY canonical_rate_score
),
old AS (
    SELECT
        canonical_rate_score,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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: 15 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	4	21,047,569	14,078,360	6,969,209	49.5%
Hospital	5	37,675,253	27,187,606	10,487,647	38.6%
Hospital	4	160,789,302	117,210,186	43,579,116	37.2%
Hospital	2	22,141,007	16,435,648	5,705,359	34.7%
Hospital	3	87,719,688	65,985,353	21,734,335	32.9%
Imaging Center	4	4,037,149	2,964,215	1,072,934	36.2%
Imaging Center	3	268	325	-57	-17.5%
Imaging Center	2	77	89	-12	-13.5%
Laboratory	4	685,652	589,271	96,381	16.4%
Laboratory	3	4,573,162	3,951,273	621,889	15.7%
Laboratory	5	5,861,831	5,170,508	691,323	13.4%
Physician Group	5	313,225	70,213	243,012	346.1%
Physician Group	3	237,209,780	133,407,892	103,801,888	77.8%
Physician Group	2	55,929,074	32,369,884	23,559,190	72.8%
Physician Group	4	2,791,132	2,170,329	620,803	28.6%

Click to see SQL

WITH
new AS (
    SELECT
        provider_type,
        canonical_rate_score,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY provider_type, canonical_rate_score
),
old AS (
    SELECT
        provider_type,
        canonical_rate_score,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY provider_type, canonical_rate_score
)
SELECT
    provider_type,
    canonical_rate_score,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (provider_type, canonical_rate_score)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY provider_type, ABS(roid_count_pct_change) DESC

By Canonical Rate Class

Count: 3 categories with significant ROID count changes

canonical_rate_class	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Raw	513,920,816	326,470,730	187,450,086	57.4%
Impute	94,980,880	70,612,086	24,368,794	34.5%
Transform	33,941,151	26,480,349	7,460,802	28.2%

Click to see SQL

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

canonical_rate_source	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
payer	414,135,609	257,041,952	157,093,657	61.1%
hospital	89,876,049	63,480,800	26,395,249	41.6%
payer_hospital	43,850,309	32,428,327	11,421,982	35.2%
imputation	94,980,880	70,612,086	24,368,794	34.5%

Click to see SQL

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

canonical_contract_methodology	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Fee Schedule	377,413,105	229,527,061	147,886,044	64.4%
Unknown	23,370,462	16,829,968	6,540,494	38.9%
Case Rate	119,669,706	87,402,938	32,266,768	36.9%
Percent of Total Billed Charges	117,880,424	86,292,030	31,588,394	36.6%
Per Diem	4,509,150	3,511,168	997,982	28.4%

Click to see SQL

WITH
new AS (
    SELECT
        canonical_contract_methodology,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY canonical_contract_methodology
),
old AS (
    SELECT
        canonical_contract_methodology,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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: 8 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 MS-DRG	1,227,480	617,095	610,385	98.9%
Crosswalked From CSTM-ALL to APR-DRG	1,422,072	716,902	705,170	98.4%
Crosswalked From CSTM-ALL to HCPCS	20,340,645	12,180,065	8,160,580	67.0%
Crosswalked From MS-DRG to APR-DRG	26,399,689	19,166,623	7,233,066	37.7%
Crosswalked From RC-FAMILY to APR-DRG	1,140,096	916,018	224,078	24.5%
Crosswalked From RC-FAMILY to MS-DRG	837,809	695,804	142,005	20.4%
Crosswalked From NDC to HCPCS	950,588	1,192,692	-242,104	-20.3%
Crosswalked From APC to HCPCS	7,987,270	6,706,257	1,281,013	19.1%

Click to see SQL

WITH
new AS (
    SELECT
        canonical_crosswalk_method,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_1.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY canonical_crosswalk_method
),
old AS (
    SELECT
        canonical_crosswalk_method,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_0.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: 6 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	30,144,742	20,677,134	9,467,608	45.8%
mrf_gross_charge_provider	71,356,967	51,301,044	20,055,923	39.1%
mrf_gross_charge_state_median	131,887,833	97,588,552	34,299,281	35.1%
komodo_gross_charge_provider	2,486,307	1,874,765	611,542	32.6%
komodo_gross_charge_state_median	15,094,453	11,908,067	3,186,386	26.8%
komodo_gross_charge_cbsa_median	2,717,231	2,251,854	465,377	20.7%

Click to see SQL

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

---