Aggregation with SUM, JOIN to dim for human-readable column. Practice grouping by two columns simultaneously (listing_id + product_title) which is required when you want both id and label in the output without having a non-aggregated column issue.
Quarterly product review prep. Give me the top 10 listings by units_sold (SUM of fact_orders.units, all-time). Three columns: listing_id, product_title, units_sold. Sort units_sold descending, listing_id ascending as tiebreaker. The top 10 should look like our hero SKUs — anything in the top 10 you don't recognize is interesting.
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| listing_id | INT | FK → dim_listings |
| marketplace_id | INT | FK → dim_marketplaces |
| order_ts | TEXT | |
| units | INT | |
| gross_amount | REAL | |
| refunded | INT |
| Column | Type | Key |
|---|---|---|
| listing_id | INT | PK |
| marketplace_id | INT | FK → dim_marketplaces |
| asin_or_listing_id | TEXT | |
| product_title | TEXT | |
| category | TEXT | |
| list_price | REAL | |
| current_status | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
SUM(o.units) AS units_sold from fact_orders.
JOIN dim_listings on listing_id to pull product_title.
GROUP BY listing_id, product_title — both are needed to keep both in the output.
ORDER BY units_sold DESC, listing_id ASC and LIMIT 10.