Zomato: Blinkit Stock-Outs Lasting a Day or More
Every time a Blinkit dark store runs out of an item, and every time the item is back on the shelf, an event is logged.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #1035
Problem statement
Every time a Blinkit dark store runs out of an item, and every time the item is back on the shelf, an event is logged. The category team chases stock-outs that lasted a day or longer.
For each out_of_stock event, the stock-out ends at the next event of the same store and product (always a restocked event — the events of one store and product alternate, starting with out_of_stock). If there is no next event the item is still out, and the stock-out is measured up to 2024-09-01 00:00:00. Return the stock-outs of 24 hours or more — hours_out is TIMESTAMPDIFF(HOUR, …) from the out-of-stock time to the end — with store_id, product_name, out_at, back_at (NULL while still out) and hours_out. Order by store_id, product_name, out_at.
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: StockEvent
| Column | Type |
|---|---|
| event_id | int |
| store_id | int |
| product_name | varchar |
| event_time | datetime |
| event_type | enum(out_of_stock, restocked) |
Primary key: event_id.
One row per stock event, all before 2024-09-01. Two events of one store and product never share a time.
Examples
Example 1
StockEvent
| event_id | store_id | product_name | event_time | event_type |
|---|---|---|---|---|
| 1 | 7 | Bananas | 2024-08-20 09:00:00 | out_of_stock |
| 2 | 7 | Bananas | 2024-08-21 09:00:00 | restocked |
| 3 | 7 | Bananas | 2024-08-25 10:00:00 | out_of_stock |
| 4 | 7 | Bananas | 2024-08-25 18:30:00 | restocked |
| 5 | 7 | Brown Bread | 2024-08-22 08:15:00 | out_of_stock |
| 6 | 7 | Brown Bread | 2024-08-23 08:14:00 | restocked |
| 7 | 9 | Brown Bread | 2024-08-30 20:00:00 | out_of_stock |
| 8 | 9 | Paneer Block | 2024-08-31 12:00:00 | out_of_stock |
| 9 | 9 | Paneer Block | 2024-08-10 06:00:00 | out_of_stock |
| 10 | 9 | Paneer Block | 2024-08-13 07:00:00 | restocked |
Output
| store_id | product_name | out_at | back_at | hours_out |
|---|---|---|---|---|
| 7 | Bananas | 2024-08-20 09:00:00 | 2024-08-21 09:00:00 | 24 |
| 9 | Brown Bread | 2024-08-30 20:00:00 | NULL | 28 |
| 9 | Paneer Block | 2024-08-10 06:00:00 | 2024-08-13 07:00:00 | 73 |
How to solve Zomato: Blinkit Stock-Outs Lasting a Day or More
Each stock-out spans two rows — the out_of_stock event and the restocked event after it — so the task is to bring the next row's time alongside the current one. LEAD(event_time) does exactly that within a window partitioned by store and product and ordered by time. The partition matters: without it, the "next" event could belong to another product.
Only the out-of-stock rows are reported, so the filter keeps event_type = 'out_of_stock' — after LEAD has been computed over all events, which is why it sits in the outer query. The last stock-out of a series may have no next event; COALESCE(next_time, '2024-09-01 00:00:00') measures it to the cut-off while back_at itself stays NULL, telling the team it is still out.
TIMESTAMPDIFF(HOUR, …) truncates to whole hours, so a stock-out of exactly 24 hours is kept and one of 23 h 59 min is not. The rows arrive in any order in the table, but the window orders them itself.
The alternative finds the end with a correlated MIN(event_time) of the restocked events after each stock-out — correct here because the events alternate. LEAD reads the events once in sorted order; the subquery probes per stock-out and wants an index on (store_id, product_name, event_time).
Reference solution (MySQL)
WITH seqd AS (
SELECT store_id, product_name, event_time, event_type,
LEAD(event_time) OVER (PARTITION BY store_id, product_name ORDER BY event_time) AS next_time
FROM StockEvent
)
SELECT store_id, product_name, event_time AS out_at, next_time AS back_at,
TIMESTAMPDIFF(HOUR, event_time, COALESCE(next_time, '2024-09-01 00:00:00')) AS hours_out
FROM seqd
WHERE event_type = 'out_of_stock'
AND TIMESTAMPDIFF(HOUR, event_time, COALESCE(next_time, '2024-09-01 00:00:00')) >= 24
ORDER BY store_id, product_name, out_atAnother way
SELECT store_id, product_name, out_at, back_at,
TIMESTAMPDIFF(HOUR, out_at, IFNULL(back_at, '2024-09-01 00:00:00')) AS hours_out
FROM (
SELECT e.store_id, e.product_name, e.event_time AS out_at,
(SELECT MIN(r.event_time) FROM StockEvent r
WHERE r.store_id = e.store_id AND r.product_name = e.product_name
AND r.event_type = 'restocked' AND r.event_time > e.event_time) AS back_at
FROM StockEvent e
WHERE e.event_type = 'out_of_stock'
) s
WHERE TIMESTAMPDIFF(HOUR, out_at, IFNULL(back_at, '2024-09-01 00:00:00')) >= 24
ORDER BY store_id, product_name, out_at← Zomato: Reviews Far From the Restaurant's Consensus · Zomato: District Event Sell-Through by Category →