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
| Column | Type |
|---|---|
| store_id | int |
| store_name | varchar |
| city | varchar |
Primary key: store_id.
One row per dark store; names are unique.
Table: InstamartOrder
| Column | Type |
|---|---|
| order_id | int |
| store_id | int |
| order_date | date |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per Instamart order, fulfilled by one store.
Examples
Example 1
DarkStore
| store_id | store_name | city |
|---|---|---|
| 1 | HSR Layout | Bengaluru |
| 2 | Koramangala | Bengaluru |
| 3 | Powai | Mumbai |
| 4 | Andheri East | Mumbai |
InstamartOrder
| order_id | store_id | order_date | status |
|---|---|---|---|
| 1 | 1 | 2025-03-02 | delivered |
| 2 | 1 | 2025-03-15 | delivered |
| 3 | 2 | 2025-03-03 | delivered |
| 4 | 2 | 2025-03-21 | delivered |
| 5 | 2 | 2025-03-22 | cancelled |
| 6 | 3 | 2025-03-30 | delivered |
| 7 | 1 | 2025-04-01 | delivered |
| 8 | 2 | 2025-04-02 | delivered |
| 9 | 2 | 2025-04-30 | delivered |
| 10 | 4 | 2025-04-11 | delivered |
| 11 | 3 | 2025-04-12 | cancelled |
Output
| city | order_month | store_name | orders |
|---|---|---|---|
| Bengaluru | 2025-03 | HSR Layout | 2 |
| Bengaluru | 2025-03 | Koramangala | 2 |
| Bengaluru | 2025-04 | Koramangala | 2 |
| Mumbai | 2025-03 | Powai | 1 |
| Mumbai | 2025-04 | Andheri East | 1 |
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_nameAnother 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 →