Intermediate
Case 34 of 50
Case 034: At-Risk Stock
productsinventoryorder_items
Operations is worried about running out of popular items, and the manager passes the concern to Alex.
"Could you show me everything under 20 units in stock, and how much of it has actually been selling?"
The data Alex is looking at
products
| id | name | category | price |
|---|---|---|---|
| 1 | Keyboard | Accessories | 2500 |
| 2 | Mouse | Accessories | 1200 |
| 3 | Headphones | Audio | 3500 |
| 4 | Monitor | Displays | 12000 |
| 5 | Laptop | Computers | 60000 |
| … 5 more rows | |||
inventory
| product_id | stock |
|---|---|
| 1 | 40 |
| 2 | 25 |
| 3 | 60 |
| 4 | 15 |
| 5 | 20 |
| … 5 more rows | |
order_items
| order_id | product_id | quantity |
|---|---|---|
| 101 | 1 | 2 |
| 101 | 2 | 1 |
| 102 | 3 | 1 |
| 103 | 2 | 3 |
| 104 | 5 | 1 |
| … 29 more rows | ||
TASK
Help Alex answer the manager:
“For every product with fewer than 20 units in stock, show its stock level and how many units have sold so far, most in-demand first.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
SELECT
products.name,
inventory.stock,
COALESCE(SUM(order_items.quantity), 0) AS units_sold
FROM products
JOIN inventory ON inventory.product_id = products.id
LEFT JOIN order_items ON order_items.product_id = products.id
WHERE inventory.stock < 20
GROUP BY products.name, inventory.stock
ORDER BY units_sold DESC;
What that query returns
Case Debrief
LEFT JOIN + COALESCE
- LEFT JOIN + COALESCE is the standard combination for "keep everything, even with no activity, and show 0 instead of NULL" business reports.