Swiggy: Instamart SKUs Out of Stock Across a Whole City
An item missing from one dark store is a local problem; an item missing from every dark store in a city means nobody there can order it, and the category…
- Difficulty: Medium
- Topics: Aggregation, Subqueries, Joins
- Dialect: MySQL
- Problem: #957
Problem statement
An item missing from one dark store is a local problem; an item missing from every dark store in a city means nobody there can order it, and the category team escalates it to the supplier.
From the latest stock snapshot, return city and sku_name for every SKU that is listed at every dark store of the city (each store has a stock row for it) and has qty_on_hand = 0 at all of them. A SKU some store of the city does not list at all is not a city-wide stock-out. Order the rows by city, then sku_name.
A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.
Tables
Table: DarkStore
| Column | Type |
|---|---|
| store_id | int |
| store_name | varchar |
| city | varchar |
Primary key: store_id.
One row per Instamart dark store.
Table: Sku
| Column | Type |
|---|---|
| sku_id | int |
| sku_name | varchar |
Primary key: sku_id.
One row per catalogue item. Names are unique.
Table: StoreStock
| Column | Type |
|---|---|
| store_id | int |
| sku_id | int |
| qty_on_hand | int |
Primary key: store_id, sku_id.
One row per SKU a store lists, with the units on its shelves at snapshot time.
Examples
Example 1
DarkStore
| store_id | store_name | city |
|---|---|---|
| 1 | HSR Layout | Bengaluru |
| 2 | Koramangala | Bengaluru |
| 3 | Powai | Mumbai |
Sku
| sku_id | sku_name |
|---|---|
| 1 | Amul Butter |
| 2 | Toned Milk |
| 3 | Fresh Paneer |
| 4 | Eggs Tray |
StoreStock
| store_id | sku_id | qty_on_hand |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 1 | 0 |
| 1 | 2 | 0 |
| 2 | 2 | 5 |
| 1 | 3 | 0 |
| 3 | 1 | 0 |
| 3 | 2 | 4 |
| 1 | 4 | 0 |
| 2 | 4 | 0 |
| 3 | 4 | 0 |
Output
| city | sku_name |
|---|---|
| Bengaluru | Amul Butter |
| Bengaluru | Eggs Tray |
| Mumbai | Amul Butter |
| Mumbai | Eggs Tray |
How to solve Swiggy: Instamart SKUs Out of Stock Across a Whole City
This is relational division: a (city, SKU) pair qualifies only if it holds for every store of the city. Joining the stock rows to their stores and grouping by city and SKU gives one group per pair, with one row per store that lists the SKU (the primary key guarantees no duplicates). Two conditions on the group finish the job: MAX(qty_on_hand) = 0 says no store has a single unit, and COUNT(*) equal to the number of stores in the city says no store is missing from the group. A SKU listed at only some stores fails the count, even if it is zero wherever it appears.
The alternative is the textbook double negation: keep a (city, SKU) pair when there is no store in the city for which there is no zero-quantity row of that SKU. It starts from every city crossed with every SKU, so it does not even need the SKU to appear in the stock table — the inner NOT EXISTS fails for any store without the row.
The grouping version scans the stock once and runs the store count per group; the double negation does nested lookups and benefits from the (store_id, sku_id) key. Both return sorted by city, then name.
Reference solution (MySQL)
SELECT d.city, k.sku_name
FROM StoreStock s
JOIN DarkStore d ON d.store_id = s.store_id
JOIN Sku k ON k.sku_id = s.sku_id
GROUP BY d.city, k.sku_id, k.sku_name
HAVING MAX(s.qty_on_hand) = 0
AND COUNT(*) = (SELECT COUNT(*) FROM DarkStore d2 WHERE d2.city = d.city)
ORDER BY d.city, k.sku_nameAnother way
SELECT DISTINCT d.city, k.sku_name
FROM DarkStore d CROSS JOIN Sku k
WHERE NOT EXISTS (
SELECT 1 FROM DarkStore s
WHERE s.city = d.city
AND NOT EXISTS (SELECT 1 FROM StoreStock st WHERE st.store_id = s.store_id AND st.sku_id = k.sku_id AND st.qty_on_hand = 0)
)
ORDER BY d.city, k.sku_name← Swiggy: Share of Each Partner's Orders Delivered in a Batch · Swiggy: Swiggy One Plans That Paid for Themselves →