Multi-column filter + JOIN + ORDER BY — the shape of a typical enterprise-tier worklist
The CRO wants a worklist for this week's enterprise deal review. Give me every opportunity where `amount > 50000`, joined to the account, ordered by amount descending. Return `opportunity_id`, `company_name`, `stage`, and `amount`. Include every stage (open and closed) — she wants to see everything in that size band.
| 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 |
|---|---|---|
| account_id | INT | PK |
| company_name | TEXT | |
| industry | TEXT | |
| employee_band | TEXT | |
| country | TEXT | |
| created_date | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
INNER JOIN `revops_opportunities` to `revops_accounts` on `account_id = account_id`. Every opportunity has an account, so INNER vs LEFT doesn't matter for the row count.
`WHERE amount > 50000` — strictly greater, not >=. $50K is the trigger, $50,001 is the first row that matches.
`ORDER BY amount DESC`. You should see 30 rows spanning open pipeline, Closed-Won (including expansions), and Closed-Lost.