Conditional aggregation via SUM(CASE WHEN ...) — splitting one row source into multiple metrics in a single SELECT. Clean Claim Rate (also called First-Pass Yield) is the canonical RCM diagnostic: percentage of submitted claims that paid on the first submission with no denial, no rework, no appeal. Industry benchmark is 90-95%; below 80% signals systemic edit-quality problems.
Sarah wants the CCR-by-payer view for the Wednesday denials prevention review. From fact_claims joined to dim_payer, give me four metrics per payer: total_claims (all submissions), paid_first_pass (count where claim_status='paid'), and ccr_pct (the first-pass yield, rounded to 1 decimal). Use SUM(CASE WHEN ...) for the conditional count — don't write two queries and join them. Four output columns: payer_name, total_claims, paid_first_pass, ccr_pct. Sort by ccr_pct ascending so the worst-performing payers lead — those are the ones we need to dig into. Float-promote: 100.0 (with the decimal), not 100, otherwise integer division silently rounds the percentage.
| Column | Type | Key |
|---|---|---|
| claim_id | INT | PK |
| charge_id | INT | FK → fact_charges |
| payer_id | INT | FK → dim_payer |
| submission_date | TEXT | |
| claim_status | TEXT | |
| submitted_amount | REAL |
| Column | Type | Key |
|---|---|---|
| payer_id | INT | PK |
| payer_name | TEXT | |
| plan_type | TEXT | |
| expected_pct_of_billed | REAL | |
| is_in_network | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Single GROUP BY on payer with COUNT(*) for total claims and a sibling SUM(CASE WHEN ...) for the paid-first-pass count. The CASE expression returns 1 when claim_status='paid' and 0 otherwise; SUM totals the 1s.
Float-promote the ratio — multiply by 100.0 (the decimal!) BEFORE dividing, otherwise integer division silently rounds the percentage to whole-number multiples of (100 / total_claims).
Sort ascending on ccr_pct so the lowest performers lead — that's the meeting agenda. Without ascending sort, the highest-CCR payers land at the top and the audience has to scroll past them to find the problems.