Swiggy: Busiest Instamart Store per City Each Month

Medium
963.

Swiggy: Busiest Instamart Store per City Each Month

Regional managers reward the busiest dark store of their city every month, so the Instamart analytics team needs the winner for each city and month.

Count each store's delivered orders per calendar month. For every city and month, return the store (or stores, if tied) with the most delivered orders in that month: columns city, order_month (formatted YYYY-MM), store_name and orders. Only stores with at least one delivered order in the month compete. Order by city, order_month, then store_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 dark store; names are unique.

Table: InstamartOrder

ColumnTypeValues
order_idint
store_idint
order_datedate
statusenumdelivered, cancelled

Primary key: order_id.

One row per Instamart order, fulfilled by one store.

Example

The rows must come back in the order shown.

DarkStore

store_idstore_namecity
1HSR LayoutBengaluru
2KoramangalaBengaluru
3PowaiMumbai
4Andheri EastMumbai

InstamartOrder

order_idstore_idorder_datestatus
112025-03-02delivered
212025-03-15delivered
322025-03-03delivered
422025-03-21delivered
522025-03-22cancelled
632025-03-30delivered
712025-04-01delivered
822025-04-02delivered
922025-04-30delivered
1042025-04-11delivered
1 more row

Output

cityorder_monthstore_nameorders
Bengaluru2025-03HSR Layout2
Bengaluru2025-03Koramangala2
Bengaluru2025-04Koramangala2
Mumbai2025-03Powai1
Mumbai2025-04Andheri East1
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: Busiest Instamart Store per City Each Month

Regional managers reward the busiest dark store of their city every month, so the Instamart analytics team needs the winner for each city and month.

  • Difficulty: Medium
  • Topics: Window Functions, Aggregation, Dates
  • Dialect: MySQL
  • Problem: #963

Problem statement

Regional managers reward the busiest dark store of their city every month, so the Instamart analytics team needs the winner for each city and month.

Count each store's delivered orders per calendar month. For every city and month, return the store (or stores, if tied) with the most delivered orders in that month: columns city, order_month (formatted YYYY-MM), store_name and orders. Only stores with at least one delivered order in the month compete. Order by city, order_month, then store_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 dark store; names are unique.

Table: InstamartOrder

ColumnType
order_idint
store_idint
order_datedate
statusenum(delivered, cancelled)

Primary key: order_id.

One row per Instamart order, fulfilled by one store.

Examples

Example 1

DarkStore

store_idstore_namecity
1HSR LayoutBengaluru
2KoramangalaBengaluru
3PowaiMumbai
4Andheri EastMumbai

InstamartOrder

order_idstore_idorder_datestatus
112025-03-02delivered
212025-03-15delivered
322025-03-03delivered
422025-03-21delivered
522025-03-22cancelled
632025-03-30delivered
712025-04-01delivered
822025-04-02delivered
922025-04-30delivered
1042025-04-11delivered
1132025-04-12cancelled

Output

cityorder_monthstore_nameorders
Bengaluru2025-03HSR Layout2
Bengaluru2025-03Koramangala2
Bengaluru2025-04Koramangala2
Mumbai2025-03Powai1
Mumbai2025-04Andheri East1

How to solve Swiggy: Busiest Instamart Store per City Each Month

The question has two levels of aggregation, so it takes two steps. The first, monthly, joins orders to their store, keeps delivered ones and counts them per store and month — the month label comes from DATE_FORMAT(order_date, '%Y-%m'), grouped by its alias so MySQL's ONLY_FULL_GROUP_BY is satisfied. Stores with no delivered order that month have no row and so never compete.

The second step picks winners within each (city, month). RANK() OVER (PARTITION BY city, order_month ORDER BY orders DESC) numbers the stores from the busiest; stores with equal counts share a rank, so every tied winner has rank 1. ROW_NUMBER would be wrong here — it would keep one tied store arbitrarily.

The alternative compares each store-month with the maximum of its own city and month through a correlated subquery, and labels the month with LEFT(order_date, 7), which is the same text for a YYYY-MM-DD date. Both group the orders once; the window version then sorts each partition, the correlated one rescans the small monthly result per row.

Reference solution (MySQL)

WITH monthly AS (
  SELECT d.city, DATE_FORMAT(o.order_date, '%Y-%m') AS order_month, d.store_name, COUNT(*) AS orders
  FROM InstamartOrder o
  JOIN DarkStore d ON d.store_id = o.store_id
  WHERE o.status = 'delivered'
  GROUP BY d.city, order_month, d.store_id, d.store_name
), ranked AS (
  SELECT city, order_month, store_name, orders,
         RANK() OVER (PARTITION BY city, order_month ORDER BY orders DESC) AS pos
  FROM monthly
)
SELECT city, order_month, store_name, orders
FROM ranked
WHERE pos = 1
ORDER BY city, order_month, store_name

Another way

WITH monthly AS (
  SELECT d.city, LEFT(o.order_date, 7) AS order_month, d.store_name, COUNT(*) AS orders
  FROM InstamartOrder o JOIN DarkStore d ON d.store_id = o.store_id
  WHERE o.status = 'delivered'
  GROUP BY d.city, order_month, d.store_id, d.store_name
)
SELECT m.city, m.order_month, m.store_name, m.orders
FROM monthly m
WHERE m.orders = (SELECT MAX(m2.orders) FROM monthly m2 WHERE m2.city = m.city AND m2.order_month = m.order_month)
ORDER BY m.city, m.order_month, m.store_name

← Swiggy: Genie Tasks Flagged by Blocked Keywords · Swiggy: Partner Top-Up to the Shift Earnings Guarantee →