JOIN + WHERE + GROUP BY to aggregate the pipeline across two tables
Monday morning question from the CRO: how many open deals does each rep own right now? Join `revops_opportunities` to `revops_users` on `owner_user_id`, keep only OPEN stages (stage NOT IN ('Closed-Won', 'Closed-Lost')), count the opportunities per owner, and order highest to lowest. Return `full_name` and the count. Exclude reps with zero open deals — they won't appear in the JOIN result naturally.
| 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.
Start with an INNER JOIN on `owner_user_id = user_id`. Open-deal reps appear because they have at least one matching row.
Filter `stage NOT IN ('Closed-Won', 'Closed-Lost')` in WHERE. Group by `full_name`. COUNT(*) works fine — each opportunity is one row, no fan-out to worry about here.
Order by the count DESC. You should see 7 owners.