AVG + JOIN + GROUP BY — the per-segment average every regional leader asks for
CRO wants to know where our big deals are landing geographically. Join opportunities to users, group by `region`, compute average deal size across ALL opportunities (open + closed, every stage — she wants the full book, not just historical wins). Return `region` and the rounded `avg_amount`. Order by avg_amount desc.
| 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.
INNER JOIN on `owner_user_id = user_id` — same as mission 3. Drop no rows; keep all stages.
Group by `region`. `AVG(amount)`. Round to the nearest dollar with `ROUND(AVG(amount), 0)` or `ROUND(AVG(amount))`.
You should see 5 rows — one per active region that has at least one opportunity. Regions with no opps (e.g. the Director's 'Global' region, since the Director owns zero deals directly) don't appear.