Conditional aggregation (SUM CASE WHEN) + ratio — the shape of every rate / ratio report in analytics
Per-rep win rate for the quarterly ops review. Filter to closed deals only (`stage IN ('Closed-Won', 'Closed-Lost')`), join to `revops_users`, and for each rep compute: total closed, wins, win_rate_pct (100 * wins / total_closed, rounded to 1 decimal). Return `full_name`, `total_closed`, `win_rate_pct`. Order by win_rate_pct desc, then total_closed desc. Include only reps with at least one closed deal — we don't need a zero row for reps who are all-open.
| Column | Type | Key |
|---|---|---|
| opportunity_id | INT | PK |
| account_id | INT | FK → revops_accounts |
| owner_user_id | INT | FK → revops_users |
| name | TEXT | |
| stage | TEXT | |
| amount | REAL | |
| close_date | TEXT | |
| created_date | TEXT | |
| is_closed | INT | |
| is_won | INT |
| Column | Type | Key |
|---|---|---|
| user_id | INT | PK |
| full_name | TEXT | |
| role_title | TEXT | |
| region | TEXT | |
| hire_date | TEXT | |
| status | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Filter in WHERE: `stage IN ('Closed-Won', 'Closed-Lost')`. Then join users. Group by rep.
Wins: `SUM(CASE WHEN stage = 'Closed-Won' THEN 1 ELSE 0 END)`. Total: `COUNT(*)`. Win rate: `ROUND(100.0 * wins / total_closed, 1)`. The `100.0 *` forces float math — without it you'll get 0 on integer division.
You should see 6 rows — every rep with closed deals. One rep has a perfect 100% win rate across their closed deals (albeit small sample).