v2_3_0 → v2_3_1

Generated: 2026-01-08 10:55:23.
Filter: canonical_rate_score > 1.

---

1. Summary Statistics

Overview

• Total ROIDs: 378,780,950 → 402,677,568 (+23,896,618, +6.3%)

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	6,467,877	3,535,461	154	146	814	696	913	917
Physician Group	Professional	165,994,574	150,471,614	175	167	20,774	17,335	3,776	3,781
ASC	Outpatient	14,764,804	15,194,451	146	143	5,581	5,590	3,361	3,369
Hospital	Outpatient	170,254,264	164,813,213	174	167	5,663	5,647	7,084	7,064
Imaging Center	Professional	1,412,861	2,980,187	153	150	2,848	2,976	164	172
Hospital	Inpatient	43,783,188	41,786,024	171	167	5,606	5,594	1,717	1,717

Click to see SQL

WITH
new AS (
    SELECT
        provider_type,
        bill_type,
        COUNT(DISTINCT roid) AS distinct_roids,
        COUNT(DISTINCT payer_id) AS distinct_payers,
        COUNT(DISTINCT network_id) AS distinct_networks,
        COUNT(DISTINCT provider_id) AS distinct_providers,
        COUNT(DISTINCT billing_code) AS distinct_billing_codes
    FROM tq_dev.internal_dev_csong_cld_v2_3_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_3_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: 209 dropped

Click to see SQL

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

No dropoffs detected.

Click to see SQL

WITH
new AS (
    SELECT DISTINCT network_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_3_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_3_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: 3483 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_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_3_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: 25 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_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_3_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: 4432 entities with significant rate changes

bill_type	provider_id	new_avg_pct_of_medicare_rate	old_avg_pct_of_medicare_rate	new_avg_canonical_rate	old_avg_canonical_rate	medicare_rate_pct_change	canonical_rate_pct_change	new_roid_count	old_roid_count
Inpatient	4523	4.01	1.25	67,844.46	14,242.55	221.0%	376.4%	3,302	882
Professional	-4628066455756742447	3.36	1.08	1,561.93	5,163.47	210.4%	-69.8%	23,016	1,999
Outpatient	10507	3.29	1.1	7,397.59	3,390.47	198.8%	118.2%	34,281	692
Professional	-7106726521015360774	3.07	1.06	1,808.78	285.57	188.8%	533.4%	34,770	938
Professional	3193278596207345254	3.67	1.28	2,703.98	8,396.90	186.5%	-67.8%	9,862	1,489
Professional	-1493008543685875813	2.94	1.06	1,365.90	6,512.10	178.4%	-79.0%	13,749	662
Professional	7899513482663811806	2.92	1.05	1,351.25	6,252.82	177.6%	-78.4%	13,755	690
Professional	584930542830122847	3.01	1.11	1,681.99	5,080.78	171.9%	-66.9%	3,169	855
Inpatient	3882	3.07	1.16	52,288.62	18,141.00	164.0%	188.2%	5,916	92
Professional	6309773015046657538	2.71	1.05	917.79	1,477.81	157.4%	-37.9%	2,176	691
Inpatient	860	3.2	1.32	40,725.39	17,837.99	143.1%	128.3%	84	308
Professional	-5886670808142517704	2.57	1.06	1,265.70	6,464.62	142.7%	-80.4%	28,666	2,002
Outpatient	8395	2.74	1.15	5,389.06	672.75	138.4%	701.0%	4,021	1,201
Inpatient	1654	3.34	1.41	55,139.87	21,175.22	137.0%	160.4%	6,776	5,596
Inpatient	1746	2.59	1.1	52,738.50	20,829.40	136.6%	153.2%	6,060	827
Professional	6142195696931586702	2.46	1.06	1,434.30	167.50	132.7%	756.3%	25,000	892
Professional	-7902779422664431535	2.44	1.07	1,136.79	6,077.03	128.0%	-81.3%	20,727	700
Professional	-2635133499079390052	2.46	1.09	1,159.49	5,525.27	126.0%	-79.0%	20,086	627
Inpatient	1145	4.49	2.01	46,121.08	20,724.05	123.0%	122.5%	93	329
Professional	-280694059845959968	2.44	1.1	1,768.33	8,318.18	120.9%	-78.7%	4,300	504
Professional	-5534215453433769048	2.44	1.1	1,762.53	8,318.18	120.8%	-78.8%	4,315	504
Professional	-5306777069328025024	2.42	1.1	1,741.48	8,318.18	119.3%	-79.1%	4,369	504
Professional	890106468733209218	2.32	1.06	1,185.54	6,409.28	118.8%	-81.5%	16,335	1,350
Inpatient	1659	2.51	1.16	49,734.57	21,652.91	116.9%	129.7%	3,464	1,774
Inpatient	4077	2.49	1.15	44,496.21	18,361.65	116.7%	142.3%	2,331	308
Professional	3228476953590425655	4.45	2.06	1,180.52	1,544.26	115.9%	-23.6%	1,290	4,286
Inpatient	10266	3.09	1.44	26,779.66	13,555.79	114.8%	97.6%	64	190
Professional	433643027565259209	2.6	1.24	1,020.54	7,490.95	110.8%	-86.4%	14,327	691
Inpatient	1698	2.3	1.11	45,333.55	21,369.03	106.5%	112.1%	9,560	5,299
Inpatient	1712	2.44	1.19	52,000.55	24,992.99	104.9%	108.1%	8,362	5,789
Professional	9104213900107629209	2.36	1.16	1,282.07	2,423.37	103.5%	-47.1%	8,801	1,424
Inpatient	2742	2.3	1.14	41,666.80	16,079.61	101.2%	159.1%	4,614	3,097
Professional	5620639211081377557	2.09	1.05	926.80	6,427.52	99.7%	-85.6%	13,872	1,346
Inpatient	2720	2.59	1.3	42,594.97	13,721.76	98.7%	210.4%	2,678	349
Outpatient	9408	3.22	1.64	6,629.00	432.05	96.5%	1434.3%	9,202	1,213
Inpatient	10111	2.72	1.38	41,447.58	21,692.62	96.3%	91.1%	6,793	4,409
Professional	4023060950915423295	2.24	1.15	749.20	5,272.16	95.3%	-85.8%	9,446	922
Inpatient	1220	2.32	1.19	23,417.56	12,233.22	95.1%	91.4%	95	680
Professional	-3660965165911820084	2.18	1.12	899.32	1,486.85	94.5%	-39.5%	18,154	839
Professional	-65955366608295509	2.06	1.08	977.25	6,120.79	91.6%	-84.0%	5,868	695
Professional	-2138544681139717803	2.05	1.08	2,784.64	7,665.20	90.6%	-63.7%	952	534
Professional	228633303655034534	2.04	1.07	667.52	436.09	90.3%	53.1%	14,028	758
Inpatient	1006	2.76	1.47	26,763.61	14,854.32	88.2%	80.2%	133	555
Professional	-1592729558437445532	1.99	1.07	826.79	289.88	86.6%	185.2%	14,133	514
Professional	423428797205956888	1.98	1.06	4,189.94	8,801.50	86.6%	-52.4%	1,160	942
Inpatient	2752	2.14	1.15	39,129.07	15,649.98	85.4%	150.0%	4,164	1,923
Outpatient	10679	4.62	29.93	10,425.63	11,643.22	-84.6%	-10.5%	31,571	24,632
Professional	-6880903641465428744	1.94	1.06	1,618.38	281.85	82.9%	474.2%	4,756	968
Professional	-1745185663183391073	1.94	1.06	7,378.57	8,725.01	82.8%	-15.4%	630	950
Inpatient	1343	2.12	1.18	21,272.98	11,960.28	80.7%	77.9%	96	631

Showing 50 of 4432 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_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_3_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: 14036 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,281,965,180,796,802,413	1895	5.37	1.5	32,997.35	29,668.18	258.5%	11.2%	5,308	1,108
861,259,371,462,427,559	-1493008543685875813	3.29	1.06	4,944.90	6,512.10	211.5%	-24.1%	560	662
8,361,580,493,441,765,265	-1612902388498009527	3.41	1.1	1,256.43	405.23	210.2%	210.1%	3,597	660
4,911,047,023,988,898,996	2217	6.61	2.17	37,598.08	22,432.22	204.1%	67.6%	7,788	7,788
4,281,965,180,796,802,413	-1612902388498009527	3.31	1.1	1,172.22	405.23	201.8%	189.3%	3,398	660
4,911,047,023,988,898,996	2128	6.64	2.2	37,605.94	22,443.91	201.6%	67.6%	7,784	7,787
4,911,047,023,988,898,996	2215	6.64	2.2	37,681.94	22,510.98	201.3%	67.4%	7,775	7,767
-3,776,001,016,975,145,508	2217	6.67	2.28	37,694.82	22,615.85	192.7%	66.7%	7,989	7,970
-3,776,001,016,975,145,508	2128	6.76	2.31	38,120.25	22,849.69	192.3%	66.8%	7,900	7,888
2,119,715,416,692,563,737	969	3.77	1.29	14,609.02	987.82	191.7%	1378.9%	5,645	2,922
-3,776,001,016,975,145,508	2215	6.62	2.31	37,158.83	22,427.72	187.2%	65.7%	8,097	8,066
-6,293,294,949,651,930,973	-65955366608295509	2.95	1.08	2,320.09	6,120.79	173.8%	-62.1%	1,358	695
4,281,965,180,796,802,413	-5535260362071740111	5.11	1.87	4,404.78	14,609.99	173.3%	-69.9%	1,199	514
6,650,880,607,858,420,237	2950	3.09	1.13	14,394.36	23,160.81	172.4%	-37.9%	4,736	1,219
-6,293,294,949,651,930,973	584930542830122847	3.01	1.11	1,681.99	5,080.78	171.9%	-66.9%	3,169	855
4,281,965,180,796,802,413	319	2.97	1.1	6,346.62	740.56	170.1%	757.0%	4,696	1,205
861,259,371,462,427,559	7899513482663811806	2.83	1.05	4,550.97	6,252.82	168.8%	-27.2%	566	690
-6,293,294,949,651,930,973	4023060950915423295	3.06	1.15	1,728.42	5,272.16	166.7%	-67.2%	2,653	922
4,281,965,180,796,802,413	5865	2.91	1.1	3,544.33	653.72	165.5%	442.2%	2,570	1,202
-7,695,283,351,826,393,948	-1592729558437445532	2.78	1.07	289.64	289.88	161.0%	-0.1%	1,214	514
-7,580,909,833,117,018,375	1297	3.22	1.25	13,296.74	15,244.71	158.0%	-12.8%	7,757	2,459
-3,711,121,367,896,011,833	978503522494579513	4.45	1.83	94.30	136.24	143.4%	-30.8%	573	1,991
-3,711,121,367,896,011,833	2305726694726575217	4.45	1.83	94.30	136.24	143.4%	-30.8%	573	1,991
-3,711,121,367,896,011,833	7256875003103537069	4.45	1.83	94.20	136.21	143.4%	-30.8%	573	1,991
-3,711,121,367,896,011,833	-2554745372953414343	4.45	1.83	94.20	136.21	143.4%	-30.8%	573	1,991
4,281,965,180,796,802,413	5834	2.65	1.1	2,969.80	735.59	141.0%	303.7%	2,871	1,206
-3,776,001,016,975,145,508	3101	3.09	1.29	12,809.22	931.93	139.5%	1274.5%	5,243	1,026
2,039,367,776,086,447,454	8395	2.75	1.15	17,419.64	672.75	139.0%	2489.3%	5,377	1,201
3,478,392,755,490,109,147	-3660965165911820084	2.55	1.08	682.99	428.48	136.9%	59.4%	2,543	773
4,615,092,770,909,199,842	1654	3.92	1.66	61,523.02	22,278.46	136.2%	176.2%	1,827	1,750
-2,162,498,661,799,343,237	7939961992639786364	3.54	1.5	73.62	131.59	135.4%	-44.1%	520	1,952
-2,162,498,661,799,343,237	-2247816892336659600	3.54	1.5	73.62	131.59	135.4%	-44.1%	520	1,952
-3,711,121,367,896,011,833	7939961992639786364	3.54	1.5	73.62	131.59	135.4%	-44.1%	520	1,952
-3,711,121,367,896,011,833	-2247816892336659600	3.54	1.5	73.62	131.59	135.4%	-44.1%	520	1,952
-7,580,909,833,117,018,375	1317	3.4	1.46	14,872.77	8,531.90	132.2%	74.3%	8,297	4,522
-6,293,294,949,651,930,973	-3494964061796008155	2.52	1.09	3,636.94	5,310.76	131.6%	-31.5%	713	653
-3,711,121,367,896,011,833	8922819839730165824	3.53	1.54	87.80	135.19	129.2%	-35.1%	551	1,953
-3,711,121,367,896,011,833	-6496269935786383412	3.53	1.54	87.80	135.19	129.2%	-35.1%	551	1,953
6,974,535,101,123,239,131	7	4.65	2.09	17,761.39	6,338.43	122.8%	180.2%	5,413	3,243
8,611,629,356,743,080,984	7578129148473253785	2.89	1.3	435.38	168.50	122.3%	158.4%	599	1,503
8,611,629,356,743,080,984	-1414207905763961762	2.89	1.3	435.38	168.50	122.3%	158.4%	599	1,503
6,650,880,607,858,420,237	2985	2.82	1.27	15,542.02	22,273.60	121.7%	-30.2%	6,574	2,935
-3,776,001,016,975,145,508	-5755362789290830590	2.75	1.25	1,543.49	399.04	120.8%	286.8%	2,214	929
-3,776,001,016,975,145,508	9784	14.07	6.44	57,803.85	6,555.55	118.4%	781.8%	3,601	1,728
707,343,854,139,029,255	742	2.33	1.1	11,266.30	23,296.06	111.7%	-51.6%	3,326	1,155
5,170,047,179,706,197,777	-5403419749418586593	3.33	1.58	4,077.20	8,945.58	111.1%	-54.4%	1,145	749
-7,160,850,762,653,242,095	-5403419749418586593	3.33	1.58	4,077.20	8,945.58	111.1%	-54.4%	1,145	749
4,683,160,466,464,628,412	-6542220446295814786	2.2	1.05	1,842.28	8,051.04	109.7%	-77.1%	1,348	518
4,281,965,180,796,802,413	5858	2.29	1.1	2,647.31	704.60	109.2%	275.7%	3,336	1,206
6,355,182,945,120,798,897	-1603034259324667206	2.38	1.14	547.05	1,594.23	108.4%	-65.7%	709	708

Showing 50 of 14036 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_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_3_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: 16347 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
6,650,880,607,858,420,237	2905	Inpatient	4.95	1	89,097.37	11,246.22	392.8%	692.2%	1,551	132
4,911,047,023,988,898,996	2128	Outpatient	7.05	1.44	25,499.33	6,955.57	390.3%	266.6%	6,068	6,071
4,911,047,023,988,898,996	2217	Outpatient	7.05	1.44	25,490.34	6,959.40	389.9%	266.3%	6,072	6,072
4,911,047,023,988,898,996	2215	Outpatient	7.04	1.44	25,467.61	6,948.52	389.1%	266.5%	6,075	6,077
-3,776,001,016,975,145,508	2128	Outpatient	7.15	1.53	25,741.19	7,087.61	365.7%	263.2%	6,184	6,172
-3,776,001,016,975,145,508	2217	Outpatient	7.07	1.54	25,351.66	6,975.72	359.8%	263.4%	6,273	6,254
-3,776,001,016,975,145,508	2215	Outpatient	6.98	1.54	24,879.29	6,832.80	353.2%	264.1%	6,381	6,350
4,281,965,180,796,802,413	7228	Inpatient	4.51	1.04	63,274.81	23,310.85	333.7%	171.4%	1,275	19
-2,707,949,347,815,340,394	5979	Inpatient	4.34	1.12	79,404.42	9,633.72	289.2%	724.2%	700	16
-6,096,679,287,704,990,848	5979	Inpatient	4.34	1.12	79,404.42	9,633.72	289.2%	724.2%	700	16
8,361,580,493,441,765,265	8701	Inpatient	5.33	1.4	101,169.01	18,207.01	281.0%	455.7%	1,099	133
4,281,965,180,796,802,413	8701	Inpatient	5.33	1.4	101,169.01	18,207.01	281.0%	455.7%	1,099	133
4,281,965,180,796,802,413	4294	Inpatient	4.54	1.2	56,654.22	11,523.80	276.7%	391.6%	59	252
8,361,580,493,441,765,265	4294	Inpatient	4.54	1.2	56,654.22	11,523.80	276.7%	391.6%	59	252
-137,228,703,119,221,502	5936	Inpatient	3.79	1.03	60,800.44	22,199.60	267.8%	173.9%	336	86
-2,162,498,661,799,343,237	6212	Inpatient	3.36	1.01	59,730.16	17,435.10	232.3%	242.6%	1,716	1,716
-6,096,679,287,704,990,848	6005	Inpatient	4.22	1.27	70,098.48	9,622.01	231.0%	628.5%	1,647	25
-2,707,949,347,815,340,394	6005	Inpatient	4.22	1.27	70,098.48	9,622.01	231.0%	628.5%	1,647	25
4,281,965,180,796,802,413	1895	Inpatient	4.34	1.32	65,798.57	19,741.17	228.0%	233.3%	1,701	839
-137,228,703,119,221,502	5905	Inpatient	3.54	1.11	56,419.32	19,938.87	218.0%	183.0%	1,213	387
-2,707,949,347,815,340,394	6025	Inpatient	4.72	1.51	71,339.31	11,378.58	212.5%	527.0%	1,656	69
-6,096,679,287,704,990,848	6025	Inpatient	4.72	1.51	71,339.31	11,378.58	212.5%	527.0%	1,656	69
8,361,580,493,441,765,265	3882	Inpatient	3.65	1.17	62,155.03	18,417.72	212.4%	237.5%	1,546	22
4,281,965,180,796,802,413	3882	Inpatient	3.65	1.17	62,155.03	18,417.72	212.4%	237.5%	1,546	22
861,259,371,462,427,559	-1493008543685875813	Professional	3.29	1.06	4,944.90	6,512.10	211.5%	-24.1%	560	662
-6,096,679,287,704,990,848	5986	Inpatient	4.3	1.38	69,174.27	12,688.27	210.8%	445.2%	1,630	16
-2,707,949,347,815,340,394	5986	Inpatient	4.3	1.38	69,174.27	12,688.27	210.8%	445.2%	1,630	16
8,361,580,493,441,765,265	-1612902388498009527	Professional	3.41	1.1	1,256.43	405.23	210.2%	210.1%	3,597	660
-2,707,949,347,815,340,394	5987	Inpatient	4.36	1.42	74,986.50	12,335.13	207.0%	507.9%	1,628	77
-6,096,679,287,704,990,848	5987	Inpatient	4.36	1.42	74,986.50	12,335.13	207.0%	507.9%	1,628	77
3,133,716,806,509,821,992	1659	Inpatient	3.29	1.07	64,801.15	18,746.73	206.6%	245.7%	731	56
-7,419,059,302,917,965,819	365	Inpatient	5.63	1.85	89,699.28	21,657.81	203.8%	314.2%	1,548	117
4,281,965,180,796,802,413	-1612902388498009527	Professional	3.31	1.1	1,172.22	405.23	201.8%	189.3%	3,398	660
8,361,580,493,441,765,265	284	Inpatient	5.11	1.71	68,718.35	18,443.89	199.1%	272.6%	79	198
4,281,965,180,796,802,413	284	Inpatient	5.1	1.71	68,662.16	18,482.75	198.0%	271.5%	79	198
3,133,716,806,509,821,992	1698	Inpatient	3.19	1.08	63,124.68	22,848.96	194.6%	176.3%	1,645	427
6,650,880,607,858,420,237	2985	Outpatient	3.33	1.13	13,383.30	23,160.81	193.9%	-42.2%	4,858	1,219
2,119,715,416,692,563,737	969	Outpatient	3.77	1.29	14,609.02	987.82	191.7%	1378.9%	5,645	2,922
4,281,965,180,796,802,413	10111	Inpatient	4.65	1.6	67,934.72	24,586.33	190.9%	176.3%	1,656	1,361
3,133,716,806,509,821,992	1712	Inpatient	2.98	1.03	62,635.53	26,405.26	189.7%	137.2%	1,648	106
3,973,810,847,438,814,798	2009	Inpatient	3.38	1.17	67,972.06	23,895.61	188.9%	184.5%	1,667	1,453
-6,096,679,287,704,990,848	5978	Inpatient	3.89	1.36	68,219.15	12,572.37	186.8%	442.6%	1,635	99
-2,707,949,347,815,340,394	5978	Inpatient	3.89	1.36	68,219.15	12,572.37	186.8%	442.6%	1,635	99
3,133,716,806,509,821,992	1746	Inpatient	3.09	1.08	62,393.49	13,679.34	186.1%	356.1%	1,607	12
311,448,953,162,816,591	430	Inpatient	4.07	1.43	58,306.65	25,184.34	184.4%	131.5%	1,686	1,592
4,281,965,180,796,802,413	147	Inpatient	4.49	1.58	69,178.94	25,051.51	183.4%	176.1%	1,664	1,421
4,281,965,180,796,802,413	5132	Inpatient	5.16	1.82	82,157.41	32,078.67	183.1%	156.1%	1,695	1,656
-2,162,498,661,799,343,237	1698	Inpatient	2.99	1.06	59,192.75	8,937.35	183.0%	562.3%	1,633	16
-3,776,001,016,975,145,508	1842	Inpatient	3.35	1.19	48,581.75	13,695.82	180.2%	254.7%	1,551	433
-2,162,498,661,799,343,237	3260	Inpatient	3.99	1.42	60,807.76	15,605.64	179.9%	289.7%	1,451	47

Showing 50 of 16347 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_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_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
)
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: 16 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,816,084,628,491,357,618	3.13	2.53	16,519.62	10,763.00	23.8%	53.5%	922,619	1,476,044
-2,510,676,791,703,302,876	2.99	2.44	16,442.38	10,296.64	22.9%	59.7%	959,871	1,618,289
6,638,296,964,362,884,623	2.4	2.76	7,209.04	8,947.62	-13.3%	-19.4%	203,991	163,296
-3,954,532,594,486,231,313	2.81	3.12	13,627.55	14,237.95	-10.1%	-4.3%	831,878	795,347
-4,445,518,905,666,343,797	1.96	2.16	12,927.20	18,337.46	-9.1%	-29.5%	183,394	183,602
-7,160,850,762,653,242,095	2.39	2.6	8,932.59	9,974.14	-8.1%	-10.4%	365,852	328,694
5,170,047,179,706,197,777	2.51	2.72	9,366.86	10,437.76	-7.8%	-10.3%	381,214	342,537
6,355,182,945,120,798,897	2.06	2.23	6,857.47	7,726.09	-7.4%	-11.2%	370,273	335,159
-1,275,781,459,639,371,202	2.16	2.28	4,011.34	4,872.94	-5.5%	-17.7%	111,163	87,457
-7,572,382,112,705,938,029	2.05	2.16	5,717.30	6,554.15	-5.3%	-12.8%	454,902	411,394
5,781,103,079,387,890,636	1.67	1.73	3,495.61	4,310.34	-3.1%	-18.9%	1,255,919	1,214,529
-2,707,949,347,815,340,394	2	2.06	13,472.27	11,285.34	-3.1%	19.4%	141,279	88,950
-1,016,849,053,752,795,150	2.73	2.81	10,687.87	9,662.00	-3.0%	10.6%	422,507	387,166
-6,096,679,287,704,990,848	1.95	1.98	12,220.94	9,654.13	-1.2%	26.6%	176,418	126,401
3,376,187,909,306,580,799	1.92	1.93	3,288.01	3,732.38	-0.7%	-11.9%	1,170,359	1,157,073
7,247,945,408,878,586,409	2.24	2.23	6,261.16	5,630.34	0.4%	11.2%	1,006,684	1,001,902

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

service_line	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Oncology	10,160,822	8,590,032	1,570,790	18.3%
Diabetes	201,119	170,918	30,201	17.7%
Lab/Path	33,724,056	29,141,192	4,582,864	15.7%
DME and Supplies	1,699,812	1,475,031	224,781	15.2%

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

provider_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Laboratory	6,467,877	3,535,461	2,932,416	82.9%
Imaging Center	1,412,861	2,980,187	-1,567,326	-52.6%
Physician Group	165,994,574	150,471,614	15,522,960	10.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_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_3_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: 15 categories with significant ROID count changes

state	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
SD	1,818,707	1,379,892	438,815	31.8%
MN	8,840,289	6,927,143	1,913,146	27.6%
MO	8,975,186	7,060,104	1,915,082	27.1%
DE	856,825	705,421	151,404	21.5%
WI	8,750,752	7,272,323	1,478,429	20.3%
NE	3,525,967	2,954,837	571,130	19.3%
IA	5,641,967	4,763,064	878,903	18.5%
ND	1,273,954	1,080,809	193,145	17.9%
KS	5,597,238	4,748,811	848,427	17.9%
OK	6,344,603	5,397,593	947,010	17.5%
AK	569,406	504,206	65,200	12.9%
IL	13,433,418	11,965,898	1,467,520	12.3%
VT	475,581	423,941	51,640	12.2%
MD	4,329,704	3,902,091	427,613	11.0%
UT	1,951,852	1,763,309	188,543	10.7%

Click to see SQL

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

payer_network_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
SelectHealth Value/Care Tiered POS - UT	141,279	88,950	52,329	58.8%
Geisinger PPO	959,871	1,618,289	-658,418	-40.7%
SelectHealth HMO	176,418	126,401	50,017	39.6%
Geisinger HMO	922,619	1,476,044	-553,425	-37.5%
Blue Cross Blue Shield of Vermont PPO	111,163	87,457	23,706	27.1%
Aetna DE HMO	203,991	163,296	40,695	24.9%
Health Alliance Plan (HAP) PPO	1,298,642	1,070,367	228,275	21.3%
Health Alliance Plan (HAP) HMO	1,162,550	959,220	203,330	21.2%
Blue Cross Blue Shield of Kansas City Preferredcare Blue	239,472	203,940	35,532	17.4%
Blue Cross Blue Shield of South Carolina HMO	269,193	230,165	39,028	17.0%
Cigna TN HMO	802,684	691,175	111,509	16.1%
Blue Cross Blue Shield of Maryland & DC (CareFirst) Preferred Provider Network (PPO)	1,048,820	914,312	134,508	14.7%
Quartz Quartz Tiered Choice Plus (QHBPC WI): Quartz	455,857	403,800	52,057	12.9%
Blue Cross Blue Shield of New Mexico HMO	316,578	280,795	35,783	12.7%
Blue Cross Blue Shield of New Mexico Preferred Provider Organization PPO	314,886	279,803	35,083	12.5%
Blue Cross Blue Shield of Pennsylvania (Capital Blue Cross) PPO	769,816	688,804	81,012	11.8%
Anthem ME HMO	365,852	328,694	37,158	11.3%
Anthem ME Blue Choice PPO	381,214	342,537	38,677	11.3%
Wellmark Blue Cross Blue Shield SD PPO	312,284	282,325	29,959	10.6%
Aetna NV HMO	454,902	411,394	43,508	10.6%
CDPHP (Capital District Physicians Health Plan) HMO	385,662	348,791	36,871	10.6%
Anthem NH HMO	370,273	335,159	35,114	10.5%
Blue Cross Blue Shield of Maryland & DC (CareFirst) HMO	730,962	662,036	68,926	10.4%

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_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_3_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
458	Blue Cross Blue Shield of North Dakota	PPO	imputation	89.00	102
397	Blue Cross Blue Shield of Rhode Island	PPO	imputation	50.00	134
628	Premera Blue Cross	Alaska Heritage Network	imputation	534.00	543
53	Blue Cross Blue Shield of Oklahoma	Blue Preferred PPO	imputation	625.00	671
121	HealthPartners	Open Access	imputation		1,051
462	Blue Cross Blue Shield of Vermont	PPO	imputation	1,048.00	1,206
42	Anthem	NH HMO	imputation	586.00	1,307
42	Anthem	ME HMO	imputation	2,943.00	2,041
770	Mass General Brigham Health Plan	HMO	hospital	2,058.00	2,058
76	Cigna	GA HMO	payer	2,983.00	2,356

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_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_3_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: 116 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	MelroseWakefield Healthcare	125,845	78,578	47,267	60.2%
Hospital	University Health	167,031	111,440	55,591	49.9%
Hospital	HealthPartners	212,332	145,428	66,904	46.0%
Hospital	ThedaCare	369,754	257,766	111,988	43.4%
Hospital	The MetroHealth System	101,582	71,591	29,991	41.9%
Physician Group	Avera Health	216,618	153,461	63,157	41.2%
Hospital	North Memorial Health	128,299	94,416	33,883	35.9%
Hospital	Presbyterian Healthcare Services	290,148	213,925	76,223	35.6%
Hospital	Sarah Bush Lincoln Health System	109,463	80,781	28,682	35.5%
Hospital	Park Nicollet Health Services	110,908	81,972	28,936	35.3%
Hospital	HCA Gulf Coast Division - HCA Healthcare Corpus Christi Medical Center	165,384	123,593	41,791	33.8%
Hospital	SSM Health	563,171	421,347	141,824	33.7%
Hospital	Saint Francis Healthcare	116,042	87,248	28,794	33.0%
Hospital	Ascension Saint Thomas	316,539	241,788	74,751	30.9%
Physician Group	CentraCare Health System	169,573	129,587	39,986	30.9%
Hospital	Memorial Health	178,839	137,146	41,693	30.4%
Hospital	SSM Health in Illinois	188,963	145,413	43,550	29.9%
Physician Group	Gundersen Health System	248,007	191,124	56,883	29.8%
Physician Group	WellSpan Health	120,093	166,513	-46,420	-27.9%
Hospital	Allina Health	529,213	415,283	113,930	27.4%
Physician Group	Penn Medicine Lancaster General Health	93,540	128,196	-34,656	-27.0%
Hospital	FirstHealth of the Carolinas	127,752	100,777	26,975	26.8%
Physician Group	University of Pittsburgh Medical Center	81,733	111,207	-29,474	-26.5%
Hospital	M Health Fairview	583,453	462,518	120,935	26.1%
Hospital	Inova	592,268	473,274	118,994	25.1%
Hospital	Childrens Minnesota	102,442	81,956	20,486	25.0%
Hospital	Froedtert & the Medical College of Wisconsin	389,041	312,221	76,820	24.6%
Hospital	BJC HealthCare	634,329	510,810	123,519	24.2%
Hospital	Freeman Health System	192,191	154,923	37,268	24.1%
Hospital	Medical Facilities Corporation	120,971	97,637	23,334	23.9%
Physician Group	UPMC Central Pennsylvania	165,610	217,415	-51,805	-23.8%
Hospital	Bellin Health	100,513	81,174	19,339	23.8%
Physician Group	Bryan Health	100,673	81,310	19,363	23.8%
Hospital	HCA Central & West Texas Division - St Davids HealthCare	487,364	393,713	93,651	23.8%
Hospital	Sinai Chicago	119,239	96,668	22,571	23.3%
Hospital	Ascension Wisconsin	686,980	557,780	129,200	23.2%
Hospital	University of Wisconsin Health	177,257	144,146	33,111	23.0%
Hospital	El Paso County Hospital District	147,612	120,120	27,492	22.9%
Hospital	CentraCare Health System	312,240	254,864	57,376	22.5%
Physician Group	Lehigh Valley Health Network	166,744	214,463	-47,719	-22.3%
Hospital	Ascension St John Health System	264,930	217,130	47,800	22.0%
Physician Group	Saint Francis Health System	122,093	100,077	22,016	22.0%
Hospital	Ridgeview Medical Center Health System	140,578	116,280	24,298	20.9%
Physician Group	CoxHealth	131,687	108,952	22,735	20.9%
Hospital	The University of Kansas Health System	186,564	155,280	31,284	20.1%
Physician Group	Saint Lukes Health System	126,885	105,730	21,155	20.0%
Hospital	UnityPoint Health Trinity	148,355	123,751	24,604	19.9%
Hospital	HCA Mountain Division - MountainStar Healthcare	315,952	263,555	52,397	19.9%
Hospital	Cottage Health	180,033	150,281	29,752	19.8%
Hospital	Mercy	1,614,924	1,349,867	265,057	19.6%

Showing 50 of 116 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_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_3_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: 1 categories with significant ROID count changes

canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
2	47,443,391	42,035,969	5,407,422	12.9%

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

provider_type	canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Imaging Center	2	46	99	-53	-53.5%
Imaging Center	4	1,412,619	2,979,728	-1,567,109	-52.6%
Imaging Center	3	196	360	-164	-45.6%
Laboratory	2	928,028	484,392	443,636	91.6%
Laboratory	3	2,084,650	1,102,616	982,034	89.1%
Laboratory	5	3,131,975	1,725,375	1,406,600	81.5%
Laboratory	4	323,224	223,078	100,146	44.9%
Physician Group	5	92,342	929,642	-837,300	-90.1%
Physician Group	4	2,318,382	1,265,107	1,053,275	83.3%
Physician Group	2	31,575,903	27,164,668	4,411,235	16.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_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_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
)
SELECT
    provider_type,
    canonical_rate_score,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count,
    (new.roid_count - old.roid_count) AS roid_count_change,
    (new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0) AS roid_count_pct_change
FROM new
JOIN old USING (provider_type, canonical_rate_score)
WHERE ABS((new.roid_count - old.roid_count) * 1.0 / NULLIF(old.roid_count, 0)) > 0.1
ORDER BY provider_type, ABS(roid_count_pct_change) DESC

By Canonical Rate Class

Count: 0 categories with significant ROID count changes

No significant ROID count swings detected.

Click to see SQL

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

No significant ROID count swings detected.

Click to see SQL

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

canonical_contract_methodology	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Per Diem	3,467,441	4,148,724	-681,283	-16.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_3_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_3_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: 2 categories with significant ROID count changes

canonical_crosswalk_method	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Crosswalked From NDC to HCPCS	1,031,701	841,012	190,689	22.7%
Crosswalked From RC-FAMILY to MS-DRG	675,772	752,632	-76,860	-10.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_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_3_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: 0 categories with significant ROID count changes

No significant ROID count swings detected.

Click to see SQL

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

---