AVG + JOIN + GROUP BY — the per-segment average every regional leader asks for
Territory planning. We're sizing FY26 quotas region-by-region and I need a baseline read on average opportunity size per region — the input that anchors capacity modeling. Join opportunities to users, group by `region`, compute average deal size across ALL opportunities (open + closed, every stage — we want the full book, not just historical wins, so newer territories with thin won-history aren't penalized). 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 | |
| forecast_category | TEXT |
| 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)::numeric, 0)` — Postgres requires the `::numeric` cast on a double-precision input before `ROUND(x, n)` will accept the precision argument.
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.