Zomato: Blinkit Stock-Outs Lasting a Day or More

Medium
1035.

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. 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

ColumnTypeValues
event_idint
store_idint
product_namevarchar
event_timedatetime
event_typeenumout_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.

Example

The rows must come back in the order shown.

StockEvent

event_idstore_idproduct_nameevent_timeevent_type
17Bananas2024-08-20 09:00:00out_of_stock
27Bananas2024-08-21 09:00:00restocked
37Bananas2024-08-25 10:00:00out_of_stock
47Bananas2024-08-25 18:30:00restocked
57Brown Bread2024-08-22 08:15:00out_of_stock
67Brown Bread2024-08-23 08:14:00restocked
79Brown Bread2024-08-30 20:00:00out_of_stock
89Paneer Block2024-08-31 12:00:00out_of_stock
99Paneer Block2024-08-10 06:00:00out_of_stock
109Paneer Block2024-08-13 07:00:00restocked

Output

store_idproduct_nameout_atback_athours_out
7Bananas2024-08-20 09:00:002024-08-21 09:00:0024
9Brown Bread2024-08-30 20:00:00NULL28
9Paneer Block2024-08-10 06:00:002024-08-13 07:00:0073
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
event_idint
store_idint
product_namevarchar
event_timedatetime
event_typeenum(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_idstore_idproduct_nameevent_timeevent_type
17Bananas2024-08-20 09:00:00out_of_stock
27Bananas2024-08-21 09:00:00restocked
37Bananas2024-08-25 10:00:00out_of_stock
47Bananas2024-08-25 18:30:00restocked
57Brown Bread2024-08-22 08:15:00out_of_stock
67Brown Bread2024-08-23 08:14:00restocked
79Brown Bread2024-08-30 20:00:00out_of_stock
89Paneer Block2024-08-31 12:00:00out_of_stock
99Paneer Block2024-08-10 06:00:00out_of_stock
109Paneer Block2024-08-13 07:00:00restocked

Output

store_idproduct_nameout_atback_athours_out
7Bananas2024-08-20 09:00:002024-08-21 09:00:0024
9Brown Bread2024-08-30 20:00:00NULL28
9Paneer Block2024-08-10 06:00:002024-08-13 07:00:0073

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_at

Another 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 →