Version: Canary - 2.3 🚧

v2_3_0

Generated: 2025-11-27 14:47:07

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): 92,313,707

Click to see SQL

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

2. cbsa Medicare Rate Outliers

Rates vs cbsa Medicare Rates

Total Rates: 549,084,631 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Hospital	Outpatient	238,830,244	20,783,020	3,745,462
Physician Group	Professional	217,223,895	38,631,073	0
Hospital	Inpatient	62,894,023	0	0
ASC	Outpatient	22,326,018	3,281,377	403,895
Imaging Center	Professional	3,912,379	379,322	2,105
Laboratory	Professional	3,898,072	1,935,799	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_3_0.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: 549,084,631 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Hospital	Outpatient	238,830,244	20,620,786	3,772,387
Physician Group	Professional	217,223,895	38,631,073	0
Hospital	Inpatient	62,894,023	422,000	126
ASC	Outpatient	22,326,018	3,134,372	478,734
Imaging Center	Professional	3,912,379	379,322	2,105
Laboratory	Professional	3,898,072	1,935,799	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_3_0.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,438,717,994	79.0%
1	92,313,707	3.0%
2	60,378,257	2.0%
3	262,398,121	9.0%
4	198,061,856	6.0%
5	28,246,397	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_0.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	166,482,581	87.0%
ASC	1	3,253,090	2.0%
ASC	2	177,112	0.0%
ASC	3	244,486	0.0%
ASC	4	21,904,420	11.0%
Hospital	0	727,222,801	67.0%
Hospital	1	52,616,815	5.0%
Hospital	2	20,880,384	2.0%
Hospital	3	85,457,126	8.0%
Hospital	4	170,260,617	16.0%
Hospital	5	25,126,140	2.0%
Imaging Center	0	43,751,450	92.0%
Imaging Center	1	147,942	0.0%
Imaging Center	2	99	0.0%
Imaging Center	3	360	0.0%
Imaging Center	4	3,911,920	8.0%
Laboratory	0	17,504,535	81.0%
Laboratory	1	128,214	1.0%
Laboratory	2	527,194	2.0%
Laboratory	3	1,188,605	6.0%
Laboratory	4	261,070	1.0%
Laboratory	5	1,921,203	9.0%
Physician Group	0	1,483,756,627	85.0%
Physician Group	1	36,167,646	2.0%
Physician Group	2	38,793,468	2.0%
Physician Group	3	175,507,544	10.0%
Physician Group	4	1,723,829	0.0%
Physician Group	5	1,199,054	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_0.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	37,746	84	0.0%
Hospital	Inpatient	0640-1	34,704	51	0.0%
Hospital	Inpatient	309	43,960	43	0.0%
Hospital	Inpatient	298	28,330	43	0.0%
Hospital	Inpatient	0640-2	34,762	43	0.0%
Hospital	Inpatient	310	43,202	41	0.0%
Hospital	Inpatient	311	39,553	40	0.0%
Hospital	Inpatient	730	33,174	40	0.0%
Hospital	Inpatient	950	41,181	40	0.0%
Hospital	Inpatient	807	41,335	39	0.0%
Hospital	Inpatient	916	38,824	38	0.0%
Hospital	Inpatient	833	37,359	37	0.0%
Hospital	Inpatient	122	34,411	37	0.0%
Hospital	Inpatient	724	30,284	37	0.0%
Hospital	Inpatient	305	42,286	37	0.0%
Hospital	Inpatient	882	41,009	36	0.0%
Hospital	Inpatient	881	41,970	35	0.0%
Hospital	Inpatient	285	31,349	35	0.0%
Hospital	Inpatient	284	35,671	34	0.0%
Hospital	Inpatient	195	41,655	34	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_0.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	52,885	22,160	42.0%
Hospital	Outpatient	72131	52,876	21,735	41.0%
Hospital	Outpatient	72128	52,504	21,410	41.0%
Hospital	Outpatient	71250	53,148	21,355	40.0%
Hospital	Outpatient	74150	52,305	20,802	40.0%
Hospital	Outpatient	70486	53,051	20,792	39.0%
Hospital	Outpatient	70480	52,468	20,677	39.0%
Hospital	Outpatient	70450	53,399	20,586	39.0%
Hospital	Outpatient	72192	52,722	20,523	39.0%
Hospital	Outpatient	76942	44,722	20,500	46.0%
Hospital	Outpatient	73700	52,219	20,358	39.0%
Hospital	Outpatient	73200	52,017	20,357	39.0%
Hospital	Outpatient	70490	52,428	20,093	38.0%
Hospital	Outpatient	31627	28,484	19,512	69.0%
Hospital	Outpatient	37182	40,496	19,356	48.0%
Hospital	Outpatient	76390	41,838	19,299	46.0%
Hospital	Outpatient	62284	41,303	19,078	46.0%
Hospital	Outpatient	71275	53,102	18,541	35.0%
Hospital	Outpatient	74170	52,413	18,049	34.0%
Hospital	Outpatient	75635	51,436	17,696	34.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_0.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