v2_2_0

Generated: 2025-10-15 10:33:24

---

Overview

This analysis examines the accuracy of canonical rates by analyzing scoring patterns and identifying potential outliers based on Medicare rate comparisons.

1. Outlier Score Analysis

Records with Outlier Canonical Rate Score

• Total Records with canonical_rate_score = 1 (outliers): 191,498,188

Click to see SQL

            SELECT COUNT(*) as outlier_score_count
            FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_all
            WHERE canonical_rate_score = 1
            

2. cbsa Medicare Rate Outliers

Rates vs cbsa Medicare Rates

Total Rates: 677,501,156 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	354,084,445	9,348,807	5,243,080
Short Term Acute Care Hospital	Outpatient	158,914,869	21,064,631	3,019,492
ASC	Outpatient	55,843,293	13,788,421	736,987
Short Term Acute Care Hospital	Inpatient	42,262,363	369,500	0
Critical Access Hospital	Outpatient	40,442,805	6,896,664	1,461,007
Critical Access Hospital	Inpatient	6,295,439	710,453	0
Childrens Hospital	Outpatient	5,714,742	892,693	140,229
Rehabilitation Hospital	Outpatient	5,191,114	1,539,895	69,180
Imaging Center	Professional	3,809,018	387,507	3,117
Laboratory	Professional	2,795,515	1,463,916	3,338
Childrens Hospital	Inpatient	1,466,269	0	0
Rehabilitation Hospital	Inpatient	681,284	14,986	0

Click to see SQL

            SELECT
                provider_type,
                bill_type,
                COUNT(*) as count,
                COUNT(CASE WHEN canonical_rate < 0.8 * cbsa_avg_medicare_rate THEN 1 END) as below_80pct_count,
                COUNT(CASE WHEN canonical_rate > 15 * cbsa_avg_medicare_rate THEN 1 END) as above_15x_count
            FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_all
            WHERE canonical_rate_score > 1
            GROUP BY provider_type, bill_type
            ORDER BY count DESC
            

3. Medicare Rate Outliers

Rates vs Facility-Specific Medicare Rates

Total Rates: 677,501,156 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	354,084,445	9,348,807	5,243,080
Short Term Acute Care Hospital	Outpatient	158,914,869	21,081,191	3,004,514
ASC	Outpatient	55,843,293	13,648,242	796,646
Short Term Acute Care Hospital	Inpatient	42,262,363	789,822	37
Critical Access Hospital	Outpatient	40,442,805	8,240,474	477,955
Critical Access Hospital	Inpatient	6,295,439	666,926	254
Childrens Hospital	Outpatient	5,714,742	894,089	124,857
Rehabilitation Hospital	Outpatient	5,191,114	1,541,353	64,897
Imaging Center	Professional	3,809,018	387,507	3,117
Laboratory	Professional	2,795,515	1,463,376	3,338
Childrens Hospital	Inpatient	1,466,269	13,155	0
Rehabilitation Hospital	Inpatient	681,284	16,842	0

Click to see SQL

            SELECT
                provider_type,
                bill_type,
                COUNT(*) as count,
                COUNT(CASE WHEN canonical_rate < 0.8 * medicare_rate THEN 1 END) as below_80pct_count,
                COUNT(CASE WHEN canonical_rate > 15 * medicare_rate THEN 1 END) as above_15x_count
            FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_all
            WHERE canonical_rate_score > 1
            GROUP BY provider_type, bill_type
            ORDER BY count DESC
            

4. Canonical Rate Score Distribution

Distribution of Canonical Rate Scores

canonical_rate_score	record_count	percentage
0	1,827,744,833	68.0%
1	191,498,188	7.0%
2	4,719,801	0.0%
3	76,395,689	3.0%
4	571,796,487	21.0%
5	24,589,179	1.0%

Click to see SQL

            SELECT
                canonical_rate_score,
                COUNT(*) as record_count,
                ROUND(1.000 * COUNT(*) / SUM(COUNT(*)) OVER(), 2) as percentage
            FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_all
            GROUP BY canonical_rate_score
            ORDER BY canonical_rate_score
            

5. Extreme Rate Ratios by Code Combination

Code Combinations with Most Rates < 0.2x Medicare

Top 20 provider_type, bill_type, billing_code combinations with the most rates below 20% of Medicare rates:

provider_type	bill_type	billing_code	total_records	below_20pct_count	below_20pct_percentage
Short Term Acute Care Hospital	Outpatient	38240	22,433	14,305	64.0%
Short Term Acute Care Hospital	Outpatient	86885	27,739	13,955	50.0%
Short Term Acute Care Hospital	Outpatient	86886	28,578	13,193	46.0%
Physician Group	Professional	64555	190,904	12,428	7.0%
Short Term Acute Care Hospital	Outpatient	86901	29,282	9,939	34.0%
Short Term Acute Care Hospital	Outpatient	86880	29,912	9,911	33.0%
Physician Group	Professional	93247	71,224	9,871	14.0%
Short Term Acute Care Hospital	Outpatient	88333	25,498	9,163	36.0%
Short Term Acute Care Hospital	Outpatient	86900	26,953	9,094	34.0%
Physician Group	Professional	93243	71,125	8,876	12.0%
Short Term Acute Care Hospital	Outpatient	54411	18,591	8,554	46.0%
Short Term Acute Care Hospital	Outpatient	86902	24,976	8,552	34.0%
Short Term Acute Care Hospital	Outpatient	0674T	13,376	7,536	56.0%
Short Term Acute Care Hospital	Outpatient	85097	24,040	7,425	31.0%
ASC	Outpatient	20696	16,810	6,945	41.0%
Short Term Acute Care Hospital	Outpatient	86905	23,964	6,588	27.0%
ASC	Outpatient	45347	21,207	6,436	30.0%
Short Term Acute Care Hospital	Outpatient	0680T	13,543	6,386	47.0%
ASC	Outpatient	45389	22,181	6,110	28.0%
Short Term Acute Care Hospital	Outpatient	86850	29,799	5,885	20.0%

Click to see SQL

            SELECT
                provider_type,
                bill_type,
                billing_code,
                COUNT(*) as total_records,
                COUNT(CASE WHEN canonical_rate < 0.2 * medicare_rate THEN 1 END) as below_20pct_count,
                ROUND(1.000 * COUNT(CASE WHEN canonical_rate < 0.2 * medicare_rate THEN 1 END) / COUNT(*), 2) as below_20pct_percentage
            FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_all
            WHERE canonical_rate_score > 1
                AND medicare_rate IS NOT NULL
                AND medicare_rate > 0
            GROUP BY provider_type, bill_type, billing_code
            HAVING COUNT(CASE WHEN canonical_rate < 0.2 * medicare_rate THEN 1 END) > 0
            ORDER BY below_20pct_count DESC, below_20pct_percentage DESC
            LIMIT 20
            

Code Combinations with Most Rates > 10x Medicare

Top 20 provider_type, bill_type, billing_code combinations with the most rates above 10x Medicare rates:

provider_type	bill_type	billing_code	total_records	above_10x_count	above_10x_percentage
Physician Group	Professional	01996	179,339	61,197	34.0%
Physician Group	Professional	36482	221,934	48,147	22.0%
Physician Group	Professional	31295	220,081	47,839	22.0%
Physician Group	Professional	37191	221,122	47,712	22.0%
Physician Group	Professional	36466	220,024	47,411	22.0%
Physician Group	Professional	37241	219,355	47,142	21.0%
Physician Group	Professional	36906	215,767	47,128	22.0%
Physician Group	Professional	36465	215,334	47,005	22.0%
Physician Group	Professional	G0500	211,107	46,949	22.0%
Physician Group	Professional	31627	221,545	46,937	21.0%
Physician Group	Professional	37244	220,740	46,932	21.0%
Physician Group	Professional	37238	218,601	46,798	21.0%
Physician Group	Professional	22513	215,132	46,566	22.0%
Physician Group	Professional	31298	213,961	45,469	21.0%
Physician Group	Professional	22514	212,679	45,252	21.0%
Physician Group	Professional	37252	216,158	44,698	21.0%
Physician Group	Professional	37228	224,589	44,055	20.0%
Physician Group	Professional	37242	209,996	40,962	20.0%
Physician Group	Professional	37229	210,143	40,456	19.0%
Physician Group	Professional	36903	207,314	39,418	19.0%

Click to see SQL

            SELECT
                provider_type,
                bill_type,
                billing_code,
                COUNT(*) as total_records,
                COUNT(CASE WHEN canonical_rate > 10 * medicare_rate THEN 1 END) as above_10x_count,
                ROUND(1.000 * COUNT(CASE WHEN canonical_rate > 10 * medicare_rate THEN 1 END) / COUNT(*), 2) as above_10x_percentage
            FROM tq_dev.internal_dev_csong_cld_v2_2_0.prod_combined_all
            WHERE canonical_rate_score > 1
                AND medicare_rate IS NOT NULL
                AND medicare_rate > 0
            GROUP BY provider_type, bill_type, billing_code
            HAVING COUNT(CASE WHEN canonical_rate > 10 * medicare_rate THEN 1 END) > 0
            ORDER BY above_10x_count DESC, above_10x_percentage DESC
            LIMIT 20