First LEFT JOIN. Practice preserving listings with zero orders via LEFT JOIN, computing SUM with COALESCE-ish defaults (the LEFT JOIN naturally produces NULL → 0 via SUM), and producing the top-N listings the merchandising team uses for promo planning.
Promo planning Friday. Pull the top 10 active listings by gross revenue (sum of fact_orders.gross_amount). Restrict to current_status = 'active'. Four columns: listing_id, product_title, orders (count of order rows), gross_revenue (rounded to 2 decimals). Sort gross_revenue descending, listing_id ascending. The merchandising team uses this to pick promo candidates — top revenue listings get the home-page placement.
| 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 |
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
LEFT JOIN fact_orders on listing_id — preserves listings with zero orders (they get gross_revenue = 0 via SUM(NULL) in many SQL dialects, or 0 explicit).
Filter dim_listings WHERE current_status = 'active' in the WHERE.
ROUND(SUM(o.gross_amount), 2) for clean output.
ORDER BY gross_revenue DESC, listing_id ASC, LIMIT 10.