v2_1_4

Generated: 2025-10-10 08:28:55

---

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): 126,181,016

Click to see SQL

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

2. cbsa Medicare Rate Outliers

Rates vs cbsa Medicare Rates

Total Rates: 657,449,078 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	331,615,900	14,762,806	4,054,950
Short Term Acute Care Hospital	Outpatient	158,072,657	19,725,123	2,950,180
ASC	Outpatient	57,401,807	14,231,328	771,596
Short Term Acute Care Hospital	Inpatient	42,572,697	362,241	33,331
Critical Access Hospital	Outpatient	40,840,144	5,851,887	1,258,865
Critical Access Hospital	Inpatient	6,512,501	697,985	10,896
Childrens Hospital	Outpatient	5,731,755	849,550	128,369
Rehabilitation Hospital	Outpatient	5,106,837	1,508,642	63,608
Imaging Center	Professional	4,476,652	452,227	3,536
Laboratory	Professional	2,947,514	1,588,865	2,508
Childrens Hospital	Inpatient	1,484,209	0	4,827
Rehabilitation Hospital	Inpatient	686,405	14,986	2,232

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_1_4.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: 657,449,078 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	331,615,900	14,762,806	4,054,950
Short Term Acute Care Hospital	Outpatient	158,072,657	19,742,527	2,935,287
ASC	Outpatient	57,401,807	14,068,537	834,628
Short Term Acute Care Hospital	Inpatient	42,572,697	781,596	32,183
Critical Access Hospital	Outpatient	40,840,144	7,478,088	357,884
Critical Access Hospital	Inpatient	6,512,501	713,776	11,790
Childrens Hospital	Outpatient	5,731,755	850,893	113,095
Rehabilitation Hospital	Outpatient	5,106,837	1,510,762	59,518
Imaging Center	Professional	4,476,652	452,227	3,536
Laboratory	Professional	2,947,514	1,589,076	2,508
Childrens Hospital	Inpatient	1,484,209	13,154	3,712
Rehabilitation Hospital	Inpatient	686,405	16,802	2,218

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_1_4.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,357,240,430	63.0%
1	126,181,016	6.0%
2	17,430,917	1.0%
3	64,026,746	3.0%
4	551,421,809	26.0%
5	24,569,606	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_1_4.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
Physician Group	Professional	64555	149,813	16,217	11.0%
Short Term Acute Care Hospital	Outpatient	38240	22,632	14,269	63.0%
Physician Group	Professional	93247	69,217	13,741	20.0%
Physician Group	Professional	93243	69,232	12,094	18.0%
Short Term Acute Care Hospital	Outpatient	86901	29,593	9,443	32.0%
Short Term Acute Care Hospital	Outpatient	86885	25,088	9,068	36.0%
Short Term Acute Care Hospital	Outpatient	88333	25,637	9,034	35.0%
Short Term Acute Care Hospital	Outpatient	86900	27,391	8,838	32.0%
Short Term Acute Care Hospital	Outpatient	86886	26,249	8,810	34.0%
Short Term Acute Care Hospital	Outpatient	54411	19,148	8,224	43.0%
Short Term Acute Care Hospital	Outpatient	0674T	13,517	7,573	56.0%
Short Term Acute Care Hospital	Outpatient	86902	24,858	7,450	30.0%
Short Term Acute Care Hospital	Outpatient	85097	24,104	7,281	30.0%
ASC	Outpatient	20696	17,391	7,051	41.0%
ASC	Outpatient	45347	21,863	6,551	30.0%
Short Term Acute Care Hospital	Outpatient	0680T	13,684	6,413	47.0%
ASC	Outpatient	45389	22,914	6,246	27.0%
Short Term Acute Care Hospital	Outpatient	86905	24,189	6,021	25.0%
ASC	Outpatient	21243	19,767	5,887	30.0%
Physician Group	Professional	43249	166,913	5,714	3.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_1_4.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	117,758	38,267	33.0%
Physician Group	Professional	36482	151,945	35,130	23.0%
Physician Group	Professional	31295	152,878	34,974	23.0%
Physician Group	Professional	37241	151,018	34,581	23.0%
Physician Group	Professional	36466	149,675	34,530	23.0%
Physician Group	Professional	37191	150,634	34,439	23.0%
Physician Group	Professional	37244	152,131	34,273	23.0%
Physician Group	Professional	37238	151,125	34,014	23.0%
Physician Group	Professional	31627	153,700	33,947	22.0%
Physician Group	Professional	36906	146,566	33,945	23.0%
Physician Group	Professional	36465	147,408	33,925	23.0%
Physician Group	Professional	49450	150,765	33,374	22.0%
Physician Group	Professional	22513	147,902	33,298	23.0%
Physician Group	Professional	69706	137,757	32,987	24.0%
Physician Group	Professional	31298	148,308	32,889	22.0%
Physician Group	Professional	36909	148,819	32,539	22.0%
Physician Group	Professional	37230	146,280	32,494	22.0%
Physician Group	Professional	22514	146,583	32,256	22.0%
Physician Group	Professional	37228	154,313	32,099	21.0%
Physician Group	Professional	50593	148,276	32,039	22.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_1_4.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