v2_3_1 → v2_3_2

Generated: 2026-01-21 07:28:00.
Filter: canonical_rate_score > 1.

---

1. Summary Statistics

Overview

• Total ROIDs: 402,677,568 → 423,207,546 (+20,529,978, +5.1%)

By Provider Type and Bill Type

provider_type	bill_type	new_distinct_roids	old_distinct_roids	new_distinct_networks	old_distinct_networks	new_distinct_providers	old_distinct_providers	new_distinct_billing_codes	old_distinct_billing_codes
Hospital	Outpatient	182,614,542	170,254,264	176	174	5,702	5,663	7,512	7,084
Imaging Center	Professional	2,967,765	1,412,861	159	153	2,898	2,848	171	164
Physician Group	Professional	167,535,830	165,994,574	174	175	21,157	20,774	3,790	3,776
Laboratory	Professional	11,292,016	6,467,877	167	154	3,710	814	916	913
Hospital	Inpatient	44,327,982	43,783,188	173	171	5,622	5,606	1,717	1,717
ASC	Outpatient	14,469,411	14,764,804	137	146	5,533	5,581	3,154	3,361

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_2.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_1.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: 117 dropped

Click to see SQL

WITH
new AS (
    SELECT DISTINCT provider_id, 'new' as source
    FROM tq_dev.internal_dev_csong_cld_v2_3_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
),
old AS (
    SELECT DISTINCT provider_id, 'old' 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'
)
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_2.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_1.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_2.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_1.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: 7113 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_2.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_1.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: 2 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_2.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_1.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: 5696 entities with significant rate changes

bill_type	provider_id	new_avg_pct_of_medicare_rate	old_avg_pct_of_medicare_rate	new_avg_canonical_rate	old_avg_canonical_rate	medicare_rate_pct_change	canonical_rate_pct_change	new_roid_count	old_roid_count
Outpatient	25283	4.87	1.42	8,533.16	1,549.00	242.4%	450.9%	7,651	4,119
Outpatient	10353	3.46	1.1	7,343.24	3,385.59	214.1%	116.9%	34,415	693
Professional	-7080430866273003041	2.96	1.08	247.23	276.18	174.5%	-10.5%	2,146	1,237
Outpatient	10415	2.97	1.1	6,193.19	2,931.25	169.8%	111.3%	8,869	1,234
Outpatient	3989	3.08	1.22	16,435.83	16,011.58	153.6%	2.6%	8,296	4,591
Outpatient	29047	5.68	2.27	1,929.56	552.78	150.3%	249.1%	5,844	4,947
Professional	5945249212552812513	1.82	0.75	143.80	122.18	141.8%	17.7%	3,860	2,568
Professional	7101987961289562706	2.01	0.92	226.50	209.31	118.8%	8.2%	1,335	2,861
Inpatient	5713	3.55	1.63	58,059.44	25,771.29	117.4%	125.3%	4,666	3,601
Professional	-5521614871150813844	3.49	1.62	1,969.29	4,276.62	115.4%	-54.0%	3,926	1,679
Outpatient	112	3.37	1.68	7,800.52	3,143.29	100.5%	148.2%	43,400	32,609
Outpatient	7060	3.37	1.7	7,796.61	3,098.48	98.0%	151.6%	43,422	29,814
Professional	1984085314987193632	2.46	1.24	209.03	273.37	97.7%	-23.5%	1,160	543
Professional	8391458242198855952	3.58	1.82	224.98	236.84	97.1%	-5.0%	3,724	1,951
Inpatient	6136	2.63	1.34	45,452.46	24,702.46	96.3%	84.0%	16,497	16,541
Inpatient	71	3.13	1.63	33,702.76	25,480.70	92.5%	32.3%	1,357	7,498
Inpatient	6296	3.41	1.78	74,173.16	38,501.63	92.2%	92.6%	1,724	1,711
Inpatient	6097	3.14	1.64	62,790.85	32,231.68	91.0%	94.8%	5,853	5,856
Outpatient	3502	3.27	1.72	8,052.01	1,972.02	89.9%	308.3%	17,308	10,168
Professional	2096255546480210986	2.26	1.19	206.22	316.92	89.3%	-34.9%	2,344	932
Professional	1710388323623340094	1.88	1.01	178.75	248.25	86.8%	-28.0%	1,269	720
Outpatient	2848	3.21	1.74	6,808.93	2,752.86	84.4%	147.3%	50,556	33,159
Outpatient	26971	1.79	0.97	1,080.45	1,115.02	84.0%	-3.1%	6,797	2,790
Professional	7956032213241034101	2.11	1.15	218.95	247.47	83.5%	-11.5%	5,152	1,158
Professional	-3653175018401984261	2.41	1.31	198.74	243.90	83.2%	-18.5%	1,250	632
Professional	8652806864076797195	2.93	1.62	252.82	367.34	80.7%	-31.2%	1,259	784
Inpatient	2809	2.21	1.23	41,360.50	22,607.07	79.9%	83.0%	14,535	9,961
Outpatient	4679	1.95	1.09	3,166.92	626.24	79.1%	405.7%	6,136	2,365
Outpatient	10285	2.36	1.36	6,518.74	16,615.24	74.0%	-60.8%	24,012	3,366
Professional	-3093032227109272327	2.13	1.23	183.41	251.55	73.0%	-27.1%	1,166	559
Outpatient	3663	2.92	1.71	7,109.02	3,183.75	71.2%	123.3%	20,837	16,666
Outpatient	6736	2.43	1.42	3,377.01	3,279.90	70.6%	3.0%	7,713	5,593
Outpatient	556	3.66	2.15	5,052.46	5,788.79	70.4%	-12.7%	21,230	17,045
Outpatient	21410	1.7	1	916.08	2,149.84	69.6%	-57.4%	3,603	840
Inpatient	10017	2.43	1.44	28,131.62	17,066.29	68.9%	64.8%	18	15
Outpatient	22363	1.68	1	867.32	2,044.71	68.2%	-57.6%	3,876	840
Outpatient	20646	1.68	1	867.32	2,044.71	68.2%	-57.6%	3,876	840
Professional	-5794386464681647556	3.04	1.83	213.61	237.54	66.2%	-10.1%	3,724	1,947
Outpatient	2764	2.81	1.7	6,637.19	3,312.68	64.6%	100.4%	46,736	35,858
Professional	-4361148986180362420	2.36	1.44	183.22	207.63	64.2%	-11.8%	1,394	776
Professional	3721368643237137830	3.35	2.06	181.98	157.75	62.5%	15.4%	2,182	5,305
Outpatient	2785	3.46	2.14	7,386.18	3,023.80	61.8%	144.3%	49,631	32,455
Professional	-3888508530636608114	1.86	1.15	145.12	153.94	61.6%	-5.7%	1,826	1,212
Inpatient	901	1.42	3.66	26,936.51	49,625.79	-61.1%	-45.7%	338	66
Outpatient	30683	3.64	2.27	4,068.87	3,218.72	60.0%	26.4%	11,265	4,725
Professional	-4429607968028956964	1.32	3.28	95.96	385.74	-59.8%	-75.1%	4,631	751
Professional	8068535777068899699	3.65	2.29	473.00	1,101.57	59.3%	-57.1%	1,135	2,367
Professional	-9172301467933811537	3.65	2.29	473.00	1,101.57	59.3%	-57.1%	1,135	2,367
Outpatient	31175	1.46	3.57	2,417.98	4,138.75	-59.0%	-41.6%	6,288	9,425
Inpatient	6667	3.5	2.21	66,522.85	43,372.88	58.9%	53.4%	17,547	17,889

Showing 50 of 5696 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_2.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_1.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: 18616 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
-2,162,498,661,799,343,237	29047	12.15	1.67	4,629.82	513.78	629.7%	801.1%	1,962	863
8,361,580,493,441,765,265	389	13.33	2.55	48,267.82	40,830.53	423.1%	18.2%	5,996	1,716
4,281,965,180,796,802,413	389	13.33	2.55	48,267.82	40,830.53	423.1%	18.2%	5,996	1,716
-8,822,629,802,732,873,104	7888860944521452451	4.06	1.05	306.10	284.00	288.7%	7.8%	1,079	516
-6,486,550,968,171,266,793	6902590622312144885	3.61	0.93	250.18	249.31	286.7%	0.4%	1,133	513
-3,776,001,016,975,145,508	7888860944521452451	3.75	1.06	332.42	278.40	255.2%	19.4%	1,357	609
8,611,629,356,743,080,984	3200007526543752952	4.25	1.21	2,293.04	1,076.82	252.4%	112.9%	3,341	3,590
-2,162,498,661,799,343,237	-8853030393023245808	4.29	1.22	2,311.79	1,081.81	252.3%	113.7%	3,275	3,564
8,611,629,356,743,080,984	503110447049703680	4.25	1.21	2,293.40	1,075.52	252.2%	113.2%	3,341	3,581
-2,162,498,661,799,343,237	-6890109099400593646	4.3	1.22	2,311.20	1,091.68	252.1%	111.7%	3,267	3,487
8,611,629,356,743,080,984	7425666071617328601	4.25	1.21	2,293.40	1,078.01	250.9%	112.7%	3,341	3,579
8,611,629,356,743,080,984	-5592630048914623792	4.25	1.21	2,293.40	1,078.02	250.8%	112.7%	3,341	3,579
-3,776,001,016,975,145,508	6902590622312144885	3.33	0.96	247.55	229.38	247.5%	7.9%	1,287	730
-2,162,498,661,799,343,237	-5620104653890955160	4.3	1.24	2,312.53	1,095.43	246.4%	111.1%	3,272	3,498
8,611,629,356,743,080,984	-6890109099400593646	4.3	1.25	2,313.41	1,105.92	244.6%	109.2%	3,262	3,482
8,611,629,356,743,080,984	5774646275367855843	4.21	1.23	2,277.68	1,081.51	243.8%	110.6%	3,346	3,579
-2,162,498,661,799,343,237	3200007526543752952	4.27	1.24	2,300.11	1,097.42	243.2%	109.6%	3,284	3,583
8,611,629,356,743,080,984	-8853030393023245808	4.28	1.25	2,311.35	1,096.57	243.0%	110.8%	3,337	3,579
-2,162,498,661,799,343,237	-7679431089154185281	4.3	1.26	2,311.76	1,097.86	242.6%	110.6%	3,267	3,491
8,611,629,356,743,080,984	3837405551423477080	4.26	1.25	2,293.68	1,088.60	241.8%	110.7%	3,341	3,578
8,611,629,356,743,080,984	-5620104653890955160	4.3	1.26	2,314.75	1,104.27	241.4%	109.6%	3,267	3,497
-2,162,498,661,799,343,237	6745681840829850181	4.3	1.26	2,311.21	1,097.83	240.9%	110.5%	3,267	3,495
-2,162,498,661,799,343,237	503110447049703680	4.27	1.25	2,299.01	1,099.76	240.8%	109.0%	3,279	3,570
-2,162,498,661,799,343,237	-5592630048914623792	4.27	1.25	2,301.81	1,103.65	240.6%	108.6%	3,275	3,563
-2,162,498,661,799,343,237	7425666071617328601	4.27	1.26	2,301.80	1,103.92	239.8%	108.5%	3,275	3,563
8,611,629,356,743,080,984	-1356683603212222664	4.22	1.25	2,299.23	1,103.02	237.6%	108.4%	3,267	3,490
8,611,629,356,743,080,984	-5294679320285090039	4.2	1.25	2,296.49	1,102.96	237.1%	108.2%	3,267	3,490
-2,162,498,661,799,343,237	-5294679320285090039	4.19	1.25	2,292.90	1,090.04	236.1%	110.3%	3,272	3,553
8,611,629,356,743,080,984	6745681840829850181	4.3	1.28	2,313.45	1,107.40	235.8%	108.9%	3,262	3,497
-2,162,498,661,799,343,237	1268700271607267425	4.3	1.28	2,311.43	1,105.47	234.9%	109.1%	3,267	3,492
-2,162,498,661,799,343,237	5774646275367855843	4.27	1.28	2,301.31	1,104.89	234.8%	108.3%	3,276	3,565
8,611,629,356,743,080,984	1268700271607267425	4.3	1.29	2,312.19	1,111.66	232.4%	108.0%	3,262	3,526
-2,162,498,661,799,343,237	3837405551423477080	4.27	1.29	2,299.49	1,109.58	232.4%	107.2%	3,279	3,567
8,611,629,356,743,080,984	-7679431089154185281	4.25	1.28	2,286.13	1,108.69	232.0%	106.2%	3,275	3,488
8,611,629,356,743,080,984	-5226014834472007176	4.21	1.27	2,281.92	1,094.41	231.7%	108.5%	3,349	3,630
6,437,583,333,460,157,798	2848	4.73	1.43	17,957.53	11,200.09	229.7%	60.3%	7,227	3,463
-2,162,498,661,799,343,237	-571561603290130424	4.3	1.31	2,311.74	1,126.04	229.0%	105.3%	3,267	3,502
-2,162,498,661,799,343,237	22312	4.71	1.43	4,623.27	842.39	228.7%	448.8%	2,209	593
-2,162,498,661,799,343,237	3192412462591107926	4.24	1.29	2,287.94	1,105.36	228.1%	107.0%	3,330	3,518
8,611,629,356,743,080,984	-571561603290130424	4.3	1.32	2,313.92	1,135.32	227.0%	103.8%	3,262	3,497
-2,162,498,661,799,343,237	9004615848033782745	4.3	1.32	2,311.30	1,098.86	227.0%	110.3%	3,268	3,490
-6,486,550,968,171,266,793	-9202659629345937969	3.84	1.17	266.76	272.98	226.8%	-2.3%	1,228	665
8,611,629,356,743,080,984	-1068105346343890498	4.27	1.31	2,276.20	1,111.92	225.5%	104.7%	3,270	3,493
-2,162,498,661,799,343,237	-5226014834472007176	4.11	1.26	2,223.19	1,098.54	225.0%	102.4%	3,355	3,676
-2,162,498,661,799,343,237	-1068105346343890498	4.12	1.27	2,267.84	1,096.83	224.3%	106.8%	3,272	3,491
8,611,629,356,743,080,984	9004615848033782745	4.19	1.29	2,270.79	1,096.76	223.7%	107.0%	3,275	3,524
-2,162,498,661,799,343,237	-1356683603212222664	3.98	1.23	2,195.28	1,087.34	222.8%	101.9%	3,290	3,554
8,611,629,356,743,080,984	3192412462591107926	4.25	1.32	2,292.66	1,119.58	222.1%	104.8%	3,265	3,513
6,650,880,607,858,420,237	2987	3.52	1.11	14,002.00	23,325.41	216.7%	-40.0%	4,779	1,211
-3,776,001,016,975,145,508	4055616441813850781	3.11	0.99	233.09	262.17	215.4%	-11.1%	1,118	507

Showing 50 of 18616 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_2.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_1.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: 20700 entities with significant rate changes

network_id	provider_id	bill_type	new_avg_pct_of_medicare_rate	old_avg_pct_of_medicare_rate	new_avg_canonical_rate	old_avg_canonical_rate	medicare_rate_pct_change	canonical_rate_pct_change	new_roid_count	old_roid_count
-2,162,498,661,799,343,237	29047	Outpatient	12.15	1.67	4,629.82	513.78	629.7%	801.1%	1,962	863
-2,215,537,880,835,804,330	2521	Outpatient	4.8	1.06	20,942.30	6,942.04	354.1%	201.7%	2,917	1,104
-6,293,294,949,651,930,973	1903	Outpatient	4.56	1.05	20,269.13	21,778.44	333.1%	-6.9%	2,842	1,214
-7,995,682,717,897,123,039	6613	Inpatient	4.5	1.1	120,091.14	22,618.96	309.3%	430.9%	1,693	116
-4,897,081,641,892,712,607	3669	Outpatient	7.21	1.77	30,987.17	554.42	308.1%	5489.1%	2,071	557
6,437,583,333,460,157,798	2848	Outpatient	5.75	1.43	16,836.41	875.22	301.2%	1823.7%	5,511	1,748
4,281,965,180,796,802,413	6136	Inpatient	4.15	1.04	78,877.26	18,356.76	298.4%	329.7%	1,716	1,716
8,361,580,493,441,765,265	6136	Inpatient	4.15	1.04	78,877.26	18,356.76	298.4%	329.7%	1,716	1,716
1,017,413,876,722,652,424	6438	Inpatient	5.78	1.47	101,928.40	46,123.58	293.3%	121.0%	1,008	110
7,616,836,552,481,822,175	6438	Inpatient	5.78	1.47	101,928.40	46,123.58	293.3%	121.0%	1,008	110
-8,822,629,802,732,873,104	7888860944521452451	Professional	4.06	1.05	306.10	284.00	288.7%	7.8%	1,079	516
-6,486,550,968,171,266,793	6902590622312144885	Professional	3.61	0.93	250.18	249.31	286.7%	0.4%	1,133	513
6,437,583,333,460,157,798	2785	Outpatient	5.44	1.51	16,940.19	1,048.74	259.1%	1515.3%	5,527	1,838
-3,776,001,016,975,145,508	7888860944521452451	Professional	3.75	1.06	332.42	278.40	255.2%	19.4%	1,357	609
8,611,629,356,743,080,984	3200007526543752952	Professional	4.25	1.21	2,293.04	1,076.82	252.4%	112.9%	3,341	3,590
-2,162,498,661,799,343,237	-8853030393023245808	Professional	4.29	1.22	2,311.79	1,081.81	252.3%	113.7%	3,275	3,564
8,611,629,356,743,080,984	503110447049703680	Professional	4.25	1.21	2,293.40	1,075.52	252.2%	113.2%	3,341	3,581
-2,162,498,661,799,343,237	-6890109099400593646	Professional	4.3	1.22	2,311.20	1,091.68	252.1%	111.7%	3,267	3,487
8,611,629,356,743,080,984	7425666071617328601	Professional	4.25	1.21	2,293.40	1,078.01	250.9%	112.7%	3,341	3,579
8,611,629,356,743,080,984	-5592630048914623792	Professional	4.25	1.21	2,293.40	1,078.02	250.8%	112.7%	3,341	3,579
-3,776,001,016,975,145,508	6902590622312144885	Professional	3.33	0.96	247.55	229.38	247.5%	7.9%	1,287	730
-2,162,498,661,799,343,237	-5620104653890955160	Professional	4.3	1.24	2,312.53	1,095.43	246.4%	111.1%	3,272	3,498
8,611,629,356,743,080,984	-6890109099400593646	Professional	4.3	1.25	2,313.41	1,105.92	244.6%	109.2%	3,262	3,482
-7,995,682,717,897,123,039	6647	Inpatient	6.45	1.87	135,463.95	41,746.20	244.4%	224.5%	1,710	1,679
8,611,629,356,743,080,984	5774646275367855843	Professional	4.21	1.23	2,277.68	1,081.51	243.8%	110.6%	3,346	3,579
-2,162,498,661,799,343,237	3200007526543752952	Professional	4.27	1.24	2,300.11	1,097.42	243.2%	109.6%	3,284	3,583
8,611,629,356,743,080,984	-8853030393023245808	Professional	4.28	1.25	2,311.35	1,096.57	243.0%	110.8%	3,337	3,579
-2,162,498,661,799,343,237	-7679431089154185281	Professional	4.3	1.26	2,311.76	1,097.86	242.6%	110.6%	3,267	3,491
8,611,629,356,743,080,984	3837405551423477080	Professional	4.26	1.25	2,293.68	1,088.60	241.8%	110.7%	3,341	3,578
8,611,629,356,743,080,984	-5620104653890955160	Professional	4.3	1.26	2,314.75	1,104.27	241.4%	109.6%	3,267	3,497
-2,162,498,661,799,343,237	6745681840829850181	Professional	4.3	1.26	2,311.21	1,097.83	240.9%	110.5%	3,267	3,495
-2,162,498,661,799,343,237	503110447049703680	Professional	4.27	1.25	2,299.01	1,099.76	240.8%	109.0%	3,279	3,570
-2,162,498,661,799,343,237	-5592630048914623792	Professional	4.27	1.25	2,301.81	1,103.65	240.6%	108.6%	3,275	3,563
-2,162,498,661,799,343,237	7425666071617328601	Professional	4.27	1.26	2,301.80	1,103.92	239.8%	108.5%	3,275	3,563
4,281,965,180,796,802,413	6097	Inpatient	3.91	1.15	81,710.49	22,424.75	239.3%	264.4%	751	751
8,361,580,493,441,765,265	6097	Inpatient	3.91	1.15	81,710.49	22,424.75	239.3%	264.4%	751	751
8,611,629,356,743,080,984	-1356683603212222664	Professional	4.22	1.25	2,299.23	1,103.02	237.6%	108.4%	3,267	3,490
8,611,629,356,743,080,984	-5294679320285090039	Professional	4.2	1.25	2,296.49	1,102.96	237.1%	108.2%	3,267	3,490
-2,162,498,661,799,343,237	-5294679320285090039	Professional	4.19	1.25	2,292.90	1,090.04	236.1%	110.3%	3,272	3,553
8,611,629,356,743,080,984	6745681840829850181	Professional	4.3	1.28	2,313.45	1,107.40	235.8%	108.9%	3,262	3,497
-2,162,498,661,799,343,237	1268700271607267425	Professional	4.3	1.28	2,311.43	1,105.47	234.9%	109.1%	3,267	3,492
-2,162,498,661,799,343,237	5774646275367855843	Professional	4.27	1.28	2,301.31	1,104.89	234.8%	108.3%	3,276	3,565
8,611,629,356,743,080,984	1268700271607267425	Professional	4.3	1.29	2,312.19	1,111.66	232.4%	108.0%	3,262	3,526
-2,162,498,661,799,343,237	3837405551423477080	Professional	4.27	1.29	2,299.49	1,109.58	232.4%	107.2%	3,279	3,567
8,611,629,356,743,080,984	-7679431089154185281	Professional	4.25	1.28	2,286.13	1,108.69	232.0%	106.2%	3,275	3,488
8,611,629,356,743,080,984	-5226014834472007176	Professional	4.21	1.27	2,281.92	1,094.41	231.7%	108.5%	3,349	3,630
-2,162,498,661,799,343,237	-571561603290130424	Professional	4.3	1.31	2,311.74	1,126.04	229.0%	105.3%	3,267	3,502
-2,162,498,661,799,343,237	22312	Outpatient	4.71	1.43	4,623.27	842.39	228.7%	448.8%	2,209	593
-2,162,498,661,799,343,237	3192412462591107926	Professional	4.24	1.29	2,287.94	1,105.36	228.1%	107.0%	3,330	3,518
8,611,629,356,743,080,984	-571561603290130424	Professional	4.3	1.32	2,313.92	1,135.32	227.0%	103.8%	3,262	3,497

Showing 50 of 20700 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_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY network_id, provider_id, bill_type
),
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_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
)
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: 26 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,499,759,224,271,609,810	4.49	2.2	557.87	189.62	104.3%	194.2%	60,033	301,102
1,481,239,756,837,705,768	2.86	2.31	13,598.60	6,293.17	24.0%	116.1%	387,636	853,620
-4,564,247,599,614,740,658	2.51	2.04	10,496.13	5,992.52	23.1%	75.2%	284,644	514,623
-8,403,435,045,225,894,954	2.26	1.88	9,702.43	5,249.34	20.2%	84.8%	214,747	424,317
-8,393,574,886,119,207,149	2.21	2.59	7,193.58	13,566.88	-14.5%	-47.0%	1,229,453	532,171
1,391,056,274,517,336,075	2.73	2.4	10,972.07	9,452.89	14.0%	16.1%	1,025,726	1,076,980
3,376,187,909,306,580,799	2.18	1.92	4,065.46	3,288.01	13.5%	23.6%	837,349	1,170,359
6,037,342,850,284,539,624	2.32	2.08	5,270.25	4,221.73	11.6%	24.8%	929,613	1,279,896
7,900,428,585,805,602,831	1.95	1.76	8,149.40	5,371.64	10.7%	51.7%	108,171	183,252
-2,707,949,347,815,340,394	1.79	2	7,453.26	13,472.27	-10.2%	-44.7%	131,463	141,279
-6,096,679,287,704,990,848	1.79	1.95	7,384.78	12,220.94	-8.4%	-39.6%	167,276	176,418
1,998,605,818,287,672,800	2.73	2.52	9,008.49	6,842.34	8.3%	31.7%	453,152	619,921
-7,995,682,717,897,123,039	2.81	2.65	20,770.12	14,614.32	6.0%	42.1%	3,391,509	5,084,000
554,749,539,877,544,775	2.32	2.22	12,993.39	15,212.58	4.3%	-14.6%	1,401,428	1,075,979
7,627,114,068,308,275,365	1.88	1.81	5,717.96	5,014.66	3.4%	14.0%	425,369	385,662
-1,766,392,260,690,175,685	2.17	2.24	7,453.07	8,340.81	-3.4%	-10.6%	1,519,575	1,408,303
-4,657,040,947,342,706,268	1.9	1.85	5,832.67	5,217.96	2.6%	11.8%	455,154	418,069
5,645,818,248,035,806,861	1.85	1.89	6,668.03	7,553.42	-2.2%	-11.7%	890,402	737,874
5,605,126,640,932,710,930	2.46	2.42	30,486.98	34,133.41	1.8%	-10.7%	276,033	238,231
-6,899,755,675,572,465,411	2.16	2.2	7,221.86	8,299.40	-1.7%	-13.0%	1,368,210	1,237,725
-2,803,535,769,510,843,135	2.2	2.16	11,767.10	13,537.63	1.6%	-13.1%	1,305,270	1,040,850
-3,167,929,429,504,156,609	2.8	2.84	6,466.52	5,833.75	-1.5%	10.8%	256,564	280,730
1,295,090,440,394,638,876	2	2.03	13,256.21	7,290.33	-1.2%	81.8%	230,556	427,284
9,194,373,599,761,516,721	1.76	1.78	7,293.98	8,208.87	-0.7%	-11.1%	273,739	240,549
4,484,998,026,550,298,562	1.74	1.73	7,912.68	8,832.56	0.7%	-10.4%	245,178	216,140
515,753,131,988,643,145	1.96	1.95	7,429.07	6,464.89	0.4%	14.9%	1,621,002	1,480,959

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_2.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_1.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: 5 categories with significant ROID count changes

service_line	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Diabetes	291,627	201,119	90,508	45.0%
Emergency	723,685	612,762	110,923	18.1%
Anesthesia	13,881,771	11,889,296	1,992,475	16.8%
Cardiovascular	20,882,598	18,771,815	2,110,783	11.2%
Lab/Path	37,128,240	33,724,056	3,404,184	10.1%

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

By Provider Type

Count: 2 categories with significant ROID count changes

provider_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Imaging Center	2,967,765	1,412,861	1,554,904	110.1%
Laboratory	11,292,016	6,467,877	4,824,139	74.6%

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_2.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_1.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: 4 categories with significant ROID count changes

state	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
VA	10,956,025	8,731,599	2,224,426	25.5%
WA	9,629,058	8,437,086	1,191,972	14.1%
UT	1,737,038	1,951,852	-214,814	-11.0%
NC	10,546,835	9,541,539	1,005,296	10.5%

Click to see SQL

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

payer_network_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Regence Blue Cross Blue Shield WA PPO	1,229,453	532,171	697,282	131.0%
HealthSmart Preferred PPO	60,033	301,102	-241,069	-80.1%
Regence Blue Cross Blue Shield OR PPO	387,636	853,620	-465,984	-54.6%
Regence Blue Cross Blue Shield UT PPO	214,747	424,317	-209,570	-49.4%
PreferredOne PPO	230,556	427,284	-196,728	-46.0%
Regence Blue Cross Blue Shield ID PPO	284,644	514,623	-229,979	-44.7%
Kaiser Permanente Kaiser Health Plan (HI)	108,171	183,252	-75,081	-41.0%
Kaiser Permanente Kaiser Health Plan (CA)	3,391,509	5,084,000	-1,692,491	-33.3%
HealthLink PPO	1,401,428	1,075,979	325,449	30.2%
MVP Health Care HMO	837,349	1,170,359	-333,010	-28.5%
MVP Health Care MVP EPO / PPO	929,613	1,279,896	-350,283	-27.4%
Blue Cross Blue Shield of Nebraska Networkblue PPO	453,152	619,921	-166,769	-26.9%
HealthLink HMO	1,305,270	1,040,850	264,420	25.4%
Wellmark Blue Cross Blue Shield IA PPO	890,402	737,874	152,528	20.7%
Blue Cross Blue Shield of Illinois PPO Participating Provider Options	2,197,058	1,860,775	336,283	18.1%
EmblemHealth GHI / Anthem CBP	276,033	238,231	37,802	15.9%
Blue Cross Blue Shield of North Carolina HMO	455,324	393,138	62,186	15.8%
Sanford Health Plan PPO	931,300	809,925	121,375	15.0%
Blue Cross Blue Shield of Hawaii (HMSA) PPO	273,739	240,549	33,190	13.8%
Blue Cross Blue Shield of Hawaii (HMSA) HMO	245,178	216,140	29,038	13.4%
Blue Cross Blue Shield of North Carolina Preferred Provider Network	589,919	520,485	69,434	13.3%
Anthem GA HMO	573,732	508,374	65,358	12.9%
Anthem VA HMO	931,958	831,878	100,080	12.0%
Kaiser Permanente Kaiser Health Plan (CO)	570,426	509,937	60,489	11.9%
Blue Cross Blue Shield of Illinois HMO	904,176	809,715	94,461	11.7%
Cigna GA HMO	456,881	409,500	47,381	11.6%
Premera Blue Cross HMO	721,926	649,340	72,586	11.2%
Highmark Blue Cross Blue Shield PPO	1,133,439	1,022,176	111,263	10.9%
Anthem IN HMO	830,463	750,196	80,267	10.7%
Geisinger PPO	1,062,357	959,871	102,486	10.7%
Aetna MD HMO	1,703,126	1,539,127	163,999	10.7%
Blue Cross Blue Shield of Minnesota HMO	1,368,210	1,237,725	130,485	10.5%
Wellmark Blue Cross Blue Shield HMO	930,369	842,689	87,680	10.4%
Baylor Scott & White Health Plan PPO	1,111,294	1,006,684	104,610	10.4%
CDPHP (Capital District Physicians Health Plan) HMO	425,369	385,662	39,707	10.3%
Aetna TX HMO	2,954,524	2,678,939	275,585	10.3%
Cigna VA HMO	587,833	533,465	54,368	10.2%
Mass General Brigham Health Plan Commercial PPO	193,968	176,041	17,927	10.2%

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

By Network + Canonical Rate Source (Check for New Pairs with 0 Rates)

Count: 10 categories with significant ROID count changes

payer_id	payer_name	network_name	canonical_rate_source	n_old	n_new
397	Blue Cross Blue Shield of Rhode Island	PPO	imputation	134.00	181
780	The Alliance	Comprehensive Network	payer_hospital		204
47	Blue Cross Blue Shield of Kansas City	Preferredcare Blue	payer_hospital		216
168	Blue Cross Blue Shield of South Carolina	HMO	payer_hospital		503
458	Blue Cross Blue Shield of North Dakota	PPO	imputation	102.00	609
76	Cigna	GA HMO	payer_hospital		666
286	MVP Health Care	HMO	payer_hospital		787
728	Sutter Health Plus	PPO	payer_hospital		869
286	MVP Health Care	MVP EPO / PPO	payer_hospital		920
784	UHA Health Insurance	PPO	payer_hospital		938

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_2.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_1.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: 96 categories with significant ROID count changes

provider_type	health_system_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Physician Group	Sentara Health	248,697	171,199	77,498	45.3%
Physician Group	Carilion Clinic	179,941	128,207	51,734	40.4%
Hospital	United Health Services	243,008	173,787	69,221	39.8%
Hospital	Essentia Health	603,399	433,816	169,583	39.1%
Hospital	TMC Health	177,081	129,504	47,577	36.7%
Hospital	Edward-Elmhurst Health	130,967	95,856	35,111	36.6%
Hospital	HCA Capital Division - HCA Virginia Health System - LewisGale Regional Health System	271,562	205,880	65,682	31.9%
Physician Group	Bon Secours Health System	170,183	131,297	38,886	29.6%
Hospital	University of Iowa Health Care	156,143	120,496	35,647	29.6%
Hospital	Virtua Health	350,587	271,010	79,577	29.4%
Hospital	Bon Secours Health System	717,286	557,503	159,783	28.7%
Hospital	UnityPoint Health Trinity	189,951	148,355	41,596	28.0%
Physician Group	Providence Swedish	258,118	203,406	54,712	26.9%
Hospital	NorthShore University Health System	359,959	287,041	72,918	25.4%
Physician Group	Valley Health	188,508	150,476	38,032	25.3%
Hospital	Allegheny Health Network	572,484	461,070	111,414	24.2%
Hospital	VCU Health	305,175	246,000	59,175	24.1%
Hospital	HCA Capital Division - HCA Virginia Health System	634,443	513,417	121,026	23.6%
Hospital	St Peters Health Partners	639,901	519,979	119,922	23.1%
Hospital	Riverside Health System	309,422	252,222	57,200	22.7%
Hospital	Albany MED Health System	581,802	474,628	107,174	22.6%
Hospital	Penn Medicine	581,101	475,912	105,189	22.1%
Hospital	Erlanger Health System	260,136	214,055	46,081	21.5%
Hospital	Rush University System for Health	120,880	100,490	20,390	20.3%
Hospital	University of Texas Medical Branch Health	268,776	224,157	44,619	19.9%
Hospital	University of Rochester Medical Center	447,963	373,878	74,085	19.8%
Hospital	University of Missouri Health Care	216,173	180,425	35,748	19.8%
Hospital	UVA Health System	120,600	100,664	19,936	19.8%
Hospital	The University of Vermont Health Network	346,345	289,527	56,818	19.6%
Hospital	Phoenix Childrens Health System	105,751	88,499	17,252	19.5%
Physician Group	St Lukes Health System	93,892	116,627	-22,735	-19.5%
Hospital	WellSpan Health	502,687	420,788	81,899	19.5%
Physician Group	Northern Light Health	111,597	137,644	-26,047	-18.9%
Hospital	Mohawk Valley Health System	109,221	92,326	16,895	18.3%
Hospital	Advocate Health Care	599,952	508,191	91,761	18.1%
Hospital	Ascension Seton	611,319	519,649	91,670	17.6%
Physician Group	MultiCare Health System	256,264	218,525	37,739	17.3%
Physician Group	Providence Health & Services - Oregon and Southwest Washington	160,182	193,176	-32,994	-17.1%
Hospital	PAM Health	142,327	121,629	20,698	17.0%
Hospital	Ellis Medicine	175,115	149,670	25,445	17.0%
Hospital	Inova	690,063	592,268	97,795	16.5%
Physician Group	Bryan Health	84,162	100,673	-16,511	-16.4%
Hospital	Nemours Childrens Health System	106,185	91,450	14,735	16.1%
Hospital	Loyola University Health System	196,931	169,642	27,289	16.1%
Hospital	Sarasota Memorial Health Care System	100,975	87,058	13,917	16.0%
Hospital	UT Health East Texas	442,099	381,908	60,191	15.8%
Hospital	HCA San Antonio Division	931,191	807,469	123,722	15.3%
Hospital	Sentara Health	880,280	764,519	115,761	15.1%
Hospital	Guthrie Clinic	386,557	336,709	49,848	14.8%
Physician Group	Atrium Health	290,414	253,463	36,951	14.6%

Showing 50 of 96 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_2.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_1.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
5	32,180,456	29,229,020	2,951,436	10.1%

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_2.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_1.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: 9 categories with significant ROID count changes

provider_type	canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Hospital	2	16,641,222	14,767,304	1,873,918	12.7%
Imaging Center	4	2,967,327	1,412,619	1,554,708	110.1%
Imaging Center	2	88	46	42	91.3%
Imaging Center	3	350	196	154	78.6%
Laboratory	3	3,958,375	2,084,650	1,873,725	89.9%
Laboratory	4	588,704	323,224	265,480	82.1%
Laboratory	2	1,581,531	928,028	653,503	70.4%
Laboratory	5	5,163,406	3,131,975	2,031,431	64.9%
Physician Group	5	71,564	92,342	-20,778	-22.5%

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_2.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_1.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: 1 categories with significant ROID count changes

canonical_rate_class	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Impute	71,489,449	63,531,911	7,957,538	12.5%

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_2.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_1.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: 2 categories with significant ROID count changes

canonical_rate_source	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
hospital	62,699,342	72,872,375	-10,173,033	-14.0%
imputation	71,489,449	63,531,911	7,957,538	12.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_3_2.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_1.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
Percent of Total Billed Charges	86,892,463	78,602,193	8,290,270	10.5%

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_2.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_1.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: 1 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,236,443	1,031,701	204,742	19.8%

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

By Canonical Gross Charge Type

Count: 2 categories with significant ROID count changes

canonical_gross_charge_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
komodo_gross_charge_cbsa_median	2,273,604	1,952,578	321,026	16.4%
komodo_gross_charge_state_median	12,010,234	10,847,200	1,163,034	10.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_3_2.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_1.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

---