v2_2_1

Generated: 2025-11-03 22:14:27

---

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): 147,769,498

Click to see SQL

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

2. cbsa Medicare Rate Outliers

Rates vs cbsa Medicare Rates

Total Rates: 727,405,875 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	395,459,411	23,172,348	2,047,057
Hospital	Outpatient	217,013,922	31,691,477	3,686,531
ASC	Outpatient	55,958,200	13,872,024	732,211
Hospital	Inpatient	52,316,257	0	0
Imaging Center	Professional	3,814,375	387,691	2,331
Laboratory	Professional	2,843,710	1,507,997	3,307

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_1.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: 727,405,875 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	395,459,411	23,172,348	2,047,057
Hospital	Outpatient	217,013,922	31,632,295	3,724,188
ASC	Outpatient	55,958,200	13,588,985	837,834
Hospital	Inpatient	52,316,257	337,655	88
Imaging Center	Professional	3,814,375	387,691	2,331
Laboratory	Professional	2,843,710	1,507,997	3,307

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_1.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,822,051,746	68.0%
1	147,769,498	5.0%
2	17,080,346	1.0%
3	63,496,761	2.0%
4	620,911,565	23.0%
5	25,917,203	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_1.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
Hospital	Outpatient	86885	41,898	23,201	55.0%
Physician Group	Professional	64555	221,880	22,218	10.0%
Physician Group	Professional	93247	103,422	20,638	20.0%
Hospital	Outpatient	86886	42,211	19,338	46.0%
Physician Group	Professional	93243	103,551	18,815	18.0%
Hospital	Outpatient	38240	28,091	18,021	64.0%
Physician Group	Professional	00540	156,028	16,404	11.0%
Physician Group	Professional	00218	156,681	16,161	10.0%
Physician Group	Professional	00792	157,498	16,152	10.0%
Physician Group	Professional	00474	155,815	16,143	10.0%
Physician Group	Professional	00406	156,820	16,142	10.0%
Physician Group	Professional	00625	156,782	16,088	10.0%
Physician Group	Professional	00604	156,749	16,087	10.0%
Physician Group	Professional	00670	158,311	16,085	10.0%
Hospital	Outpatient	86901	44,348	16,006	36.0%
Hospital	Outpatient	86900	41,213	15,452	37.0%
Physician Group	Professional	01173	154,894	15,434	10.0%
Hospital	Outpatient	86880	44,294	15,278	34.0%
Physician Group	Professional	00210	158,457	14,372	9.0%
Physician Group	Professional	00797	158,554	14,360	9.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_1.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	36482	223,436	48,195	22.0%
Physician Group	Professional	31295	222,973	47,857	21.0%
Physician Group	Professional	37191	221,491	47,692	22.0%
Physician Group	Professional	36466	222,893	47,406	21.0%
Physician Group	Professional	36906	218,906	47,132	22.0%
Physician Group	Professional	37241	220,847	47,107	21.0%
Physician Group	Professional	G0500	221,508	47,002	21.0%
Physician Group	Professional	36465	218,828	46,986	21.0%
Physician Group	Professional	31627	223,131	46,950	21.0%
Physician Group	Professional	37244	222,135	46,816	21.0%
Physician Group	Professional	37238	222,570	46,799	21.0%
Physician Group	Professional	22513	220,070	46,589	21.0%
Physician Group	Professional	31298	218,409	45,398	21.0%
Physician Group	Professional	22514	217,464	45,271	21.0%
Physician Group	Professional	37252	219,488	44,655	20.0%
Physician Group	Professional	37228	227,257	44,032	19.0%
Physician Group	Professional	37242	211,570	40,953	19.0%
Physician Group	Professional	37229	212,836	40,414	19.0%
Physician Group	Professional	36903	210,709	39,416	19.0%
Physician Group	Professional	37243	210,006	38,193	18.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_1.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