Find All Products That Are Out of Stock

SQL coding challenge · Difficulty: easy · +50 XP

Problem

The inventory manager needs a list of all products that are completely sold out (stock = 0) so they can trigger restock purchase orders immediately.

Tables

Table: products

| product_id | product_name | category_id | stock_quantity |
| --- | --- | --- | --- |
| 1 | T-Shirt | 1 | 50 |
| 2 | Jeans | 1 | 0 |
| 3 | Sneakers | 2 | 30 |
| 4 | Backpack | 2 | 0 |
| 5 | Watch | 3 | 15 |
| 6 | Sunglasses | 3 | 10 |

Table: categories

| category_id | category_name |
| --- | --- |
| 1 | Apparel |
| 2 | Footwear |
| 3 | Accessories |

Table: order_items

| item_id | product_id | quantity | unit_price | order_date |
| --- | --- | --- | --- | --- |
| 1 | 1 | 5 | 599 | 2024-05-01 |
| 2 | 3 | 2 | 2499 | 2024-05-01 |
| 3 | 2 | 3 | 1299 | 2024-05-02 |
| 4 | 5 | 1 | 1499 | 2024-05-02 |
| 5 | 3 | 4 | 2499 | 2024-05-03 |
| 6 | 1 | 2 | 599 | 2024-05-03 |
| 7 | 2 | 1 | 1299 | 2024-06-01 |
| 8 | 4 | 2 | 899 | 2024-06-01 |
| 9 | 5 | 4 | 1499 | 2024-06-02 |

Table: product_reviews

| review_id | product_id | rating |
| --- | --- | --- |
| 1 | 1 | 5 |
| 2 | 1 | 4 |
| 3 | 1 | 3 |
| 4 | 2 | 4 |
| 5 | 2 | 3 |
| 6 | 3 | 5 |
| 7 | 3 | 5 |
| 8 | 4 | 2 |
| 9 | 5 | 4 |
| 10 | 5 | 5 |

Expected Output

| product_id | product_name |
| --- | --- |
| 2 | Jeans |
| 4 | Backpack |

• Return: product_id, product_name
• Only products where stock_quantity = 0
• Sort by product_id ascending