Version: 2.4

CLD v2.4.2 → v2.4.3 Consistency Report (prod_combined_abridged)

Generated: 2026-04-09 10:09 New: tq_dev.internal_dev_csong_cld_v2_4_3.prod_combined_abridged Old: tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged Filters: canonical_rate_score > 1, network_type != 'NARROW', network_class = 'Commercial'

Summary Statistics

SQL

WITH
new AS (
    SELECT
        provider_type,
        bill_type,
        COUNT(DISTINCT roid) AS distinct_roids,
        COUNT(DISTINCT payer_id) AS distinct_payers,
        COUNT(DISTINCT network_id) AS distinct_networks,
        COUNT(DISTINCT provider_id) AS distinct_providers,
        COUNT(DISTINCT billing_code) AS distinct_billing_codes
    FROM tq_dev.internal_dev_csong_cld_v2_4_3.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY 1,2
),
old AS (
    SELECT
        CASE
            WHEN provider_type LIKE '%Hospital%' THEN 'Hospital'
            ELSE provider_type
        END AS provider_type,
        bill_type,
        COUNT(DISTINCT roid) AS distinct_roids,
        COUNT(DISTINCT payer_id) AS distinct_payers,
        COUNT(DISTINCT network_id) AS distinct_networks,
        COUNT(DISTINCT provider_id) AS distinct_providers,
        COUNT(DISTINCT billing_code) AS distinct_billing_codes
    FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY 1, 2
)
SELECT
    provider_type,
    bill_type,
    new.distinct_roids AS new_distinct_roids,
    old.distinct_roids AS old_distinct_roids,
    new.distinct_networks AS new_distinct_networks,
    old.distinct_networks AS old_distinct_networks,
    new.distinct_providers AS new_distinct_providers,
    old.distinct_providers AS old_distinct_providers,
    new.distinct_billing_codes AS new_distinct_billing_codes,
    old.distinct_billing_codes AS old_distinct_billing_codes
FROM new
JOIN old USING (provider_type, bill_type)

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	13314941	13034316	175	179	3594	3728	933	922
Hospital	Outpatient	228870364	231204220	188	188	6014	6007	7623	7629
Imaging Center	Professional	4227086	4148570	161	170	2925	2943	170	171
Hospital	Inpatient	58031426	59131946	182	181	5950	5976	1711	1711
ASC	Outpatient	46789775	46576542	158	167	5623	5659	3379	3138
Physician Group	Professional	783576510	666271324	182	183	42372	21406	4542	4104

Rate Object Space Dropoffs

Dropped Bill Billing

SQL

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

193 dropped entities

bill_type	billing_code
Professional	J7178
Professional	Q4317
Professional	26440
Professional	Q4343
Professional	35661
Professional	54056
Professional	50715
Professional	32854
Professional	22216
Professional	35556
Professional	35820
Professional	49205
Professional	51595
Professional	J7211
Professional	33990
Professional	G6014
Professional	77014
Professional	J7182
Professional	34201
Professional	J7198
Professional	62141
Professional	27337
Professional	J7177
Professional	32480
Professional	23120
Professional	28430
Professional	26418
Professional	65778
Professional	33641
Professional	J7203
Professional	Q4312
Professional	43633
Professional	28400
Professional	35371
Professional	59618
Professional	G6015
Professional	22015
Professional	28485
Professional	93799
Professional	99466
Professional	49203
Professional	21450
Professional	J7213
Professional	28515
Professional	J7214
Professional	J7181
Professional	J7195
Professional	22212
Professional	91122
Professional	J7183
Professional	J7202
Professional	Q4204
Professional	34705
Professional	21820
Professional	22325
Professional	33979
Professional	35355
Professional	63266
Professional	Q4111
Professional	47600
Professional	93890
Professional	26540
Professional	G6002
Professional	35840
Professional	27590
Professional	38100
Professional	48150
Professional	28470
Professional	59614
Professional	J7205
Professional	28510
Professional	44320
Professional	26480
Professional	22327
Professional	33967
Professional	28490
Professional	J7200
Professional	Q4341
Professional	49204
Professional	J7204
Professional	63200
Professional	J7190
Professional	91120
Professional	G6012
Professional	J7194
Professional	22326
Professional	Q0249
Professional	56405
Professional	28445
Professional	63276
Professional	63081
Professional	61697
Professional	37220
Professional	26951
Professional	37225
Professional	37227
Professional	J7189
Professional	27335
Outpatient	83001
Professional	44050

… 93 more rows truncated

Dropped Networks

SQL

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

No dropped entities.

Dropped Payers

SQL

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

No dropped entities.

Dropped Provider Networks

SQL

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

45,424 dropped entities

provider_id	network_id
-1220013670881656648	4281965180796802413
30695	-2162498661799343237
-8649953576620496228	1017413876722652424
-5526197348992577433	5810723572563874815
-7214636840314214962	4281965180796802413
8332	-2162498661799343237
5668874739860809349	4484133589360821145
-7268344576467116688	2469641715717297819
-4507710200103183873	-7995682717897123039
3341936098892732437	-2162498661799343237
8344015273275393603	-2510676791703302876
-6726888862176413042	4484133589360821145
22855	-3954532594486231313
28085399521615644	8361580493441765265
6351267574953115616	2039367776086447454
8422926631464807926	8361580493441765265
2622	-8973307920338611544
-7518353958206796071	4484133589360821145
-622051519973612167	4484133589360821145
2495722753055424319	8816084628491357618
-7407711646836483704	2469641715717297819
25702	2039367776086447454
22503	-6293294949651930973
1597	2884766606938738180
5998182881423097506	-8499759224271609810
-3326099122440540843	-3776001016975145508
-5991128660378846213	5810723572563874815
-6280435216940295349	-7995682717897123039
-1633641271652094190	-2162498661799343237
-2558493323081511644	-2510676791703302876
7332369780771243870	5373876635451223842
32059	-2162498661799343237
23392	4857361656543545479
7016361244338359039	8361580493441765265
3027	-2162498661799343237
-6620244172783399326	-3776001016975145508
77200	-2162498661799343237
-8523139764919580023	4281965180796802413
4415120937217058635	4484133589360821145
-277122960970244717	1295090440394638876
3307582668393069748	-7995682717897123039
73492	-2162498661799343237
-3922485493939830741	-4541460228936150437
9007838893776122434	-3776001016975145508
8575485474468555479	1295090440394638876
1693	-3776001016975145508
-4823036739330484982	2469641715717297819
-58538621835592835	-2162498661799343237
-7167908770827079214	-3776001016975145508
-7390961216692168704	4377565664656721182
2127752995463255239	-2162498661799343237
4707906299320096948	2469641715717297819
-4504547706900246341	8611629356743080984
24873	-2162498661799343237
-1513646383934814586	2469641715717297819
7892479564694766281	-2162498661799343237
4151252365443874186	2469641715717297819
6883176750926085251	2039367776086447454
3548600002171192353	-2510676791703302876
7842591896311424766	8816084628491357618
6992315452059192209	2469641715717297819
5108913430212984955	-7995682717897123039
8843912253567060373	-2162498661799343237
6384971955390664436	-2162498661799343237
-1528104922632475703	4281965180796802413
-1745459054238730240	-1786307896854006966
-348743377488625080	-2510676791703302876
-2476588139437996382	-2162498661799343237
26507	-7995682717897123039
-7428510725363198264	8361580493441765265
8495683252366120367	6974535101123239131
5379822640726955220	4484133589360821145
3045	4301155135282901848
4780054998046546363	2469641715717297819
6148416616010722780	2469641715717297819
-567388799063654790	5075764097723629059
-8500400727739061549	8361580493441765265
27902	-8973307920338611544
-6675872059165885792	-3776001016975145508
3095	-2162498661799343237
4122925263413897900	8361580493441765265
6427304466799355771	-3776001016975145508
-8976895034922256940	-2162498661799343237
899567676627204644	8361580493441765265
-4131894737507804573	8361580493441765265
70265	-7995682717897123039
5202730557433781976	6650880607858420237
8388038030736101889	-2162498661799343237
926167067493590775	4281965180796802413
-3034389746420753817	-2162498661799343237
1014778408071517932	2469641715717297819
-5834757182145158738	-2162498661799343237
-7711712424482131402	2469641715717297819
-8579051896867077117	4281965180796802413
1182753044507312377	-4463301013711633961
25612	-8973307920338611544
-4246346060534000942	2469641715717297819
-6752922170756593641	3376187909306580799
5999471718236161678	8611629356743080984
70699	-2162498661799343237

… 45,324 more rows truncated

Dropped Providers

SQL

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

3,714 dropped entities

provider_id
-862062797350031162
3712541555032249079
845494334273931984
3968948499618850890
2649397644040254624
-6754054132964984083
5078556158611860717
-769421429077185470
8447107251900441793
-7298428687831682090
-719247182149646933
-8411142206162171861
659891974964894497
-6067389928519635783
-5681008389921883737
-169002171778989240
4402264282278755325
7196759574118652704
-7429306341157200787
4201156802322134178
5156806639409249375
4707906299320096948
5883762516847668205
4012033581502002677
-8567856011115791995
2726568573815160352
9038600880081795402
2715628493010164424
-2815687561096823106
-2583962773948833587
4393329538455005506
3772565865597713098
-5166385227368372970
3471902121948172147
2772959407405871115
-1952086124656830700
1641106211486863060
-6950490715443493496
-7598516263225516184
7231192939504441961
-8706558919831024174
-4930770152559124495
2716311054452193568
6039495850745072729
3787517946731899921
3293430144924826774
7263498743669455337
2407667416533928430
2101220437996684525
-7540027179024006328
-7823123314690326273
-1210241282019775147
4732987798522825812
-8616653821418642761
-6751696510526213120
4304890043796620729
-2165845445613112914
-4085717549920132921
-8846093794321754815
-5917751849408243835
-4066685490816013165
8386197486653245700
7604514510165726948
-2782323481598600787
2621949859845990649
1251114941678786061
601778688595326274
-6878530620364289848
1961229570432111293
2610569081363770926
-8174621888630797054
-5832908799325421494
-938303321242995755
2687427778402164511
27198
-6704598508177933856
-7707747837838820926
-2016709770868425712
2165011592644710424
-8649173594858254288
-6708089758449925862
7887855025460974576
-6537253798919945434
8917325516092318206
4125699177506130380
-6199915825973076522
-8119874976395194244
-7243519153673524483
-8477925080427982280
-2816570198592131084
-4152013639837612475
3099458883831250334
-8554282575886882775
-5434909666955471549
-7241925047989311501
2889700098572313643
-5324335918131663934
2696543080239605299
480418890022404464
-7096747276781392302

… 3,614 more rows truncated

Large Rate Swings (>10%)

Rate Swings by Bill Provider

SQL

WITH
new AS (
    SELECT
        bill_type,
        provider_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_3.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY bill_type, provider_id
),
old AS (
    SELECT
        bill_type,
        provider_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY bill_type, provider_id
)
SELECT
    bill_type,
    provider_id,
    new.avg_pct_of_medicare_rate AS new_avg_pct_of_medicare_rate,
    old.avg_pct_of_medicare_rate AS old_avg_pct_of_medicare_rate,
    new.avg_canonical_rate AS new_avg_canonical_rate,
    old.avg_canonical_rate AS old_avg_canonical_rate,
    (new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0) AS medicare_rate_pct_change,
    (new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0) AS canonical_rate_pct_change,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count
FROM new
JOIN old USING (bill_type, provider_id)
WHERE (
    ABS((new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0)) > 0.1
   OR ABS((new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0)) > 0.1
) AND (
    CASE
        WHEN bill_type = 'Inpatient' 
        THEN new.roid_count > 10 AND old.roid_count > 10
        WHEN bill_type = 'Outpatient' 
        THEN new.roid_count > 500 AND old.roid_count > 500
        WHEN bill_type = 'Professional' 
        THEN new.roid_count > 500 AND old.roid_count > 500
    END
)
ORDER BY ABS(medicare_rate_pct_change) DESC

24,472 entities with >10% rate swing

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	2032	3.70145	0.568696	53946.1	4864	5.50866	10.0909	1914	414
Inpatient	1897	3.11199	0.581111	53115.7	5157.42	4.35524	9.2989	3063	63
Inpatient	8451	4.36385	0.859	104528	5818.41	4.08015	16.9651	1716	20
Outpatient	5217	4.99251	1.12968	1411.85	134.312	3.41943	9.51171	4581	677
Outpatient	9750	4.8213	1.10833	1376.5	111.666	3.35006	11.3269	4696	766
Inpatient	9383	4.85839	1.12706	75255.4	7280.46	3.31068	9.33662	1518	17
Inpatient	6337	3.96494	0.924028	79269.3	12498.8	3.29094	5.34216	2389	1090
Inpatient	6296	2.53283	0.620102	57910.5	7027.83	3.08455	7.24016	2085	393
Inpatient	6342	2.97531	0.73125	70843.6	6609.7	3.0688	9.71813	1744	56
Inpatient	5104	2.39933	0.610909	49306	5263.18	2.92748	8.3681	3362	11
Outpatient	9810	4.38031	1.12743	9619.08	32751.3	2.88523	-0.706299	6379	1247
Inpatient	6338	2.75763	0.727837	60082	10998.6	2.7888	4.46269	3088	1424
Inpatient	3493	2.7664	0.77	42150.2	5058.68	2.59273	7.33225	998	15
Inpatient	1877	2.44	0.730876	39820.5	9852.74	2.33846	3.04156	4679	3195
Outpatient	8812	4.96971	1.53197	1462.6	324.603	2.24401	3.50581	5523	1048
Inpatient	6537	3.18494	0.991112	70334	16291.6	2.2135	3.31718	4004	2257
Inpatient	3772	5.12419	1.59484	82787.2	28075.7	2.21299	1.94871	3893	1767
Outpatient	10310	4.73658	1.53727	1423.91	324.899	2.08116	3.38261	5480	1048
Inpatient	1869	2.36597	0.79764	34761.1	9486.86	1.96621	2.66413	5318	3758
Inpatient	6617	3.32626	1.12521	82812.7	24904.1	1.95612	2.32527	3680	1915
Inpatient	47	3.99584	1.36805	94060.1	20120.2	1.92083	3.6749	1966	277
Inpatient	9485	1.94589	0.671694	30849.5	4773.31	1.89699	5.46292	1094	124
Inpatient	6298	2.068	0.715366	45753.6	5214.13	1.89083	7.77493	1708	41
Inpatient	1879	1.92325	0.667541	33663.1	9858.01	1.8811	2.41479	4402	2892
Inpatient	6586	3.69693	1.31724	95794.6	55362.6	1.80657	0.730312	1706	29
Inpatient	6508	1.89391	0.678218	43082.2	14877.4	1.79248	1.89582	3552	1852
Inpatient	6364	2.58216	0.925332	56580.7	15422.2	1.79053	2.66877	4005	2232
Inpatient	6443	2.7071	0.981892	56946	15580.5	1.75703	2.65495	4523	2669
Inpatient	6405	2.73272	0.991906	54443.6	14803.9	1.75502	2.67766	4899	2949
Inpatient	6311	2.71234	0.997702	57044.8	17007.8	1.71859	2.35404	4539	2681
Inpatient	6682	2.73852	1.04007	57769.1	18231.7	1.63302	2.16861	5105	3235
Inpatient	5649	3.62924	1.38062	52406.1	25151	1.62871	1.08366	1637	8247
Inpatient	8089	2.40423	0.917203	51076.9	14970.1	1.62127	2.41193	4482	2635
Inpatient	6293	5.1724	1.97796	128873	56095	1.61501	1.2974	1676	1709
Inpatient	1873	2.18346	0.850371	29695.9	9671.21	1.56766	2.07055	6007	4314
Inpatient	3746	3.82165	1.5416	61860.5	27438.4	1.47902	1.25453	3915	1767
Inpatient	6614	5.04502	2.04029	131748	56477.8	1.4727	1.33274	1710	1708
Inpatient	6467	1.66545	0.674951	37452.7	14734	1.4675	1.54192	3539	1844
Inpatient	6425	2.18011	0.900769	46269.5	5528.75	1.42028	7.36889	1696	26
Outpatient	1161	3.74885	1.62397	3666.14	6798.3	1.30845	-0.460727	2382	14104
Inpatient	3723	3.56831	1.5529	57737	27574.4	1.29783	1.09386	3912	1767
Professional	4442081333848475326	4.34793	1.90418	119.464	664.639	1.28335	-0.820257	2495	20138
Inpatient	4361	1.78113	0.783768	22675.5	8830.36	1.27252	1.56791	2070	950
Professional	5155980122889976328	3.97559	1.75463	118.509	187.428	1.26577	-0.367709	1862	11523
Professional	-1442591173686347837	3.97559	1.75463	118.509	187.428	1.26577	-0.367709	1862	11523
Inpatient	8039	2.03437	0.898112	42646.2	14658.6	1.26516	1.9093	4478	2633
Inpatient	6355	1.66852	0.736667	42430.8	10760.3	1.26495	2.94328	2183	57
Inpatient	6345	2.62842	1.17681	52674.9	20466.4	1.2335	1.57372	215	766
Outpatient	23706	5.61979	2.52686	1386.45	407.775	1.22402	2.40003	1203	561
Outpatient	6337	7.64022	3.47845	27136.9	14469.5	1.19644	0.875461	6091	6407
Inpatient	6388	2.26129	1.0419	50173.1	19123.8	1.17036	1.6236	4059	2324
Outpatient	8089	4.35895	2.01243	11164.6	2357.57	1.16601	3.73566	8843	5630
Outpatient	5304	2.49206	1.16497	344.279	135.247	1.13916	1.54556	1694	660
Inpatient	6281	2.23868	1.05348	43722.1	21886.8	1.12503	0.997646	3480	1772
Outpatient	6537	4.23789	1.99645	13127.3	737.076	1.12271	16.81	8582	2410
Inpatient	1589	4.09487	1.94146	34912.4	15216.5	1.10917	1.29437	39	878
Professional	1961812991736415595	3.49564	1.68853	90.6354	166.194	1.07022	-0.454642	1394	7587
Professional	2093875748644995085	3.54995	1.71673	91.9235	172.285	1.06786	-0.466447	1402	8064
Professional	-7379138733837873340	3.39988	1.65102	83.3475	167.525	1.05925	-0.502476	1344	7818
Professional	6798483781417602364	3.39988	1.65102	83.3475	167.525	1.05925	-0.502476	1344	7818
Inpatient	3784	3.144	1.53277	51789.3	27269.1	1.05119	0.899194	4105	1767
Inpatient	5221	1.66228	0.812985	27997.1	12347.4	1.04466	1.26744	3912	1360
Professional	8925736803828560415	4.61788	2.26351	123.408	224.992	1.04015	-0.451499	1338	8197
Professional	6445278610301275077	4.61788	2.26351	123.408	224.992	1.04015	-0.451499	1338	8197
Inpatient	6289	1.79953	0.882857	40854.3	5715.51	1.03831	6.14797	1698	21
Outpatient	6364	3.54813	1.75908	10544.5	608.31	1.01703	16.334	9009	3296
Professional	7357004565283061815	4.54759	2.26351	121.54	224.992	1.00909	-0.459801	1382	8197
Inpatient	8885	1.33051	0.66805	21212	8053.89	0.991636	1.63375	3157	1549
Inpatient	1889	1.3497	0.680491	21825.7	10465.8	0.983417	1.08542	2446	1120
Outpatient	6311	3.96092	1.99996	12448.2	552.939	0.9805	21.5128	8775	2565
Outpatient	6443	3.95827	2.0021	12428.6	551.684	0.97706	21.5285	8779	2568
Inpatient	6524	1.3163	0.666902	29278.1	14391.8	0.973752	1.03436	3559	1882
Outpatient	8039	3.85054	1.97528	9277.25	331.536	0.949362	26.9827	8966	2704
Inpatient	4428	2.09323	1.07896	29808.2	17993.8	0.940056	0.656575	3773	1024
Inpatient	4229	5.85233	3.02236	99142.6	55484.6	0.936343	0.786848	60	55
Inpatient	4472	1.89635	0.985044	29469.8	19328	0.925144	0.524718	2757	1146
Outpatient	9471	2.34601	1.22084	10166.8	19051.5	0.921636	-0.466354	49699	2778
Inpatient	9188	1.12155	0.583906	30865.5	7824.66	0.920771	2.94465	929	512
Inpatient	4203	2.95278	1.545	56105	31999.7	0.911183	0.753296	36	44
Outpatient	6345	4.74905	2.49538	8547.97	9080.73	0.903142	-0.0586695	644	7132
Inpatient	10	2.85889	1.50267	52981.4	28447.7	0.902537	0.862411	6727	3196
Professional	8267364255116217035	2.81401	1.48599	77.8854	242.792	0.893695	-0.67921	790	12247
Outpatient	4659	2.38249	1.2606	5316.01	2652.5	0.889963	1.00415	42437	43867
Outpatient	6405	3.55664	1.906	10787.2	496.68	0.866019	20.7187	8915	2692
Inpatient	6076	7.30715	3.92003	118784	64779	0.864054	0.833689	249	338
Inpatient	6650	1.38194	0.743304	29626.3	12265.5	0.859181	1.41541	2654	796
Inpatient	4208	2.76917	1.49182	52643.2	30949	0.856236	0.700967	36	22
Professional	-6408596399631400851	2.51532	1.35589	78.8173	186.82	0.8551	-0.578111	820	7434
Professional	-4933439739826783998	1.79688	0.969215	318.578	388.443	0.853953	-0.17986	2419	14307
Inpatient	4202	2.72083	1.47	51733.6	30472	0.850907	0.697742	36	44
Inpatient	45	1.92834	1.04311	37695.9	20391.6	0.848644	0.848601	12350	7040
Outpatient	30244	1.01165	6.52415	100	2024.94	-0.844937	-0.950616	1392	4516
Professional	-1993191113665120528	2.40467	1.30344	111.688	183.781	0.844865	-0.392274	2743	12837
Outpatient	23922	1.00725	6.20088	100	1984.35	-0.837564	-0.949606	1402	4794
Outpatient	25487	1.00725	5.90583	100	1819.81	-0.829449	-0.945049	1402	4636
Inpatient	1048	0.75	4.3482	7152.87	72197.6	-0.827515	-0.900926	24	100
Inpatient	5139	3.06774	1.67904	60172.4	29791.5	0.827081	1.01978	1843	323
Inpatient	3770	1.93113	1.05942	33006.8	19752.6	0.822812	0.671009	10577	6758
Outpatient	1903	1.18827	6.55188	18677.5	16521.6	-0.818636	0.130492	1447	11341
Outpatient	9995	3.96717	2.18341	10793.5	4664.59	0.816966	1.31391	56103	27649

… 24,372 more rows truncated

Rate Swings by Network

SQL

WITH
new AS (
    SELECT
        network_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_3.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY network_id
),
old AS (
    SELECT
        network_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY network_id
)
SELECT
    network_id,
    new.avg_pct_of_medicare_rate AS new_avg_pct_of_medicare_rate,
    old.avg_pct_of_medicare_rate AS old_avg_pct_of_medicare_rate,
    new.avg_canonical_rate AS new_avg_canonical_rate,
    old.avg_canonical_rate AS old_avg_canonical_rate,
    (new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0) AS medicare_rate_pct_change,
    (new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0) AS canonical_rate_pct_change,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count
FROM new
JOIN old USING (network_id)
WHERE (
    ABS((new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0)) > 0.1
   OR ABS((new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0)) > 0.1
) AND (
    new.roid_count > 500 AND old.roid_count > 500
)
ORDER BY ABS(medicare_rate_pct_change) DESC

128 entities with >10% rate swing

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
4.85736e+18	inf	inf	4655.05	5536.73	nan	-0.159242	9.95896e+06	7.71998e+06
3.11449e+17	inf	inf	3837.79	4593.12	nan	-0.164448	8.83174e+06	7.10928e+06
2.46964e+18	inf	inf	4514.17	4024.41	nan	0.121697	8.18164e+07	9.75147e+07
4.48413e+18	inf	inf	3389.64	4314.88	nan	-0.214431	1.24277e+08	9.24217e+07
-2.1625e+18	2.3615	inf	6090.64	4138.75	nan	0.471615	4.01141e+07	9.66705e+07
-3.71112e+18	inf	inf	3698.89	4700.09	nan	-0.213018	6.48646e+06	4.82015e+06
-3.776e+18	inf	inf	4522.62	4069.55	nan	0.11133	8.08709e+07	9.51156e+07
5.81072e+18	inf	inf	2573.23	4480.58	nan	-0.425693	1.4474e+08	7.84053e+07
4.28197e+18	inf	inf	2513.76	3166.76	nan	-0.206206	1.41309e+08	1.14711e+08
8.36158e+18	inf	inf	2563.38	3246.8	nan	-0.210491	1.45717e+08	1.16997e+08
-2.68566e+18	2.41787	1.48941	14564.4	2872.52	0.62337	4.07027	275712	2.22527e+06
-2.51068e+18	3.02913	1.87195	16848.4	5704.44	0.618167	1.95355	609300	2.99826e+06
8.81608e+18	2.90462	1.91384	16284.9	5842.82	0.517692	1.78717	534510	2.7805e+06
-8.97331e+18	2.88654	2.0645	13653.2	6090.16	0.398184	1.24185	438867	1.80775e+06
3.92257e+16	1.91904	3.16154	2436.48	6842.43	-0.393006	-0.643916	353723	115865
-3.95453e+18	2.71799	2.02545	16145.8	5408.61	0.341915	1.9852	328360	2.55077e+06
-7.99568e+18	3.41244	2.54872	26054.3	6138.28	0.338883	3.24455	2.74302e+06	7.64467e+06
7.90043e+18	2.27218	1.7103	11174.5	3606.87	0.328525	2.09812	81606	288415
1.29509e+18	2.66869	2.0422	18331.2	3377.3	0.306773	4.42777	6219	703997
3.97381e+18	4.52643	3.46742	24343.8	6764.22	0.305415	2.5989	624540	1.17147e+06
-7.94267e+18	1.68452	2.36804	7893.49	18299.5	-0.288645	-0.56865	3.70836e+06	422427
4.86702e+18	3.23733	2.55024	951.868	760.674	0.269419	0.251349	312740	21463
-3.56836e+18	1.99362	2.664	3772.51	2405.28	-0.251644	0.568429	547562	505084
6.09139e+18	2.72268	2.22349	8401.25	4634.12	0.224507	0.81291	248157	468407
2.26507e+18	1.20061	1.48906	1582.34	1902.03	-0.193717	-0.168074	1.93313e+06	1.23968e+06
-1.37229e+17	1.42443	1.745	2514.36	4959.57	-0.18371	-0.493029	2.53389e+06	1.11038e+06
-4.56425e+18	2.15285	2.62917	5796.73	11669.4	-0.181169	-0.503253	434282	115073
-6.58839e+18	1.78164	2.14489	6658.31	7463.46	-0.16936	-0.107878	3.22087e+06	2.60543e+06
1.00172e+18	2.2432	2.69377	8434.33	11208.9	-0.167263	-0.247532	912865	522174
-4.94439e+18	3.93234	3.37253	15805.7	8749.05	0.165989	0.806566	450532	139108
-4.21197e+18	2.29174	1.96661	4617.45	3263.66	0.165324	0.414809	2.03094e+06	3.12767e+06
-7.3043e+18	1.80808	2.16452	7321.34	8626.66	-0.164673	-0.151313	3.47903e+06	2.76598e+06
-7.22559e+18	1.79143	2.1443	6522.11	7378.37	-0.164562	-0.116051	3.25617e+06	2.63417e+06
7.75015e+18	1.79139	2.14304	6554.76	7373.25	-0.164089	-0.111009	3.24963e+06	2.63235e+06
-1.88424e+18	3.96936	3.41109	5459.32	3075.79	0.163664	0.774934	29927	17031
5.32583e+18	2.47631	2.13496	5583.7	3397.92	0.159886	0.64327	258575	486401
-8.50611e+18	1.1687	1.39006	776.729	3393.08	-0.159242	-0.771084	2.38408e+06	610855
1.01741e+18	1.50406	1.77105	2583.34	3473.19	-0.150752	-0.256205	1.27888e+07	8.38308e+06
3.47839e+18	1.51187	1.7751	2730.27	3626.15	-0.148292	-0.247062	849665	619546
-7.57238e+18	1.48322	1.7384	3272.91	3584.73	-0.146794	-0.0869867	1.17162e+06	918458
-4.06181e+18	1.96404	2.28493	3126.65	2915.78	-0.14044	0.0723211	159671	99344
5.7811e+18	1.39205	1.61572	1665.86	2000.73	-0.13843	-0.167375	3.2228e+06	2.67814e+06
5.37388e+18	1.78333	2.06784	2854.72	3532.51	-0.137587	-0.19187	8.17571e+06	5.56047e+06
4.37757e+18	1.3956	1.61732	3249.93	4706.55	-0.137093	-0.309488	4.7829e+06	3.18319e+06
7.24795e+18	1.66708	1.92959	2054.72	3191.58	-0.136043	-0.356207	1.35139e+06	774033
7.61684e+18	1.58081	1.82871	2809.42	3833.97	-0.135556	-0.26723	1.4205e+07	9.23544e+06
5.75087e+18	1.65394	1.91172	4111.77	5148.53	-0.134845	-0.20137	6.77226e+06	5.16961e+06
-6.97213e+18	1.82462	2.1054	3377.52	4167.38	-0.13336	-0.189534	8.45281e+06	5.83018e+06
-8.82263e+18	1.91364	1.6899	5115.19	4084.63	0.132396	0.252301	1.76729e+06	2.32012e+06
8.61163e+18	1.97942	2.27621	3843.86	5858.04	-0.130388	-0.343833	9.28024e+06	6.67618e+06
1.99861e+18	2.14893	2.46875	3078.45	3926.01	-0.129549	-0.215883	1.24486e+06	991394
6.35208e+18	2.16766	2.48235	1479.78	1655	-0.126771	-0.105869	659435	682107
-1.19984e+18	1.64994	1.88508	9473.87	12457.4	-0.124739	-0.239501	5.05772e+06	2.20001e+06
8.53056e+18	1.58221	1.80135	4499.11	4280.72	-0.121656	0.0510163	1.70601e+06	1.72871e+06
2.11972e+18	1.68929	1.91834	3050.2	4345.38	-0.119401	-0.298059	1.0685e+07	7.03518e+06
4.98841e+18	2.01931	2.27524	8364.73	9300.12	-0.112484	-0.100579	1.38413e+06	1.41409e+06
6.6383e+18	1.69118	1.90299	3569.63	5095.07	-0.111303	-0.299396	516187	333968
-4.02759e+18	1.76948	1.59738	5252.34	3835.8	0.107737	0.369294	1.35704e+06	1.94365e+06
-4.44552e+18	1.65417	1.49353	833.582	582.972	0.107562	0.429883	370736	372293
-1.36306e+18	1.62962	1.82534	6778.69	9010.66	-0.107225	-0.247703	468971	254730
5.15753e+17	1.53341	1.7135	3893.4	6101.59	-0.105095	-0.361904	3.27714e+06	1.87189e+06
-5.58179e+17	1.74672	1.95017	5238.37	6307.1	-0.104323	-0.169448	4.27943e+06	3.27389e+06
5.07576e+18	1.75139	1.9518	5247.71	6316.17	-0.102682	-0.169162	4.28966e+06	3.27856e+06
3.13359e+18	2.13326	2.37256	240.692	285.449	-0.100862	-0.156797	21288	15620
-7.16348e+18	1.63681	1.80356	1411.57	2515.36	-0.0924538	-0.438819	3.44292e+06	1.67889e+06
-5.87428e+18	1.62832	1.79175	1404.2	2511.96	-0.0912092	-0.440996	3.44409e+06	1.68057e+06
-8.39357e+18	1.94565	2.13551	3759.57	5624.03	-0.0889064	-0.331516	2.26247e+06	1.42787e+06
6.43758e+18	2.29409	2.51775	7137.75	8770.23	-0.0888342	-0.186139	1.19192e+06	747458
-5.27897e+18	1.55013	1.69821	2809.3	3402.05	-0.0871978	-0.174231	3.55166e+06	2.99572e+06
1.17855e+18	2.10596	1.93846	6517.12	5499.14	0.0864077	0.185118	799958	1.01078e+06
9.21501e+18	1.52847	1.6702	2799.5	3402.35	-0.0848576	-0.177187	3.56175e+06	3.00394e+06
1.48124e+18	2.18667	2.3885	3317.52	4815.21	-0.084501	-0.311033	1.72102e+06	1.0886e+06
-2.59692e+18	1.92109	2.09394	5445.22	6065.62	-0.0825505	-0.102282	1.53432e+06	1.33842e+06
-4.34992e+17	1.96036	2.13611	3441.47	3896.58	-0.0822775	-0.116797	3.06686e+06	2.68867e+06
-1.70808e+18	1.4725	1.60133	5496.24	7154.27	-0.0804525	-0.231754	397769	174409
6.65088e+18	1.32992	1.44551	2048.55	2714.58	-0.0799661	-0.245353	4.91688e+06	2.96546e+06
5.26319e+18	1.29369	1.39995	1992.21	2604.09	-0.0759051	-0.234968	4.72486e+06	2.84008e+06
-6.29329e+18	1.90623	2.06225	4028.61	3125.84	-0.0756524	0.288809	3.28947e+06	3.36203e+06
-7.69528e+18	2.38398	2.21668	6714.95	5217.08	0.0754738	0.28711	1.00431e+06	1.29728e+06
-4.94405e+18	3.08543	3.32419	3039.31	5397.01	-0.0718261	-0.436853	1.49449e+06	862125
-3.04564e+18	2.83262	3.05158	11571.3	14005.4	-0.0717526	-0.173795	921110	762961
-8.49976e+18	1.80446	1.94393	138.011	161.945	-0.0717459	-0.147791	966391	604470
-4.66457e+18	1.83782	1.97563	4968.93	5859.32	-0.0697563	-0.151961	808552	608471
-1.59583e+18	1.86299	1.74859	4241.03	3688.78	0.065427	0.149712	1.64042e+06	1.15399e+06
-5.30429e+17	1.82216	1.71159	3998.72	3521.55	0.0645973	0.135498	1.63433e+06	1.14645e+06
6.97454e+18	1.69939	1.59648	3868.25	2997.74	0.0644609	0.290389	921248	1.24012e+06
4.91105e+18	2.06026	1.93944	8316.25	6760.04	0.0622942	0.230206	2.83743e+06	3.53883e+06
-5.61727e+18	2.07728	2.21522	11774.1	16670.6	-0.0622669	-0.293718	1.20804e+06	1.11356e+06
-8.80602e+18	2.07687	2.21436	2085.38	2324.64	-0.0620895	-0.102924	1.8525e+06	1.90945e+06
-1.06886e+18	1.79408	1.91026	2972.87	3528.61	-0.0608211	-0.157496	4.12826e+06	3.17662e+06
5.60513e+18	1.84044	1.95708	14213.5	27534.9	-0.0596031	-0.4838	1.20037e+06	633995
-2.86547e+18	1.9588	2.08206	4204.4	5163.95	-0.0591966	-0.185818	1.09624e+06	834387
-3.06376e+18	1.81363	1.9272	3074.35	3598.75	-0.0589334	-0.145718	3.88528e+06	2.93631e+06
5.17876e+18	2.36756	2.51489	11849.8	13815.5	-0.0585836	-0.142287	1.31418e+06	987967
5.17793e+18	1.63797	1.73657	3543.32	4177.54	-0.0567783	-0.151817	3.76746e+06	2.96379e+06
-2.78293e+18	2.5001	2.64401	1976.31	2252.4	-0.0544302	-0.122572	25031	22953
-5.23072e+18	1.82449	1.92632	2134.82	3198.41	-0.0528613	-0.332537	1.30606e+07	7.50081e+06
6.06917e+18	1.84959	1.95116	4663.57	5248.42	-0.0520569	-0.111435	1.73162e+06	1.5732e+06
-3.16793e+18	2.82609	2.97885	3102.7	3531.68	-0.0512815	-0.121466	516460	478107
4.61509e+18	1.93479	1.84398	5666.6	3877.7	0.0492469	0.461333	1.56385e+06	2.66164e+06

… 28 more rows truncated

Rate Swings by Network Provider

SQL

WITH
new AS (
    SELECT
        network_id,
        provider_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_3.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY network_id, provider_id
),
old AS (
    SELECT
        network_id,
        provider_id,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY network_id, provider_id
)
SELECT
    network_id,
    provider_id,
    new.avg_pct_of_medicare_rate AS new_avg_pct_of_medicare_rate,
    old.avg_pct_of_medicare_rate AS old_avg_pct_of_medicare_rate,
    new.avg_canonical_rate AS new_avg_canonical_rate,
    old.avg_canonical_rate AS old_avg_canonical_rate,
    (new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0) AS medicare_rate_pct_change,
    (new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0) AS canonical_rate_pct_change,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count
FROM new
JOIN old USING (network_id, provider_id)
WHERE (
    ABS((new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0)) > 0.1
   OR ABS((new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0)) > 0.1
) AND (
    new.roid_count > 500 AND old.roid_count > 500
)
ORDER BY ABS(medicare_rate_pct_change) DESC

131,254 entities with >10% rate swing

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
-2162498661799343237	527	2.24736	inf	5519.6	9766.11	nan	-0.434821	5711	5323
-5146581664284924254	527	inf	inf	7076.06	10325.1	nan	-0.314674	4905	3143
4281965180796802413	9261	3.18779	0.573638	4037.37	5274.52	4.55714	-0.234551	1406	591
-6293294949651930973	2029	6.5805	1.38848	35431.5	22995.1	3.73936	0.540827	2483	1204
-3776001016975145508	4659	4.33635	0.980108	11506.1	2992.49	3.42436	2.845	7299	6116
2469641715717297819	4659	4.33635	0.980108	11506.1	2992.49	3.42436	2.845	7299	6116
5177932399303345046	9899	2.57295	0.638286	5264.07	7335.13	3.03103	-0.282349	1619	811
4484133589360821145	-5533196711371329157	4.24983	1.13518	97.3568	356.215	2.74375	-0.726691	611	5755
4281965180796802413	9899	2.37741	0.651426	5630.31	7599.75	2.64955	-0.259145	1811	1003
8611629356743080984	-6851520483243936391	4.13467	1.15547	145.355	853.297	2.57834	-0.829655	953	5762
8611629356743080984	-1692682499389316655	4.1619	1.17222	146.811	687.428	2.55045	-0.786434	944	5699
4484133589360821145	-8612629535985160731	5.13222	1.4657	112.175	360.162	2.50154	-0.688543	538	5666
2119715416692563737	-8612629535985160731	5.13222	1.46573	112.175	360.162	2.50147	-0.688543	538	5666
-2162498661799343237	8457	4.28718	1.22516	11735.8	1780.23	2.49929	5.59232	3954	2376
4484133589360821145	8457	4.28718	1.22516	11735.8	1780.23	2.49929	5.59232	3954	2376
8611629356743080984	-543736610439017036	4.16733	1.19446	146.479	700.605	2.48888	-0.790925	942	5704
-434992428366718724	1552371258498160260	5.52607	1.59225	118.612	371.401	2.4706	-0.680637	512	5668
4281965180796802413	10030	2.13369	0.626492	4714.98	6397.71	2.40577	-0.263021	1284	841
-2162498661799343237	936	7.14941	2.15304	6171.76	13109.7	2.32061	-0.529222	1208	7617
8611629356743080984	-2974582469839138239	4.16211	1.25356	146.834	810.274	2.32022	-0.818785	944	5771
8611629356743080984	-1897428036733617130	4.16211	1.25356	146.834	810.311	2.32022	-0.818793	944	5771
8611629356743080984	-4622893277367969014	4.16211	1.25356	146.834	810.274	2.32022	-0.818785	944	5771
-3776001016975145508	3533	7.88973	2.46714	20473.6	7899.44	2.19792	1.59178	8091	7568
-6899755675572465411	246	2.94637	0.927061	10672	8032.65	2.17818	0.328576	1957	3969
2119715416692563737	-3615894406312521316	3.85395	1.21716	148.773	370.159	2.16635	-0.598084	831	5758
-434992428366718724	-2316701640470619156	4.7607	1.51217	130.449	355.3	2.14826	-0.632847	658	5736
2469641715717297819	7261959515225550159	3.32421	1.05781	1260.07	392.684	2.14253	2.20886	3974	4046
4911047023988898996	2121	2.82618	0.906906	42112.8	13059.3	2.11629	2.22473	1710	1477
5750867047671580791	3094309123841005573	3.52012	1.1339	78.731	298.604	2.10444	-0.736337	574	5589
4484133589360821145	3094309123841005573	3.52012	1.1339	78.731	298.604	2.10444	-0.736337	574	5589
4484133589360821145	2158801442449536628	3.52012	1.1339	78.731	298.604	2.10444	-0.736337	574	5589
5750867047671580791	2158801442449536628	3.52012	1.1339	78.731	298.604	2.10444	-0.736337	574	5589
8611629356743080984	-3148982206069435370	4.16496	1.34914	146.082	867.892	2.08711	-0.831682	949	5761
-2162498661799343237	3083	8.82277	2.85973	2616.68	9093.43	2.08518	-0.712244	646	4617
-3776001016975145508	9139808401823935150	3.17646	1.03973	286.849	542.774	2.05508	-0.471512	1045	5084
2469641715717297819	9139808401823935150	3.17646	1.03973	286.849	542.774	2.05508	-0.471512	1045	5084
2469641715717297819	-7146320621286215096	3.72525	1.23283	353.98	603.323	2.02172	-0.413283	897	5022
-3776001016975145508	-7146320621286215096	3.72525	1.23283	353.98	603.323	2.02172	-0.413283	897	5022
4484133589360821145	-2316701640470619156	4.78552	1.58997	166.022	358.436	2.00982	-0.536815	780	5738
-2162498661799343237	359	8.95114	3.0088	15355.3	10732.6	1.97498	0.430714	844	8209
4484133589360821145	1197	3.08714	1.04633	7679.07	10911.6	1.95044	-0.296248	1081	600
-2162498661799343237	1197	3.08714	1.04633	7679.07	10911.6	1.95044	-0.296248	1081	600
-434992428366718724	-5167770475857091632	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
4484133589360821145	8648638605755853649	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
-434992428366718724	-5804826201479272897	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
-434992428366718724	-2376062169520869241	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
4484133589360821145	-2376062169520869241	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
4484133589360821145	-5167770475857091632	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
-434992428366718724	-5244241993508083181	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
4484133589360821145	-5804826201479272897	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
-434992428366718724	8648638605755853649	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
2469641715717297819	-218275949651467597	3.39686	1.15553	221.355	410.349	1.93964	-0.46057	1117	4906
-3776001016975145508	-218275949651467597	3.39686	1.15553	221.355	410.349	1.93964	-0.46057	1117	4906
8611629356743080984	7578129148473253785	3.8597	1.32403	323.883	353.927	1.91511	-0.0848879	957	5663
8611629356743080984	-1414207905763961762	3.8597	1.32403	323.883	353.927	1.91511	-0.0848879	957	5663
-3776001016975145508	-1921451032501448557	3.45876	1.19823	914.064	479.858	1.88655	0.904863	705	4847
2469641715717297819	-1921451032501448557	3.45876	1.19823	914.064	479.858	1.88655	0.904863	705	4847
-3776001016975145508	-6952577638134067614	4.02807	1.39644	933.41	471.059	1.88452	0.981514	603	4472
2469641715717297819	-6952577638134067614	4.02807	1.39644	933.41	471.059	1.88452	0.981514	603	4472
4911047023988898996	6117951486337506263	2.66469	0.925734	175.043	370.24	1.87846	-0.527219	2823	4850
-2162498661799343237	2807350638538610290	2.90405	1.01409	360.868	312.543	1.8637	0.154617	1317	5681
-1766392260690175685	3752	5.36345	1.8756	23177.8	9156.03	1.85959	1.53142	7732	3793
-2162498661799343237	3197309463706723792	3.57684	1.25581	469.245	761.984	1.84824	-0.38418	1144	5704
4281965180796802413	-6952577638134067614	4.11504	1.47073	162.416	440.366	1.79796	-0.631179	892	5537
8611629356743080984	-4820926513593666878	4.16496	1.48909	146.082	857.111	1.79698	-0.829565	949	5762
2469641715717297819	-7494421924227766976	3.22986	1.15704	323.603	341.216	1.79148	-0.0516196	1036	5058
-3776001016975145508	-7494421924227766976	3.22986	1.15704	323.603	341.216	1.79148	-0.0516196	1036	5058
4281965180796802413	8373	1.74481	0.625263	5418.88	7512.51	1.79052	-0.278686	1395	931
-7995682717897123039	6647	5.34313	1.91591	40381.8	3950.06	1.78882	9.22309	8288	5126
4911047023988898996	-1921451032501448557	3.33072	1.19688	898.19	479.71	1.78285	0.872361	526	4847
5750867047671580791	8373	1.82281	0.655638	5675.45	8074.77	1.78021	-0.297138	1326	862
-3776001016975145508	-836335150426181903	3.44577	1.23985	1162.61	467.962	1.77917	1.48441	1461	5079
2469641715717297819	-1098810057456550881	3.79019	1.36389	236.817	410.288	1.77895	-0.422803	1023	4261
-3776001016975145508	-1098810057456550881	3.79019	1.36389	236.817	410.288	1.77895	-0.422803	1023	4261
8361580493441765265	-6952577638134067614	4.08556	1.47073	159.431	440.366	1.77792	-0.637957	892	5537
-2162498661799343237	4469775568359057112	3.10716	1.12646	134.579	744.42	1.75834	-0.819217	990	5677
2469641715717297819	-836335150426181903	3.41597	1.23985	1162.77	467.962	1.75514	1.48476	1458	5079
-2162498661799343237	1308	5.29299	1.92207	28074.5	8344.22	1.75379	2.36455	3331	5968
4911047023988898996	-4549661144609661073	2.0354	0.740587	619.477	318.587	1.74837	0.94445	1382	3958
-3776001016975145508	-8622705167749940743	3.84442	1.39909	246.71	541.05	1.74781	-0.544017	1031	4347
2469641715717297819	-8622705167749940743	3.84442	1.39909	246.71	541.05	1.74781	-0.544017	1031	4347
-6486550968171266793	-8942406926707988997	4.0193	1.47422	2052.93	674.673	1.7264	2.04285	1869	5007
-6899755675572465411	3752	5.10272	1.87412	22497.5	9155.65	1.72273	1.45723	8195	3793
-3776001016975145508	2512	5.82049	2.15232	1269.53	8126.67	1.70429	-0.843782	839	5687
2469641715717297819	2512	5.82049	2.15232	1269.53	8126.67	1.70429	-0.843782	839	5687
-7304296722942665713	-6459693256712245422	3.51129	1.30418	184.554	1546.3	1.69234	-0.880647	646	1363
-2162498661799343237	7112740971720668231	3.08604	1.14721	227.294	89.6496	1.69005	1.53536	536	1917
-2215537880835804330	2649	4.38749	1.63499	14787	5583.82	1.68349	1.64819	5828	1503
-2215537880835804330	2595	4.38256	1.63967	14793.9	5577.3	1.67284	1.65252	5840	1505
6437583333460157798	9995	3.50517	1.31154	14823.1	24663.3	1.67257	-0.398981	4110	1710
4484133589360821145	8717763333513152914	3.34123	1.25215	288.38	757.981	1.66839	-0.619542	2646	5682
-3776001016975145508	2121	2.86456	1.07636	42116.7	12133.2	1.66135	2.47119	1679	782
2469641715717297819	2121	2.86456	1.07636	42116.7	12133.2	1.66135	2.47119	1679	782
8611629356743080984	-4032066451829351945	4.16873	1.56908	147.68	816.514	1.6568	-0.819134	946	5771
8611629356743080984	-2503048820999107425	4.16873	1.56908	147.68	816.514	1.6568	-0.819134	946	5771
7750147370859442459	-6459693256712245422	3.51129	1.32546	184.554	1543.23	1.64912	-0.88041	646	1369
-7225588104346557715	-6459693256712245422	3.51129	1.32546	184.554	1543.23	1.64912	-0.88041	646	1369
-6588387606928155169	-6459693256712245422	3.51129	1.32546	184.554	1543.23	1.64912	-0.88041	646	1369
-2162498661799343237	-3923747290197026165	3.71139	1.40115	916.048	887.673	1.64882	0.031965	1818	5722
-434992428366718724	-4448885869983192034	3.55608	1.34962	104.032	350.32	1.63489	-0.703038	658	5736

… 131,154 more rows truncated

Rate Swings by Network Provider Bill

SQL

WITH
new AS (
    SELECT
        network_id,
        provider_id,
        bill_type,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_3.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY network_id, provider_id, bill_type
),
old AS (
    SELECT
        network_id,
        provider_id,
        bill_type,
        AVG(canonical_rate_percent_of_medicare) AS avg_pct_of_medicare_rate,
        AVG(canonical_rate) AS avg_canonical_rate,
        COUNT(DISTINCT roid) AS roid_count
    FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY network_id, provider_id, bill_type
)
SELECT
    network_id,
    provider_id,
    bill_type,
    new.avg_pct_of_medicare_rate AS new_avg_pct_of_medicare_rate,
    old.avg_pct_of_medicare_rate AS old_avg_pct_of_medicare_rate,
    new.avg_canonical_rate AS new_avg_canonical_rate,
    old.avg_canonical_rate AS old_avg_canonical_rate,
    (new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0) AS medicare_rate_pct_change,
    (new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0) AS canonical_rate_pct_change,
    new.roid_count AS new_roid_count,
    old.roid_count AS old_roid_count
FROM new
JOIN old USING (network_id, provider_id, bill_type)
WHERE (
    ABS((new.avg_pct_of_medicare_rate - old.avg_pct_of_medicare_rate) / NULLIF(old.avg_pct_of_medicare_rate, 0)) > 0.1
   OR ABS((new.avg_canonical_rate - old.avg_canonical_rate) / NULLIF(old.avg_canonical_rate, 0)) > 0.1
) AND (
    CASE
        WHEN bill_type = 'Inpatient' 
        THEN new.roid_count > 10 AND old.roid_count > 10
        WHEN bill_type = 'Outpatient' 
        THEN new.roid_count > 500 AND old.roid_count > 500
        WHEN bill_type = 'Professional' 
        THEN new.roid_count > 500 AND old.roid_count > 500
    END
)
ORDER BY ABS(medicare_rate_pct_change) DESC

139,436 entities with >10% rate swing

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
8361580493441765265	6539	Inpatient	6.22612	1.07418	156691	26990.8	4.79617	4.80534	1711	1711
311448953162816591	5017	Inpatient	3.09972	0.5662	53195.8	6158.82	4.47461	7.63733	1707	150
4281965180796802413	3076	Inpatient	4.57094	0.851341	69219.3	13001.2	4.36911	4.32408	1432	1708
1579516467629270577	5814	Inpatient	6.06167	1.19044	97789.2	20895.6	4.09196	3.6799	563	114
2469641715717297819	4659	Outpatient	4.81296	0.978592	11177.7	2585.24	3.91825	3.32368	6387	5937
-3776001016975145508	4659	Outpatient	4.81296	0.978592	11177.7	2585.24	3.91825	3.32368	6387	5937
-7995682717897123039	6593	Inpatient	5.5379	1.14406	130484	13606.5	3.84056	8.58982	1687	32
311448953162816591	5340	Inpatient	3.17113	0.659557	53231.1	5180.44	3.80797	9.27539	1704	158
-6293294949651930973	2029	Outpatient	6.57328	1.38848	28480.9	22995.1	3.73415	0.238565	2204	1204
3267736702885179114	365	Inpatient	5.54057	1.19815	77557.9	8642.63	3.62428	7.97387	1159	27
-7995682717897123039	6478	Inpatient	5.3758	1.23819	116956	25641.2	3.34164	3.56126	1687	216
4857361656543545479	5017	Inpatient	4.411	1.01884	78097	10456.1	3.32943	6.469	1693	259
4281965180796802413	6521	Inpatient	5.18596	1.25644	112966	31397.5	3.12749	2.59793	1708	374
311448953162816591	5419	Inpatient	1.99749	0.501875	38505.8	4008.29	2.98005	8.60654	1600	16
311448953162816591	5343	Inpatient	2.80956	0.7135	55020	7305.79	2.93772	6.53101	1697	40
-7995682717897123039	9099	Inpatient	4.47349	1.14406	104234	13606.5	2.91018	6.66064	1700	32
-2162498661799343237	1308	Outpatient	8.02229	2.09985	15025.6	3782.34	2.82041	2.97256	1681	4517
4484133589360821145	-5533196711371329157	Professional	4.24983	1.13518	97.3568	356.215	2.74375	-0.726691	611	5755
-7995682717897123039	6649	Inpatient	5.33972	1.47667	132522	22371.5	2.61606	4.92371	1687	33
2569506757851818343	5831	Inpatient	1.70499	0.472084	24445.6	11626	2.61162	1.10267	796	499
8611629356743080984	-6851520483243936391	Professional	4.13467	1.15547	145.355	853.297	2.57834	-0.829655	953	5762
311448953162816591	5457	Inpatient	4.09074	1.14649	78428.9	39511.7	2.56805	0.984956	1617	57
2569506757851818343	5857	Inpatient	3.36902	0.945606	51868.2	10791.9	2.56281	3.80623	265	66
4857361656543545479	5249	Inpatient	3.64389	1.02608	68250.4	13026.9	2.55128	4.2392	1695	153
8611629356743080984	-1692682499389316655	Professional	4.1619	1.17222	146.811	687.428	2.55045	-0.786434	944	5699
4484133589360821145	-8612629535985160731	Professional	5.13222	1.4657	112.175	360.162	2.50154	-0.688543	538	5666
2119715416692563737	-8612629535985160731	Professional	5.13222	1.46573	112.175	360.162	2.50147	-0.688543	538	5666
4484133589360821145	8457	Outpatient	4.28718	1.22516	11735.8	1780.23	2.49929	5.59232	3954	2376
-2162498661799343237	8457	Outpatient	4.28718	1.22516	11735.8	1780.23	2.49929	5.59232	3954	2376
8611629356743080984	-543736610439017036	Professional	4.16733	1.19446	146.479	700.605	2.48888	-0.790925	942	5704
-434992428366718724	1552371258498160260	Professional	5.52607	1.59225	118.612	371.401	2.4706	-0.680637	512	5668
-7995682717897123039	6590	Inpatient	4.86408	1.40786	105488	51666.2	2.45494	1.04171	1700	201
-137228703119221502	5905	Inpatient	3.77012	1.09528	63662.9	19918.1	2.44215	2.19623	1461	392
-3776001016975145508	3533	Outpatient	9.70596	2.8673	21766.1	5647.88	2.38505	2.85386	6385	5862
311448953162816591	5249	Inpatient	1.87894	0.560403	34473.7	6744.09	2.35283	4.1117	1697	471
-3948865855513812327	1661	Inpatient	2.21396	0.666733	38781.6	8238.27	2.32061	3.70749	1686	1179
8611629356743080984	-1897428036733617130	Professional	4.16211	1.25356	146.834	810.311	2.32022	-0.818793	944	5771
8611629356743080984	-4622893277367969014	Professional	4.16211	1.25356	146.834	810.274	2.32022	-0.818785	944	5771
8611629356743080984	-2974582469839138239	Professional	4.16211	1.25356	146.834	810.274	2.32022	-0.818785	944	5771
-2162498661799343237	359	Outpatient	10.996	3.31434	6401.36	5932.72	2.3177	0.0789928	643	6499
5810723572563874815	6521	Inpatient	5.0333	1.5248	106255	31802.4	2.30094	2.34109	1699	256
8361580493441765265	6521	Inpatient	5.0333	1.5248	106255	31802.4	2.30094	2.34109	1699	256
-2162498661799343237	6692	Inpatient	5.02909	1.54649	80645.8	24123.1	2.25193	2.3431	33	1312
-2162498661799343237	936	Outpatient	7.18162	2.22278	6111.85	5014.05	2.23092	0.218944	1201	5907
-7995682717897123039	6408	Inpatient	3.74951	1.17695	72288.1	34256.8	2.18579	1.11018	1703	59
2119715416692563737	-3615894406312521316	Professional	3.85395	1.21716	148.773	370.159	2.16635	-0.598084	831	5758
1579516467629270577	5829	Inpatient	3.91684	1.23852	57057	18377.5	2.16252	2.10471	734	54
311448953162816591	4990	Inpatient	1.81172	0.573844	29849.8	5634.89	2.15717	4.29732	1696	588
-434992428366718724	-2316701640470619156	Professional	4.7607	1.51217	130.449	355.3	2.14826	-0.632847	658	5736
2469641715717297819	7261959515225550159	Professional	3.32421	1.05781	1260.07	392.684	2.14253	2.20886	3974	4046
4911047023988898996	2121	Inpatient	2.82618	0.906906	42112.8	13059.3	2.11629	2.22473	1710	1477
-6899755675572465411	3752	Outpatient	5.83334	1.87412	23522.9	9155.65	2.11257	1.56922	6944	3793
-1766392260690175685	3752	Outpatient	5.83138	1.8756	23513.1	9156.03	2.10908	1.56804	6947	3793
8361580493441765265	4564	Inpatient	3.34271	1.07592	51816.6	16701.2	2.10682	2.10257	1710	1688
4484133589360821145	3094309123841005573	Professional	3.52012	1.1339	78.731	298.604	2.10444	-0.736337	574	5589
4484133589360821145	2158801442449536628	Professional	3.52012	1.1339	78.731	298.604	2.10444	-0.736337	574	5589
5750867047671580791	3094309123841005573	Professional	3.52012	1.1339	78.731	298.604	2.10444	-0.736337	574	5589
5750867047671580791	2158801442449536628	Professional	3.52012	1.1339	78.731	298.604	2.10444	-0.736337	574	5589
8611629356743080984	-3148982206069435370	Professional	4.16496	1.34914	146.082	867.892	2.08711	-0.831682	949	5761
4281965180796802413	9713	Inpatient	4.53195	1.47317	73989.2	25904.6	2.07633	1.85621	473	647
2469641715717297819	9139808401823935150	Professional	3.17646	1.03973	286.849	542.774	2.05508	-0.471512	1045	5084
-3776001016975145508	9139808401823935150	Professional	3.17646	1.03973	286.849	542.774	2.05508	-0.471512	1045	5084
8611629356743080984	6367	Inpatient	2.51513	0.82474	88184.8	33541.8	2.04961	1.6291	1710	1709
1579516467629270577	5849	Inpatient	2.55617	0.842049	36203.4	20653.2	2.03565	0.752921	736	366
-2162498661799343237	2276	Inpatient	5.75821	1.90479	91767.3	35833.7	2.02302	1.56092	1463	1679
-3776001016975145508	-7146320621286215096	Professional	3.72525	1.23283	353.98	603.323	2.02172	-0.413283	897	5022
2469641715717297819	-7146320621286215096	Professional	3.72525	1.23283	353.98	603.323	2.02172	-0.413283	897	5022
311448953162816591	5383	Inpatient	2.29091	0.758463	45846.3	12024.3	2.02046	2.81279	1633	1308
4484133589360821145	-2316701640470619156	Professional	4.78552	1.58997	166.022	358.436	2.00982	-0.536815	780	5738
-7973680817747944938	664	Outpatient	2.45649	0.817363	855.14	1322.55	2.00539	-0.353414	593	512
707343854139029255	664	Outpatient	2.45649	0.817363	855.14	1322.55	2.00539	-0.353414	593	512
-7995682717897123039	6504	Inpatient	5.82131	1.95586	141351	89552.2	1.97634	0.578415	1601	186
8611629356743080984	8689	Inpatient	4.23589	1.42412	64039.9	24258.1	1.9744	1.63993	180	1630
-2162498661799343237	2283	Inpatient	3.49375	1.17469	45684.8	11736.9	1.9742	2.8924	16	256
-6972129921516377126	6366	Inpatient	5.59489	1.88605	89849.7	32830.1	1.96647	1.73681	888	1282
-2162498661799343237	8775	Outpatient	8.98445	3.03231	8072.82	6496.27	1.96291	0.242686	714	5780
-434992428366718724	-5167770475857091632	Professional	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
4484133589360821145	-5167770475857091632	Professional	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
4484133589360821145	8648638605755853649	Professional	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
-434992428366718724	-5244241993508083181	Professional	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
-434992428366718724	-5804826201479272897	Professional	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
4484133589360821145	-5804826201479272897	Professional	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
4484133589360821145	-2376062169520869241	Professional	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
-434992428366718724	-2376062169520869241	Professional	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
-434992428366718724	8648638605755853649	Professional	4.24153	1.44151	119.366	352.195	1.94243	-0.661079	658	5736
-7995682717897123039	6512	Inpatient	4.82552	1.64138	98858.9	33751.4	1.93991	1.92904	1705	65
-3776001016975145508	-218275949651467597	Professional	3.39686	1.15553	221.355	410.349	1.93964	-0.46057	1117	4906
2469641715717297819	-218275949651467597	Professional	3.39686	1.15553	221.355	410.349	1.93964	-0.46057	1117	4906
-2162498661799343237	2138	Inpatient	5.84156	1.99101	92072.8	35959.2	1.93396	1.56048	1392	1679
4281965180796802413	6316	Inpatient	3.05079	1.03986	64668.5	24409.5	1.93385	1.64931	1710	1710
-2162498661799343237	2244	Inpatient	5.82559	1.99516	92054.6	36274.2	1.91986	1.53774	1413	1679
-2162498661799343237	2052	Inpatient	5.47149	1.87428	101385	33480.1	1.91925	2.02823	975	1662
8611629356743080984	7578129148473253785	Professional	3.8597	1.32403	323.883	353.927	1.91511	-0.0848879	957	5663
8611629356743080984	-1414207905763961762	Professional	3.8597	1.32403	323.883	353.927	1.91511	-0.0848879	957	5663
-2162498661799343237	4528	Inpatient	3.85824	1.33444	55292.7	17540.3	1.89127	2.15232	1294	27
4484133589360821145	4528	Inpatient	3.85824	1.33444	55292.7	17540.3	1.89127	2.15232	1294	27
-3776001016975145508	-1921451032501448557	Professional	3.45876	1.19823	914.064	479.858	1.88655	0.904863	705	4847
2469641715717297819	-1921451032501448557	Professional	3.45876	1.19823	914.064	479.858	1.88655	0.904863	705	4847
-3776001016975145508	-6952577638134067614	Professional	4.02807	1.39644	933.41	471.059	1.88452	0.981514	603	4472
2469641715717297819	-6952577638134067614	Professional	4.02807	1.39644	933.41	471.059	1.88452	0.981514	603	4472

… 139,336 more rows truncated

Large ROID Count Swings (>10%)

ROID Swings by Contract Methodology

SQL

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

1 entities with >10% ROID swing

canonical_contract_methodology	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Fee Schedule	894348313	755482966	138865347	0.18381

ROID Swings by Crosswalk Method

SQL

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

3 entities with >10% ROID swing

canonical_crosswalk_method	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Crosswalked From RC-FAMILY to HCPCS	11039492	9559867	1479625	0.154775
Crosswalked From RC-FAMILY to MS-DRG	1062996	941127	121869	0.129493
Crosswalked From RC-FAMILY to APR-DRG	1420460	1272350	148110	0.116407

ROID Swings by Gross Charge Type

SQL

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

1 entities with >10% ROID swing

canonical_gross_charge_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
mrf_gross_charge_cbsa_median	26176704	30448932	-4272228	-0.140308

ROID Swings by Health System

SQL

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

450 entities with >10% ROID swing

provider_type	health_system_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Hospital	Jefferson Health - New Jersey	374704	184816	189888	1.02744
Physician Group	DCH Health System	31296	121113	-89817	-0.741597
Physician Group	Baptist Health South Florida	104959	322262	-217303	-0.674305
Physician Group	Advocate Health Care	42107	128899	-86792	-0.673333
Physician Group	HCA Far West Division - Sunrise Health	37616	114572	-76956	-0.671682
Hospital	ECU Health	161153	488477	-327324	-0.670091
Physician Group	Piedmont Augusta	36139	109418	-73279	-0.669716
Physician Group	Oswego Health	42979	123665	-80686	-0.652456
Physician Group	HCA Central & West Texas Division - St Davids HealthCare	39216	111446	-72230	-0.648117
Physician Group	Sutter Health	125299	336306	-211007	-0.627426
Physician Group	UMC Health System	42445	113872	-71427	-0.627257
Physician Group	Northside Hospital System	57488	149979	-92491	-0.616693
Physician Group	BayCare Health System	150097	385434	-235337	-0.610577
Physician Group	Providence Health & Services - Southern California	102465	258664	-156199	-0.603868
Physician Group	Cape Cod Healthcare	73033	180212	-107179	-0.594738
Physician Group	Chesapeake Regional Medical Center	47392	114411	-67019	-0.585774
Physician Group	TMC Health	140585	333475	-192890	-0.578424
Physician Group	Ascension St Vincents HealthCare	69564	164153	-94589	-0.576225
Physician Group	Scripps Health	204431	480689	-276258	-0.574713
Physician Group	Jackson Health System	53705	124191	-70486	-0.567561
Physician Group	North Mississippi Health Services	114537	263682	-149145	-0.565625
Physician Group	Infirmary Health System	74642	171685	-97043	-0.565239
Physician Group	Carson Tahoe Health	48296	110983	-62687	-0.564834
Physician Group	Hoag Health System	169849	386382	-216533	-0.560412
Physician Group	Commonwealth Health	53653	121995	-68342	-0.560203
Physician Group	Childrens Health	46889	104328	-57439	-0.550562
Physician Group	Saint Clair Memorial Hospital	70608	156548	-85940	-0.548969
Physician Group	MemorialCare Health System	45446	100563	-55117	-0.548084
Physician Group	Memorial Hermann Health System	122333	269094	-146761	-0.545389
Physician Group	Circle Health	176503	388140	-211637	-0.545259
Physician Group	Salinas Valley Memorial Healthcare System	54584	119648	-65064	-0.543795
Physician Group	University of Florida Health	80404	175837	-95433	-0.542736
Physician Group	Penn Medicine Lancaster General Health	168180	364079	-195899	-0.538067
Physician Group	Atrium Health Navicent	53258	114961	-61703	-0.53673
Physician Group	Yale New Haven Health System	67628	145301	-77673	-0.534566
Physician Group	Cayuga Health	46764	100113	-53349	-0.532888
Physician Group	Boston Medical Center Corporation	408487	874403	-465916	-0.532839
Physician Group	Premier Health	118497	253560	-135063	-0.532667
Physician Group	Catholic Health	684069	1458863	-774794	-0.531094
Physician Group	Cottage Health	69212	147362	-78150	-0.530327
Physician Group	Piedmont Healthcare	367723	782768	-415045	-0.530227
Physician Group	OhioHealth	167128	355091	-187963	-0.529338
Physician Group	University of California Davis Health	160557	340326	-179769	-0.528226
Physician Group	St Lukes Hospital Health System	49399	104547	-55148	-0.527495
Physician Group	Wellstar Health System	63412	134075	-70663	-0.527041
Physician Group	Texas Health Resources	60049	125930	-65881	-0.523156
Physician Group	Banner Health	217553	456013	-238460	-0.522924
Physician Group	Huntsville Hospital Health System	142589	297273	-154684	-0.520343
Physician Group	Corewell Health West	100771	209988	-109217	-0.520111
Physician Group	Grand View Hospital	100602	209182	-108580	-0.51907
Hospital	Jefferson Torresdale Hospital	391062	257547	133515	0.51841
Physician Group	St Bernards Healthcare	61666	126453	-64787	-0.512341
Physician Group	HCA South Atlantic Division	179013	367061	-188048	-0.512307
Physician Group	Jefferson Health	173318	354986	-181668	-0.511761
Physician Group	Great Lakes Health System of Western New York	70000	141654	-71654	-0.505838
Physician Group	Prime Healthcare Services	229240	461603	-232363	-0.503383
Physician Group	Christ Hospital System	51370	103356	-51986	-0.50298
Physician Group	Yuma Regional Medical Center	52887	105813	-52926	-0.500184
Physician Group	Heritage Valley Health System	63729	126397	-62668	-0.495803
Physician Group	Emanate Health	54554	107980	-53426	-0.494777
Physician Group	Sharp HealthCare	71246	140634	-69388	-0.493394
Physician Group	Dignity Health	408706	806268	-397562	-0.493089
Physician Group	University of Pittsburgh Medical Center	167100	329053	-161953	-0.492179
Physician Group	Northeast Georgia Health System	106305	209221	-102916	-0.491901
Physician Group	Providence Health & Services - Northern California	93091	182955	-89864	-0.491181
Physician Group	Mount Carmel Health System	78009	153306	-75297	-0.491155
Physician Group	LECOM Health	83191	161869	-78678	-0.48606
Physician Group	Community Health Network	58483	113466	-54983	-0.484577
Physician Group	SUNY Upstate Medical University	215055	416604	-201549	-0.48379
Physician Group	Franciscan Missionaries of Our Lady Health System	160574	310585	-150011	-0.482995
Physician Group	Hunterdon Healthcare System	65829	127077	-61248	-0.481975
Physician Group	HCA Mid America Division	89138	171739	-82601	-0.480968
Physician Group	St Josephs / Candler	62106	119530	-57424	-0.480415
Physician Group	Kaleida Health	169574	323322	-153748	-0.475526
Hospital	Jefferson Health - Abington	249581	169209	80372	0.474987
Physician Group	Trinity Health	84604	161133	-76529	-0.474943
Physician Group	Hendrick Health	100414	190222	-89808	-0.472122
Physician Group	Lakeland Regional Health System	55108	104321	-49213	-0.471746
Physician Group	Cedars-Sinai Health System	215398	405953	-190555	-0.469402
Physician Group	Indiana University Health	134231	252739	-118508	-0.468895
Physician Group	Atrium Health Floyd	117957	222013	-104056	-0.468693
Physician Group	Emerson Hospital Health System	63986	119706	-55720	-0.465474
Physician Group	UW Medicine	67257	125491	-58234	-0.464049
Physician Group	Memorial Health	110497	205999	-95502	-0.463604
Physician Group	Mercy Medical Center	114903	213565	-98662	-0.461976
Physician Group	Covenant Health	159751	296892	-137141	-0.461922
Physician Group	Lawrence General Hospital System	85634	158860	-73226	-0.460947
Physician Group	Bon Secours Health System	256307	474847	-218540	-0.460232
Physician Group	Lehigh Valley Health Network	303794	561205	-257411	-0.458676
Physician Group	Prisma Health - Midlands	70343	129929	-59586	-0.458604
Physician Group	Forrest Health	71179	131092	-59913	-0.45703
Physician Group	Deaconess Health System	265246	485629	-220383	-0.453809
Physician Group	Singing River Health System	58843	107710	-48867	-0.45369
Physician Group	Franciscan Alliance Inc	86528	158143	-71615	-0.45285
Physician Group	Arrowhead Regional Medical Center	64520	116787	-52267	-0.447541
Physician Group	Mass General Brigham	419838	759248	-339410	-0.447034
Physician Group	Penn Highlands Healthcare	180871	327065	-146194	-0.446988
Physician Group	St Vincent Health	197858	356986	-159128	-0.445754
Physician Group	UNC Health	91948	164877	-72929	-0.442324
Physician Group	Duke LifePoint Healthcare	158689	284261	-125572	-0.441749

… 350 more rows truncated

ROID Swings by Network Source

SQL

WITH
new AS (
    SELECT 
        payer_id,
        payer_name, 
        network_name, 
        canonical_rate_source, 
        count(distinct roid) AS n_new
    FROM tq_dev.internal_dev_csong_cld_v2_4_3.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY 1,2,3,4
),
old AS (
    SELECT 
        payer_id,
        payer_name, 
        network_name, 
        canonical_rate_source, 
        count(distinct roid) AS n_old
    FROM tq_dev.internal_dev_csong_cld_v2_4_2.prod_combined_abridged
    WHERE canonical_rate_score > 1
    AND network_type != 'NARROW'
    AND network_class = 'Commercial'
    GROUP BY 1,2,3,4
)
SELECT
    payer_id,
    payer_name,
    network_name,
    canonical_rate_source,
    n_old,
    COALESCE(n_new, 0) as n_new
FROM old
FULL OUTER JOIN new USING (payer_id, payer_name, network_name, canonical_rate_source)
ORDER BY COALESCE(n_new, 0) ASC
LIMIT 10

10 entities with >10% ROID swing

payer_id	payer_name	network_name	canonical_rate_source	n_old
43	Blue Cross Blue Shield of Arizona	HMO	payer	1915594
111	Geisinger	PPO	payer	2504881
42	Anthem	IN HMO	payer_hospital	88387
111	Geisinger	PPO	payer_hospital	163961
111	Geisinger	HMO	payer_hospital	157271
43	Blue Cross Blue Shield of Arizona	HMO	payer_hospital	185342
770	Mass General Brigham Health Plan	HMO	hospital	2195
111	Geisinger	HMO	payer	2329679
779	PreferredOne	PPO	payer_hospital	1201
42	Anthem	IN HMO	payer	1452491

ROID Swings by Payer Network

SQL

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

138 entities with >10% ROID swing

payer_network_name	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Health Payment Systems (HPS) HPS Network	312740	21463	291277	13.5711
Blue Cross Blue Shield of New Jersey (Horizon) OMNIA	3708361	422427	3285934	7.7787
Blue Cross Blue Shield of Maryland & DC (CareFirst) HMO	2384080	610855	1773225	2.90286
Regence Blue Cross Blue Shield ID PPO	434282	115073	319209	2.77397
Blue Cross Blue Shield of Maryland & DC (CareFirst) Preferred Provider Network (PPO)	3864583	1130546	2734037	2.41833
Kaiser Permanente Kaiser Health Plan Mid Atlantic	450532	139108	311424	2.23872
Encore Health Network Encore Prime	353723	115865	237858	2.05289
Ambetter HMO	10934349	4317039	6617310	1.53284
Blue Cross Blue Shield of New Jersey (Horizon) PPO	5057718	2200014	2857704	1.29895
Blue Cross Blue Shield of Arizona PPO	2533890	1110375	1423515	1.28201
SelectHealth Value Network HMO	397769	174409	223360	1.28067
Sentara Health Plans PPO	3442917	1678894	1764023	1.05071
Sentara Health Plans HMO	3444092	1680572	1763520	1.04936
PreferredOne PPO	6219	703997	-697778	-0.991166
MetroPlus Health Gold	2682049	1370882	1311167	0.95644
EmblemHealth GHI / Anthem CBP	1200369	633995	566374	0.893341
Blue Cross Blue Shield of Arizona HMO	275712	2225272	-1949560	-0.8761
Anthem VA HMO	328360	2550770	-2222410	-0.87127
UnitedHealthcare Select EPO	144740320	78405289	66335031	0.846053
SelectHealth Med Network POS	468971	254730	214241	0.841051
Geisinger HMO	534510	2780495	-2245985	-0.807764
Geisinger PPO	609300	2998263	-2388963	-0.796782
Sanford Health Plan PPO	911911	514089	397822	0.773839
Anthem IN HMO	438867	1807747	-1368880	-0.75723
Nomi Health	29927	17031	12896	0.757207
Blue Cross Blue Shield of Tennessee PPO	3277141	1871886	1405255	0.750716
Anthem GA HMO	912865	522174	390691	0.748201
Baylor Scott & White Health Plan PPO	1351386	774033	577353	0.745902
Medica PPO	13060586	7500811	5559775	0.741223
Sutter Health Plus PPO	1494489	862125	632364	0.733495
Kaiser Permanente Kaiser Health Plan (HI)	81606	288415	-206809	-0.717054
Health Alliance Plan (HAP) HMO	4724855	2840076	1884779	0.663637
Health Alliance Plan (HAP) PPO	4916879	2965461	1951418	0.658049
Kaiser Permanente Kaiser Health Plan (CA)	2743023	7644673	-4901650	-0.641185
Avera Health Plans Avera Health	159671	99344	60327	0.607254
Univera Healthcare PPO	412407	1048780	-636373	-0.606775
HealthSmart Preferred PPO	966391	604470	361921	0.598741
Blue Cross Blue Shield of Illinois HMO	1191916	747458	444458	0.594626
Priority Health HMO	765676	482484	283192	0.586946
Aetna Open Access Managed Choice	40114091	96670501	-56556410	-0.585043
Regence Blue Cross Blue Shield WA PPO	2262467	1427868	834599	0.584507
Regence Blue Cross Blue Shield OR PPO	1721025	1088598	632427	0.580956
Kaiser Permanente Kaiser Health Plan Northwest	594853	377015	217838	0.577797
First Health First Health	1933134	1239677	693457	0.559385
Aetna DE HMO	516187	333968	182219	0.545618
Blue Shield of California Group PPO	14204992	9235442	4969550	0.538096
Blue Shield of California HMO	12788829	8383083	4405746	0.525552
Blue Cross Blue Shield of Michigan PPO	3791750	2489545	1302205	0.523069
Aetna EPO	10684973	7035179	3649794	0.518792
Blue Cross Blue Shield of Michigan HMO	4782903	3183192	1599711	0.502549
Anthem CA Blue Cross PPO	8175712	5560469	2615243	0.470328
Cigna ME HMO	248157	468407	-220250	-0.470211
Cigna NH HMO	258575	486401	-227826	-0.468391
Priority Health PPO	921834	628109	293725	0.467634
Kaiser Permanente Kaiser Health Plan (GA)	624540	1171468	-546928	-0.466874
Anthem CA HMO	8452809	5830185	2622624	0.449835
Blue Cross Blue Shield of Massachusetts HMO	1634330	1146448	487882	0.42556
Blue Cross Blue Shield of Massachusetts PPO	1640425	1153993	486432	0.421521
Cigna NC HMO	1563850	2661636	-1097786	-0.412448
Aetna CA HMO	9280240	6676178	2604062	0.390053
Anthem NV Choice PPO	849665	619546	230119	0.371432
PEHP (Public Employees Health Program) Preferred Network	21288	15620	5668	0.362868
Cigna New England HMO	2030936	3127670	-1096734	-0.350655
Aetna TX HMO	6486464	4820153	1666311	0.345697
Aetna National PPO	124277326	92421658	31855668	0.344678
MVP Health Care HMO	1659185	2531501	-872316	-0.344584
Harvard Pilgrim Health Care HMO	2815392	2096727	718665	0.342756
Harvard Pilgrim Health Care Choicenet PPO	2822344	2110077	712267	0.337555
Highmark Blue Cross Blue Shield PPO	1314182	987967	326215	0.330188
MVP Health Care MVP EPO / PPO	1708372	2549602	-841230	-0.329946
Anthem CT HMO	808552	608471	200081	0.328826
Blue Cross of Idaho PPO	1091671	823942	267729	0.324937
Blue Cross Blue Shield of Illinois PPO Participating Provider Options	3885275	2936310	948965	0.323183
Moda Health Connexus	1096238	834387	261851	0.313824
Aetna FL HMO	6772257	5169609	1602648	0.310013
Anthem EPO	4289662	3278559	1011103	0.308399
Anthem Blue Access Gated EPO	4279433	3273888	1005545	0.307141
Cigna AZ HMO	1357045	1943652	-586607	-0.301807
Aetna MD HMO	4128263	3176617	951646	0.299578
Blue Cross Blue Shield of Texas Blue Choice PPO	9958957	7719985	2238972	0.290023
Aetna NV HMO	1171618	918458	253160	0.275636
Midlands Choice Midlands Choice	1426927	1121003	305924	0.272902
Aetna IL HMO	3767464	2963791	803673	0.271164
Blue Cross Blue Shield of Pennsylvania (Independence) KHPE Commercial HMO/POS	473417	372825	100592	0.26981
Kaiser Permanente Kaiser Health Plan (CO)	226244	307667	-81423	-0.264647
Blue Cross Blue Shield of Hawaii (HMSA) HMO	564219	448189	116030	0.258886
EmblemHealth DC37 Med-Team	3479032	2765985	713047	0.257791
Cigna AL HMO	921248	1240118	-318870	-0.257129
Blue Cross Blue Shield of Nebraska Networkblue PPO	1244863	991394	253469	0.255669
UnitedHealthcare Choice Plus	145716557	116997485	28719072	0.245467
Blue Cross Blue Shield of Texas HMO	8831738	7109282	1722456	0.242283
Cigna NJ HMO	1767291	2320123	-552832	-0.238277
EmblemHealth GHI HMO	3220873	2605429	615444	0.236216
EmblemHealth HIP Prime POS	3256174	2634172	622002	0.236128
EmblemHealth HIP HMO	3249628	2632349	617279	0.234497
Cigna TN HMO	1168741	1522693	-353952	-0.232451
UnitedHealthcare HMO	141309214	114711371	26597843	0.231868
Blue Cross Blue Shield of Hawaii (HMSA) PPO	622736	507296	115440	0.227559
Blue Cross Blue Shield of North Carolina HMO	540484	440486	99998	0.227017
Anthem CO Blue Preferred PPO	1004309	1297277	-292968	-0.225833

… 38 more rows truncated

ROID Swings by Provider Type

SQL

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

1 entities with >10% ROID swing

provider_type	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Physician Group	783576510	666271324	117305186	0.176062

ROID Swings by Rate Class

SQL

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

2 entities with >10% ROID swing

canonical_rate_class	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
Raw	1033273036	896883236	136389800	0.152071
Transform	34936052	39857636	-4921584	-0.123479

ROID Swings by Rate Score

SQL

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

2 entities with >10% ROID swing

canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
2	2.23487e+08	1.83132e+08	4.0355e+07	0.22036
3	6.67676e+08	5.78182e+08	8.94934e+07	0.154784

ROID Swings by Rate Score By Provider

SQL

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

8 entities with >10% ROID swing

provider_type	canonical_rate_score	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
ASC	3	921949	815612	106337	0.130377
Hospital	2	19420891	22204998	-2784107	-0.125382
Imaging Center	3	25	171	-146	-0.853801
Imaging Center	2	16	58	-42	-0.724138
Laboratory	3	5164662	4680385	484277	0.103469
Physician Group	4	12955836	8019650	4936186	0.615511
Physician Group	2	188311543	158538986	29772557	0.187793
Physician Group	3	580426923	497663834	82763089	0.166303

ROID Swings by Rate Source

SQL

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

1 entities with >10% ROID swing

canonical_rate_source	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
payer	943807507	816358002	127449505	0.15612

ROID Swings by Service Line

SQL

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

17 entities with >10% ROID swing

service_line	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
DME and Supplies	16808468	3023976	13784492	4.5584
Diabetes	1102697	453115	649582	1.43359
Consultative and Preventative Care	49055566	28015137	21040429	0.751038
Transplant	119479	424893	-305414	-0.718802
Emergency	1923916	1248346	675570	0.541172
Lab/Path	104027535	73290129	30737406	0.419394
Infectious Disease	64715050	48113604	16601446	0.345047
Behavioral Health	18162534	13606135	4556399	0.334878
Rehab	16838298	12992251	3846047	0.296026
Anesthesia	31229702	43751368	-12521666	-0.286201
Radiology	196224072	161191315	35032757	0.217337
MSK	88945060	112868181	-23923121	-0.211956
Hematology	2827181	3545931	-718750	-0.202697
Cardiovascular	46674886	57711486	-11036600	-0.191237
Oncology	21575207	18123790	3451417	0.190436
Reproductive	14453928	16458716	-2004788	-0.121807
Gastrointestinal	40804002	45425306	-4621304	-0.101734

ROID Swings by State

SQL

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

24 entities with >10% ROID swing

state	new_roid_count	old_roid_count	roid_count_change	roid_count_pct_change
MI	46885900	30004389	16881511	0.562635
DC	12166188	7989344	4176844	0.522802
NJ	41803877	27992823	13811054	0.493378
DE	4098395	2768115	1330280	0.480573
CA	146219471	106586723	39632748	0.371836
NH	4446295	5738174	-1291879	-0.225138
TX	105860963	86642463	19218500	0.221814
OK	16893875	13902104	2991771	0.215203
ID	7604758	6287757	1317001	0.209455
NE	10336481	8622872	1713609	0.198728
NY	100708916	84099989	16608927	0.19749
ME	4286601	5261053	-974452	-0.18522
IL	39663383	33662944	6000439	0.178251
RI	3573868	3103478	470390	0.151569
LA	23591782	20762891	2828891	0.136247
NV	9509452	8403219	1106233	0.131644
KY	15364538	13658468	1706070	0.124909
WV	5653878	5043904	609974	0.120933
TN	22443036	20179663	2263373	0.112161
CT	9644511	8688979	955532	0.109971
CO	12768955	14326405	-1557450	-0.108712
FL	67070113	60502515	6567598	0.108551
IN	18641017	20808652	-2167635	-0.10417
MO	25486868	23138578	2348290	0.101488

Summary Statistics​

Rate Object Space Dropoffs​

Dropped Bill Billing​

Dropped Networks​

Dropped Payers​

Dropped Provider Networks​

Dropped Providers​

Large Rate Swings (>10%)​

Rate Swings by Bill Provider​

Rate Swings by Network​

Rate Swings by Network Provider​

Rate Swings by Network Provider Bill​

Large ROID Count Swings (>10%)​

ROID Swings by Contract Methodology​

ROID Swings by Crosswalk Method​

ROID Swings by Gross Charge Type​

ROID Swings by Health System​

ROID Swings by Network Source​

ROID Swings by Payer Network​

ROID Swings by Provider Type​

ROID Swings by Rate Class​

ROID Swings by Rate Score​

ROID Swings by Rate Score By Provider​

ROID Swings by Rate Source​

ROID Swings by Service Line​

ROID Swings by State​

On this page:

Summary Statistics

Rate Object Space Dropoffs

Dropped Bill Billing

Dropped Networks

Dropped Payers

Dropped Provider Networks

Dropped Providers

Large Rate Swings (>10%)

Rate Swings by Bill Provider

Rate Swings by Network

Rate Swings by Network Provider

Rate Swings by Network Provider Bill

Large ROID Count Swings (>10%)

ROID Swings by Contract Methodology

ROID Swings by Crosswalk Method

ROID Swings by Gross Charge Type

ROID Swings by Health System

ROID Swings by Network Source

ROID Swings by Payer Network

ROID Swings by Provider Type

ROID Swings by Rate Class

ROID Swings by Rate Score

ROID Swings by Rate Score By Provider

ROID Swings by Rate Source

ROID Swings by Service Line

ROID Swings by State