v2_1_2

Generated: 2025-09-21 11:22:19

---

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): 78,942,795

Click to see SQL

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

2. State Medicare Rate Outliers

Records with Outlier Rates vs State Medicare Rates

Total Outliers: 386,388,506 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	138,475,085	7,178,566	2,766
Short Term Acute Care Hospital	Outpatient	124,466,075	13,493,775	3,252,795
ASC	Outpatient	39,250,930	10,327,570	203
Critical Access Hospital	Outpatient	33,838,331	3,436,677	2,104,505
Short Term Acute Care Hospital	Inpatient	31,028,937	146	4,850
Childrens Hospital	Outpatient	4,506,031	573,329	142,189
Critical Access Hospital	Inpatient	4,479,295	87	2,051
Rehabilitation Hospital	Outpatient	3,280,438	657,913	63,286
Imaging Center	Professional	2,888,944	297,795	0
Laboratory	Professional	2,576,787	1,246,041	0
Childrens Hospital	Inpatient	1,107,251	0	1,312
Rehabilitation Hospital	Inpatient	490,402	0	338

Click to see SQL

            SELECT
                provider_type,
                bill_type,
                COUNT(*) as count,
                COUNT(CASE WHEN canonical_rate < 0.8 * state_avg_medicare_rate THEN 1 END) as below_80pct_count,
                COUNT(CASE WHEN canonical_rate > 15 * state_avg_medicare_rate THEN 1 END) as above_15x_count
            FROM tq_dev.internal_dev_csong_cld_v2_1_2.prod_combined_all
            WHERE canonical_rate_score > 1
            GROUP BY provider_type, bill_type
            ORDER BY count DESC
            

3. Medicare Rate Outliers

Records with Outlier Rates vs Medicare Rates

Total Outliers: 386,388,506 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	138,475,085	7,178,566	2,766
Short Term Acute Care Hospital	Outpatient	124,466,075	13,651,335	3,195,588
ASC	Outpatient	39,250,930	10,186,847	61,433
Critical Access Hospital	Outpatient	33,838,331	4,710,365	573,002
Short Term Acute Care Hospital	Inpatient	31,028,937	326,631	4,160
Childrens Hospital	Outpatient	4,506,031	579,645	116,926
Critical Access Hospital	Inpatient	4,479,295	47,126	1,710
Rehabilitation Hospital	Outpatient	3,280,438	664,140	56,091
Imaging Center	Professional	2,888,944	297,795	0
Laboratory	Professional	2,576,787	1,246,041	0
Childrens Hospital	Inpatient	1,107,251	8,004	1,103
Rehabilitation Hospital	Inpatient	490,402	1,261	320

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_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	1,117,588,452	71.0%
1	78,942,795	5.0%
2	10,935,437	1.0%
3	55,310,237	4.0%
4	292,763,616	19.0%
5	27,379,216	2.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_2.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
Short Term Acute Care Hospital	Outpatient	38240	17,569	9,389	53.0%
Physician Group	Professional	93247	41,872	7,760	19.0%
Short Term Acute Care Hospital	Outpatient	88333	20,423	7,452	37.0%
Short Term Acute Care Hospital	Outpatient	86885	20,075	7,345	37.0%
Short Term Acute Care Hospital	Outpatient	86886	21,253	7,291	34.0%
Physician Group	Professional	93243	41,810	6,768	16.0%
Physician Group	Professional	64555	55,908	6,235	11.0%
Short Term Acute Care Hospital	Outpatient	86902	20,003	6,027	30.0%
Short Term Acute Care Hospital	Outpatient	85097	19,224	5,486	29.0%
Short Term Acute Care Hospital	Outpatient	A9601	9,007	5,430	60.0%
Short Term Acute Care Hospital	Outpatient	86900	21,453	5,329	25.0%
Short Term Acute Care Hospital	Outpatient	86905	19,453	5,079	26.0%
Short Term Acute Care Hospital	Outpatient	86901	21,894	4,946	23.0%
Short Term Acute Care Hospital	Outpatient	0674T	8,886	4,911	55.0%
Short Term Acute Care Hospital	Outpatient	86880	22,497	4,588	20.0%
ASC	Outpatient	20696	10,935	4,359	40.0%
Short Term Acute Care Hospital	Outpatient	54411	17,796	4,318	24.0%
Short Term Acute Care Hospital	Outpatient	86975	17,845	4,215	24.0%
ASC	Outpatient	45347	13,943	4,211	30.0%
Short Term Acute Care Hospital	Outpatient	0680T	9,202	4,037	44.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_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
Short Term Acute Care Hospital	Outpatient	J2770	14,945	14,945	100.0%
Short Term Acute Care Hospital	Outpatient	29826	17,644	13,580	77.0%
Short Term Acute Care Hospital	Outpatient	0101T	15,101	12,815	85.0%
Short Term Acute Care Hospital	Outpatient	31627	16,018	12,810	80.0%
Short Term Acute Care Hospital	Outpatient	21705	16,239	12,494	77.0%
Short Term Acute Care Hospital	Outpatient	21620	18,079	11,825	65.0%
Short Term Acute Care Hospital	Outpatient	21615	16,878	11,462	68.0%
Short Term Acute Care Hospital	Outpatient	J2724	11,753	11,126	95.0%
Short Term Acute Care Hospital	Outpatient	22850	18,063	10,978	61.0%
Short Term Acute Care Hospital	Outpatient	21825	15,115	10,811	72.0%
Short Term Acute Care Hospital	Outpatient	21627	16,215	10,685	66.0%
Short Term Acute Care Hospital	Outpatient	27886	17,922	10,612	59.0%
Short Term Acute Care Hospital	Outpatient	62284	18,255	10,483	57.0%
Short Term Acute Care Hospital	Outpatient	37182	19,368	10,426	54.0%
Short Term Acute Care Hospital	Outpatient	29999	19,909	10,366	52.0%
Short Term Acute Care Hospital	Outpatient	76390	20,246	10,331	51.0%
Short Term Acute Care Hospital	Outpatient	22852	18,115	10,161	56.0%
Short Term Acute Care Hospital	Outpatient	21750	15,493	10,016	65.0%
Short Term Acute Care Hospital	Outpatient	28800	16,902	9,841	58.0%
Short Term Acute Care Hospital	Outpatient	35800	17,186	9,832	57.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_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