Version: 2.4

v2_2_2

Generated: 2025-11-13 18:36:41

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): 84,072,844

Click to see SQL

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

2. cbsa Medicare Rate Outliers

Rates vs cbsa Medicare Rates

Total Rates: 542,002,192 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	222,304,621	42,094,745	1,020,127
Hospital	Outpatient	220,659,236	31,476,348	3,395,224
Hospital	Inpatient	53,444,599	0	0
ASC	Outpatient	39,366,170	10,738,789	590,858
Imaging Center	Professional	3,664,597	377,671	2,246
Laboratory	Professional	2,562,969	1,357,806	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_2.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: 542,002,192 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	222,304,621	42,094,745	1,020,127
Hospital	Outpatient	220,659,236	31,399,042	3,412,397
Hospital	Inpatient	53,444,599	337,492	74
ASC	Outpatient	39,366,170	10,317,959	695,119
Imaging Center	Professional	3,664,597	377,671	2,246
Laboratory	Professional	2,562,969	1,357,806	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_2.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	2,083,063,418	77.0%
1	84,072,844	3.0%
2	29,776,751	1.0%
3	114,015,697	4.0%
4	370,884,924	14.0%
5	27,324,820	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_2.prod_combined_all
            GROUP BY canonical_rate_score
            ORDER BY canonical_rate_score
            

4.1. Canonical Rate Score Distribution by Provider Type

Distribution of Canonical Rate Scores by Provider Type

provider_type	canonical_rate_score	record_count	percentage
ASC	0	151,999,311	79.0%
ASC	1	630,036	0.0%
ASC	2	331,587	0.0%
ASC	3	239,177	0.0%
ASC	4	38,795,406	20.0%
Hospital	0	474,293,673	61.0%
Hospital	1	34,525,422	4.0%
Hospital	2	17,966,880	2.0%
Hospital	3	66,034,076	8.0%
Hospital	4	164,476,321	21.0%
Hospital	5	25,626,558	3.0%
Imaging Center	0	43,813,340	92.0%
Imaging Center	1	119,584	0.0%
Imaging Center	2	98	0.0%
Imaging Center	3	354	0.0%
Imaging Center	4	3,664,145	8.0%
Laboratory	0	10,380,274	80.0%
Laboratory	1	98,747	1.0%
Laboratory	2	372,080	3.0%
Laboratory	3	864,506	7.0%
Laboratory	4	174,467	1.0%
Laboratory	5	1,151,916	9.0%
Physician Group	0	1,402,576,820	84.0%
Physician Group	1	48,699,055	3.0%
Physician Group	2	11,106,106	1.0%
Physician Group	3	46,877,584	3.0%
Physician Group	4	163,774,585	10.0%
Physician Group	5	546,346	0.0%

Click to see SQL

            SELECT
                provider_type,
                canonical_rate_score,
                COUNT(*) as record_count,
                ROUND(1.000 * COUNT(*) / SUM(COUNT(*)) OVER(PARTITION BY provider_type), 2) as percentage
            FROM tq_dev.internal_dev_csong_cld_v2_2_2.prod_combined_all
            GROUP BY provider_type, canonical_rate_score
            ORDER BY provider_type, 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	38240	28,066	17,222	61.0%
Hospital	Outpatient	88333	36,865	12,257	33.0%
Hospital	Outpatient	85097	34,918	9,794	28.0%
Hospital	Outpatient	54411	22,149	8,743	39.0%
Hospital	Outpatient	0674T	14,518	7,849	54.0%
Hospital	Outpatient	86975	32,531	7,438	23.0%
Hospital	Outpatient	88319	35,882	7,093	20.0%
Hospital	Outpatient	75746	36,448	7,008	19.0%
Hospital	Outpatient	0680T	15,104	6,967	46.0%
Hospital	Outpatient	75870	35,058	6,939	20.0%
Hospital	Outpatient	75887	35,759	6,922	19.0%
ASC	Outpatient	20696	16,724	6,749	40.0%
Hospital	Outpatient	54417	21,792	6,728	31.0%
Physician Group	Professional	64555	66,553	6,439	10.0%
ASC	Outpatient	45347	20,951	6,279	30.0%
Hospital	Outpatient	75731	35,263	6,207	18.0%
ASC	Outpatient	45389	21,975	6,011	27.0%
Hospital	Outpatient	75705	35,693	5,928	17.0%
Hospital	Outpatient	74485	36,268	5,807	16.0%
Hospital	Outpatient	50555	26,511	5,739	22.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_2.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	G0500	198,171	42,281	21.0%
Physician Group	Professional	01996	80,016	24,191	30.0%
Physician Group	Professional	36482	106,662	23,804	22.0%
Physician Group	Professional	36466	103,531	22,258	21.0%
Physician Group	Professional	37252	110,770	21,202	19.0%
Physician Group	Professional	36465	101,926	20,752	20.0%
Physician Group	Professional	45388	119,577	20,355	17.0%
Physician Group	Professional	58563	112,428	19,393	17.0%
Hospital	Outpatient	72125	40,963	17,412	43.0%
Hospital	Outpatient	37182	35,038	17,222	49.0%
Hospital	Outpatient	31627	24,633	17,188	70.0%
Hospital	Outpatient	72131	40,909	17,089	42.0%
Hospital	Outpatient	71250	41,165	16,811	41.0%
Hospital	Outpatient	72128	40,597	16,763	41.0%
Physician Group	Professional	37228	79,722	16,733	21.0%
Hospital	Outpatient	76390	34,594	16,544	48.0%
Physician Group	Professional	31627	83,897	16,495	20.0%
Physician Group	Professional	19084	133,052	16,441	12.0%
Hospital	Outpatient	29826	26,536	16,342	62.0%
Hospital	Outpatient	74150	40,503	16,306	40.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_2.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
            

Overview​

1. Outlier Score Analysis​

Records with Outlier Canonical Rate Score​

2. cbsa Medicare Rate Outliers​

Rates vs cbsa Medicare Rates​

3. Medicare Rate Outliers​

Rates vs Facility-Specific Medicare Rates​

4. Canonical Rate Score Distribution​

Distribution of Canonical Rate Scores​

4.1. Canonical Rate Score Distribution by Provider Type​

Distribution of Canonical Rate Scores by Provider Type​

5. Extreme Rate Ratios by Code Combination​

Code Combinations with Most Rates < 0.2x Medicare​

Code Combinations with Most Rates > 10x Medicare​

On this page:

Overview

1. Outlier Score Analysis

Records with Outlier Canonical Rate Score

2. cbsa Medicare Rate Outliers

Rates vs cbsa Medicare Rates

3. Medicare Rate Outliers

Rates vs Facility-Specific Medicare Rates

4. Canonical Rate Score Distribution

Distribution of Canonical Rate Scores

4.1. Canonical Rate Score Distribution by Provider Type

Distribution of Canonical Rate Scores by Provider Type

5. Extreme Rate Ratios by Code Combination

Code Combinations with Most Rates < 0.2x Medicare

Code Combinations with Most Rates > 10x Medicare