Version: 3.0

v2_4_2

Generated: 2026-03-17 19:09:11

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): 144,504,678

Click to see SQL

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

2. cbsa Medicare Rate Outliers

Rates vs cbsa Medicare Rates

Total Rates: 1,401,695,115 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	891,113,579	110,823,376	0
Hospital	Outpatient	338,543,345	32,850,707	3,659,012
Hospital	Inpatient	90,027,733	4,249,634	0
ASC	Outpatient	61,116,392	9,976,410	789,506
Laboratory	Professional	15,678,994	8,486,431	0
Imaging Center	Professional	5,215,072	623,778	3,336

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_4_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: 1,401,695,115 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	891,113,579	110,823,376	0
Hospital	Outpatient	338,543,345	32,564,014	3,672,130
Hospital	Inpatient	90,027,733	4,238,008	10,168
ASC	Outpatient	61,116,392	9,566,490	1,004,249
Laboratory	Professional	15,678,994	8,486,431	0
Imaging Center	Professional	5,215,072	623,778	3,336

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_4_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	5,751,062,933	79.0%
1	144,504,678	2.0%
2	244,063,386	3.0%
3	782,416,939	11.0%
4	333,620,975	5.0%
5	41,593,815	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_4_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	264,471,374	79.0%
ASC	1	10,580,614	3.0%
ASC	2	669,556	0.0%
ASC	3	835,822	0.0%
ASC	4	59,611,014	18.0%
Hospital	0	1,994,330,589	79.0%
Hospital	1	87,446,193	3.0%
Hospital	2	30,913,594	1.0%
Hospital	3	107,950,965	4.0%
Hospital	4	257,390,803	10.0%
Hospital	5	32,315,716	1.0%
Imaging Center	0	85,974,018	94.0%
Imaging Center	1	217,820	0.0%
Imaging Center	2	58	0.0%
Imaging Center	3	171	0.0%
Imaging Center	4	5,214,843	6.0%
Laboratory	0	146,369,037	90.0%
Laboratory	1	676,251	0.0%
Laboratory	2	2,007,316	1.0%
Laboratory	3	5,667,696	3.0%
Laboratory	4	876,041	1.0%
Laboratory	5	7,127,941	4.0%
Physician Group	0	3,259,917,915	78.0%
Physician Group	1	45,583,800	1.0%
Physician Group	2	210,472,862	5.0%
Physician Group	3	667,962,285	16.0%
Physician Group	4	10,528,274	0.0%
Physician Group	5	2,150,158	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_4_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	55,717	259	0.0%
Hospital	Inpatient	310	61,214	88	0.0%
Hospital	Inpatient	601	52,013	86	0.0%
Hospital	Inpatient	833	52,992	86	0.0%
Hospital	Inpatient	684	58,007	83	0.0%
Hospital	Inpatient	761	52,716	82	0.0%
Hospital	Inpatient	807	58,262	82	0.0%
Hospital	Inpatient	390	60,467	81	0.0%
Hospital	Inpatient	195	58,870	80	0.0%
Hospital	Inpatient	192	57,736	79	0.0%
Hospital	Inpatient	395	56,679	78	0.0%
Hospital	Inpatient	639	58,348	78	0.0%
Hospital	Inpatient	759	54,387	77	0.0%
Hospital	Inpatient	298	40,002	77	0.0%
Hospital	Inpatient	806	57,429	76	0.0%
Hospital	Inpatient	776	55,011	74	0.0%
Hospital	Inpatient	440	59,381	74	0.0%
Hospital	Inpatient	724	45,729	74	0.0%
Hospital	Inpatient	0640-1	54,123	74	0.0%
Hospital	Inpatient	700	55,980	74	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_4_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	72125	74,812	27,656	37.0%
Hospital	Outpatient	72131	74,746	26,980	36.0%
Hospital	Outpatient	71250	75,213	26,363	35.0%
Hospital	Outpatient	62284	54,591	26,317	48.0%
Hospital	Outpatient	72128	73,923	26,082	35.0%
Hospital	Outpatient	70486	74,942	25,824	34.0%
Hospital	Outpatient	76390	60,669	25,556	42.0%
Hospital	Outpatient	70450	75,210	25,483	34.0%
Hospital	Outpatient	31627	38,271	25,404	66.0%
Hospital	Outpatient	70480	73,698	25,139	34.0%
Hospital	Outpatient	74150	73,528	25,085	34.0%
Hospital	Outpatient	72192	73,995	25,053	34.0%
Hospital	Outpatient	73700	72,651	24,744	34.0%
Hospital	Outpatient	73200	72,269	24,666	34.0%
Hospital	Outpatient	70490	72,603	23,906	33.0%
Hospital	Outpatient	29826	38,924	23,257	60.0%
Hospital	Outpatient	71275	74,157	22,814	31.0%
Hospital	Outpatient	76942	56,815	22,636	40.0%
Hospital	Outpatient	43775	47,469	22,120	47.0%
Hospital	Outpatient	70496	74,259	21,968	30.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_4_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