Version: 2.4

v2_3_2

Generated: 2026-01-21 07:27:57

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): 96,716,862

Click to see SQL

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

2. cbsa Medicare Rate Outliers

Rates vs cbsa Medicare Rates

Total Rates: 565,524,503 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	242,668,445	44,194,436	0
Hospital	Outpatient	228,215,021	19,816,818	3,714,636
Hospital	Inpatient	56,800,809	0	0
ASC	Outpatient	21,345,654	3,122,016	355,473
Laboratory	Professional	12,904,536	6,757,966	0
Imaging Center	Professional	3,590,038	417,730	1,453

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_3_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: 565,524,503 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	242,668,445	44,194,436	0
Hospital	Outpatient	228,215,021	19,681,288	3,736,306
Hospital	Inpatient	56,800,809	378,098	109
ASC	Outpatient	21,345,654	2,980,866	441,138
Laboratory	Professional	12,904,536	6,757,966	0
Imaging Center	Professional	3,590,038	417,730	1,453

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_3_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,856,389,431	81.0%
1	96,716,862	3.0%
2	69,712,920	2.0%
3	283,687,252	8.0%
4	179,159,877	5.0%
5	32,964,454	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_3_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	177,777,083	88.0%
ASC	1	3,154,725	2.0%
ASC	2	167,727	0.0%
ASC	3	231,661	0.0%
ASC	4	20,946,266	10.0%
Hospital	0	911,849,787	73.0%
Hospital	1	49,451,873	4.0%
Hospital	2	21,200,915	2.0%
Hospital	3	86,048,282	7.0%
Hospital	4	150,770,564	12.0%
Hospital	5	26,996,069	2.0%
Imaging Center	0	50,750,889	93.0%
Imaging Center	1	147,311	0.0%
Imaging Center	2	88	0.0%
Imaging Center	3	350	0.0%
Imaging Center	4	3,589,600	7.0%
Laboratory	0	89,071,647	87.0%
Laboratory	1	363,973	0.0%
Laboratory	2	1,750,080	2.0%
Laboratory	3	4,569,582	4.0%
Laboratory	4	698,253	1.0%
Laboratory	5	5,886,621	6.0%
Physician Group	0	1,626,940,025	85.0%
Physician Group	1	43,598,980	2.0%
Physician Group	2	46,594,110	2.0%
Physician Group	3	192,837,377	10.0%
Physician Group	4	3,155,194	0.0%
Physician Group	5	81,764	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_3_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	Inpatient	795	35,218	78	0.0%
Hospital	Inpatient	0640-1	32,335	45	0.0%
Hospital	Inpatient	730	30,513	41	0.0%
Hospital	Inpatient	833	33,629	40	0.0%
Hospital	Inpatient	298	26,139	39	0.0%
Hospital	Inpatient	284	32,479	37	0.0%
Hospital	Inpatient	0640-2	32,444	37	0.0%
Hospital	Inpatient	310	39,077	36	0.0%
Hospital	Inpatient	311	36,035	35	0.0%
Hospital	Inpatient	724	28,469	35	0.0%
Hospital	Inpatient	292	39,411	35	0.0%
Hospital	Inpatient	395	36,341	34	0.0%
Hospital	Inpatient	313	36,075	34	0.0%
Hospital	Inpatient	316	35,038	33	0.0%
Hospital	Inpatient	390	38,363	33	0.0%
Hospital	Inpatient	951	33,607	33	0.0%
Hospital	Inpatient	950	36,966	33	0.0%
Hospital	Inpatient	761	32,884	33	0.0%
Hospital	Inpatient	192	36,597	32	0.0%
Hospital	Inpatient	349	34,103	32	0.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_3_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
Hospital	Outpatient	37182	38,442	19,536	51.0%
Hospital	Outpatient	72125	44,879	19,085	43.0%
Hospital	Outpatient	31627	28,136	19,081	68.0%
Hospital	Outpatient	62284	38,997	19,018	49.0%
Hospital	Outpatient	72131	44,910	18,795	42.0%
Hospital	Outpatient	72128	44,682	18,452	41.0%
Hospital	Outpatient	71250	45,128	18,398	41.0%
Hospital	Outpatient	76390	38,754	18,255	47.0%
Hospital	Outpatient	70486	45,088	17,932	40.0%
Hospital	Outpatient	72192	44,768	17,691	40.0%
Hospital	Outpatient	74150	44,324	17,687	40.0%
Hospital	Outpatient	70450	45,318	17,667	39.0%
Hospital	Outpatient	70480	44,412	17,659	40.0%
Hospital	Outpatient	73200	43,598	17,234	40.0%
Hospital	Outpatient	73700	43,496	16,918	39.0%
Hospital	Outpatient	29826	27,183	16,913	62.0%
Hospital	Outpatient	70490	43,416	16,562	38.0%
Hospital	Outpatient	43775	32,470	15,640	48.0%
Hospital	Outpatient	71275	44,209	15,469	35.0%
Hospital	Outpatient	74170	44,350	15,319	35.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_3_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