v2_1_3

Generated: 2025-09-28 21:00: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): 104,686,670

Click to see SQL

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

2. cbsa Medicare Rate Outliers

Rates vs cbsa Medicare Rates

Total Rates: 520,113,647 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	206,135,798	10,117,842	2,485,324
Short Term Acute Care Hospital	Outpatient	149,730,659	18,422,065	2,829,780
ASC	Outpatient	59,965,503	14,685,261	1,070,169
Short Term Acute Care Hospital	Inpatient	40,308,493	351,305	32,920
Critical Access Hospital	Outpatient	38,562,594	5,648,366	1,192,619
Critical Access Hospital	Inpatient	6,046,296	715,505	8,028
Childrens Hospital	Outpatient	5,379,892	813,774	120,763
Rehabilitation Hospital	Outpatient	4,345,417	1,207,793	51,125
Imaging Center	Professional	4,238,362	434,425	2,506
Laboratory	Professional	3,349,030	1,768,629	4,355
Childrens Hospital	Inpatient	1,399,175	0	3,805
Rehabilitation Hospital	Inpatient	652,428	14,986	1,705

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_1_3.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: 520,113,647 records

provider_type	bill_type	count	below_80pct_count	above_15x_count
Physician Group	Professional	206,135,798	10,117,842	2,485,324
Short Term Acute Care Hospital	Outpatient	149,730,659	18,446,918	2,813,853
ASC	Outpatient	59,965,503	14,527,534	1,126,719
Short Term Acute Care Hospital	Inpatient	40,308,493	699,214	31,997
Critical Access Hospital	Outpatient	38,562,594	7,229,200	339,139
Critical Access Hospital	Inpatient	6,046,296	725,479	8,309
Childrens Hospital	Outpatient	5,379,892	815,307	105,522
Rehabilitation Hospital	Outpatient	4,345,417	1,210,924	45,962
Imaging Center	Professional	4,238,362	434,425	2,506
Laboratory	Professional	3,349,030	1,768,669	4,355
Childrens Hospital	Inpatient	1,399,175	12,737	2,781
Rehabilitation Hospital	Inpatient	652,428	16,926	1,636

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_3.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,451,390,742	70.0%
1	104,686,670	5.0%
2	15,504,132	1.0%
3	56,498,322	3.0%
4	422,429,971	20.0%
5	25,681,222	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_1_3.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	21,457	13,283	62.0%
Physician Group	Professional	93247	61,804	12,580	20.0%
Physician Group	Professional	93243	61,854	10,979	18.0%
Physician Group	Professional	64555	87,779	9,763	11.0%
Short Term Acute Care Hospital	Outpatient	86885	23,525	8,578	37.0%
Short Term Acute Care Hospital	Outpatient	88333	23,909	8,503	36.0%
Short Term Acute Care Hospital	Outpatient	86886	24,738	8,299	34.0%
Short Term Acute Care Hospital	Outpatient	54411	18,309	7,490	41.0%
Short Term Acute Care Hospital	Outpatient	0674T	13,484	7,332	54.0%
Short Term Acute Care Hospital	Outpatient	86902	23,433	7,089	30.0%
ASC	Outpatient	20696	16,496	6,947	42.0%
Short Term Acute Care Hospital	Outpatient	86900	25,212	6,910	27.0%
Short Term Acute Care Hospital	Outpatient	85097	22,422	6,679	30.0%
ASC	Outpatient	45347	20,739	6,595	32.0%
ASC	Outpatient	45389	21,770	6,260	29.0%
Short Term Acute Care Hospital	Outpatient	0680T	13,695	6,184	45.0%
ASC	Outpatient	21243	18,599	5,801	31.0%
Short Term Acute Care Hospital	Outpatient	86905	22,732	5,730	25.0%
Short Term Acute Care Hospital	Outpatient	86901	25,427	5,701	22.0%
Physician Group	Professional	43249	150,627	5,348	4.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_3.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	31295	123,286	28,379	23.0%
Physician Group	Professional	31627	123,338	27,160	22.0%
Physician Group	Professional	01996	69,403	26,960	39.0%
Physician Group	Professional	31298	119,415	26,560	22.0%
Physician Group	Professional	31296	120,450	25,628	21.0%
Physician Group	Professional	45388	144,877	23,663	16.0%
Physician Group	Professional	36482	95,817	22,243	23.0%
Physician Group	Professional	37238	95,316	21,397	22.0%
Physician Group	Professional	37244	90,251	20,840	23.0%
Physician Group	Professional	37228	97,616	20,754	21.0%
Physician Group	Professional	37241	89,317	20,574	23.0%
Physician Group	Professional	37223	95,894	20,541	21.0%
Physician Group	Professional	37230	92,083	20,022	22.0%
Physician Group	Professional	36466	80,231	18,197	23.0%
Physician Group	Professional	37221	98,006	18,195	19.0%
Physician Group	Professional	37229	90,536	17,703	20.0%
Physician Group	Professional	37191	80,249	17,677	22.0%
Physician Group	Professional	00812	30,411	17,475	58.0%
Physician Group	Professional	36465	79,579	17,434	22.0%
Physician Group	Professional	00811	30,271	17,145	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_3.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