Swiggy: Instamart SKUs Out of Stock Across a Whole City

Medium
957.

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

ColumnType
store_idint
store_namevarchar
cityvarchar

Primary key: store_id.

One row per Instamart dark store.

Table: Sku

ColumnType
sku_idint
sku_namevarchar

Primary key: sku_id.

One row per catalogue item. Names are unique.

Table: StoreStock

ColumnType
store_idint
sku_idint
qty_on_handint

Primary key (together): store_id, sku_id.

One row per SKU a store lists, with the units on its shelves at snapshot time.

Example

The rows must come back in the order shown.

DarkStore

store_idstore_namecity
1HSR LayoutBengaluru
2KoramangalaBengaluru
3PowaiMumbai

Sku

sku_idsku_name
1Amul Butter
2Toned Milk
3Fresh Paneer
4Eggs Tray

StoreStock

store_idsku_idqty_on_hand
110
210
120
225
130
310
324
140
240
340

Output

citysku_name
BengaluruAmul Butter
BengaluruEggs Tray
MumbaiAmul Butter
MumbaiEggs Tray
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.

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

ColumnType
store_idint
store_namevarchar
cityvarchar

Primary key: store_id.

One row per Instamart dark store.

Table: Sku

ColumnType
sku_idint
sku_namevarchar

Primary key: sku_id.

One row per catalogue item. Names are unique.

Table: StoreStock

ColumnType
store_idint
sku_idint
qty_on_handint

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_idstore_namecity
1HSR LayoutBengaluru
2KoramangalaBengaluru
3PowaiMumbai

Sku

sku_idsku_name
1Amul Butter
2Toned Milk
3Fresh Paneer
4Eggs Tray

StoreStock

store_idsku_idqty_on_hand
110
210
120
225
130
310
324
140
240
340

Output

citysku_name
BengaluruAmul Butter
BengaluruEggs Tray
MumbaiAmul Butter
MumbaiEggs 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_name

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