Zomato: Blinkit Store Items Below Their Reorder Level
A Blinkit dark store reorders an item when its stock falls below the item's reorder level. The store manager wants the morning reorder list.
- Difficulty: Easy
- Topics: Joins, Conditional Logic
- Dialect: MySQL
- Problem: #1029
Problem statement
A Blinkit dark store reorders an item when its stock falls below the item's reorder level. The store manager wants the morning reorder list.
For every active product (is_active = 1) whose units on hand are below its reorder_level, return product_name, on_hand and shortfall (reorder_level - on_hand). A product with no row in StoreStock has 0 units on hand. Order by shortfall descending, then product_name ascending.
A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.
Tables
Table: Product
| Column | Type |
|---|---|
| product_id | int |
| product_name | varchar |
| reorder_level | int |
| is_active | bool |
Primary key: product_id.
The store's catalogue; reorder_level is always at least 1. Product names are unique.
Table: StoreStock
| Column | Type |
|---|---|
| product_id | int |
| on_hand | int |
Primary key: product_id.
Units on the shelves at opening. A product that was never stocked has no row.
Examples
Example 1
Product
| product_id | product_name | reorder_level | is_active |
|---|---|---|---|
| 1 | Toned Milk | 40 | 1 |
| 2 | Brown Bread | 30 | 1 |
| 3 | Farm Eggs | 20 | 1 |
| 4 | Bananas | 25 | 1 |
| 5 | Instant Noodles | 10 | 1 |
| 6 | Paneer Block | 12 | 0 |
| 7 | Cola Bottle | 24 | 1 |
StoreStock
| product_id | on_hand |
|---|---|
| 1 | 25 |
| 2 | 30 |
| 3 | 5 |
| 4 | 60 |
| 7 | 50 |
Output
| product_name | on_hand | shortfall |
|---|---|---|
| Farm Eggs | 5 | 15 |
| Toned Milk | 25 | 15 |
| Instant Noodles | 0 | 10 |
How to solve Zomato: Blinkit Store Items Below Their Reorder Level
Products that were never stocked are the ones most in need of a reorder, yet they have no StoreStock row, so an inner join would silently drop them. A LEFT JOIN from Product keeps them with s.on_hand NULL, and COALESCE(s.on_hand, 0) turns that NULL into the 0 the statement asks for.
The filter then keeps active products whose (defaulted) stock is strictly below the reorder level. Writing the test on s.on_hand directly would be wrong: NULL < 40 is NULL, not true, and the never-stocked items would vanish again. The shortfall is the level minus the defaulted stock, and the order is by shortfall with the name as the tie-breaker.
The alternative fetches each product's stock with a scalar subquery wrapped in IFNULL, then filters in an outer query. Both make one pass over the catalogue with a key lookup per product.
Reference solution (MySQL)
SELECT p.product_name,
COALESCE(s.on_hand, 0) AS on_hand,
p.reorder_level - COALESCE(s.on_hand, 0) AS shortfall
FROM Product p
LEFT JOIN StoreStock s ON s.product_id = p.product_id
WHERE p.is_active = 1
AND COALESCE(s.on_hand, 0) < p.reorder_level
ORDER BY shortfall DESC, p.product_nameAnother way
SELECT product_name, on_hand, reorder_level - on_hand AS shortfall FROM (
SELECT p.product_name, p.reorder_level, p.is_active,
IFNULL((SELECT s.on_hand FROM StoreStock s WHERE s.product_id = p.product_id), 0) AS on_hand
FROM Product p
) t
WHERE is_active = 1 AND on_hand < reorder_level
ORDER BY shortfall DESC, product_name ASC← Zomato: Sold-Out Movie Shows on District · Zomato: Hyperpure Invoices Paid Late or Still Overdue →