GROUP BY + ORDER BY + LIMIT
AP renewal season. I need the top 10 vendors by 2025 YTD spend — show `vendor_name` and `total_spend`, sorted highest to lowest, limit 10. Only posted entries. Include all account types.
| Column | Type | Key |
|---|---|---|
| txn_id | INT | PK |
| txn_date | TEXT | |
| account_id | INT | FK → dim_accounts |
| cost_center_id | INT | FK → dim_cost_centers |
| vendor_id | INT | FK → dim_vendors |
| amount | REAL | |
| currency | TEXT | |
| description | TEXT | |
| posted | INT | |
| posted_date | TEXT |
| Column | Type | Key |
|---|---|---|
| vendor_id | INT | PK |
| vendor_name | TEXT | |
| category | TEXT | |
| country | TEXT | |
| onboarded_date | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Vendor names and transaction amounts are in separate tables. Before you rank, think about which transactions should be included — not all entries in the ledger are finalized.
GROUP BY vendor_name (or vendor_id), SUM(amount) AS total_spend, ORDER BY total_spend DESC, LIMIT 10.
Remember the trailing-space Stripe — if your LOWER(TRIM(...)) collapsed them, that's fine; if not, you'll see both. Either output is acceptable.