Delivered GMV of Every Dark Store — SQL Joins Problem
A quick-commerce company runs small warehouses (dark stores) that pack ten-minute grocery orders.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #151
Problem statement
A quick-commerce company runs small warehouses (dark stores) that pack ten-minute grocery orders. Finance wants each store's count of delivered orders and their gross merchandise value; cancelled orders are not counted, and a store with no delivered order must still appear with zeros.
Return store_id, locality, delivered_orders and gmv (sum of order_value of delivered orders, 0 when there are none) for every dark store, ordered by gmv descending, then store_id ascending.
Tables
Table: DarkStore
| Column | Type |
|---|---|
| store_id | int |
| locality | varchar |
| city | varchar |
Primary key: store_id.
One row per dark store.
Table: QuickOrder
| Column | Type |
|---|---|
| order_id | int |
| store_id | int |
| ordered_at | datetime |
| order_value | int |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per order, in rupees. store_id is always in DarkStore.
Examples
Example 1
DarkStore
| store_id | locality | city |
|---|---|---|
| 1 | Koramangala | Bengaluru |
| 2 | Powai | Mumbai |
| 3 | Gachibowli | Hyderabad |
| 4 | Salt Lake | Kolkata |
QuickOrder
| order_id | store_id | ordered_at | order_value | status |
|---|---|---|---|---|
| 101 | 1 | 2024-10-02 08:15:00 | 420 | delivered |
| 102 | 1 | 2024-10-02 09:40:00 | 180 | cancelled |
| 103 | 2 | 2024-10-02 10:05:00 | 600 | delivered |
| 104 | 1 | 2024-10-03 19:20:00 | 180 | delivered |
| 105 | 3 | 2024-10-03 21:10:00 | 250 | cancelled |
| 106 | 2 | 2024-10-04 07:55:00 | 99 | cancelled |
Output
| store_id | locality | delivered_orders | gmv |
|---|---|---|---|
| 1 | Koramangala | 2 | 600 |
| 2 | Powai | 1 | 600 |
| 3 | Gachibowli | 0 | 0 |
| 4 | Salt Lake | 0 | 0 |
How to solve Delivered GMV of Every Dark Store
Every store must be in the answer, so the query starts from DarkStore and LEFT JOINs QuickOrder, then groups by store. The subtle part is where the status = 'delivered' condition goes. Put in WHERE, it runs after the join and removes the NULL-extended row of a store that had no orders — and every row of a store whose orders were all cancelled — so those stores vanish. Put in the ON clause, it limits which orders join, and a store with no delivered order still gets its one NULL-extended row.
On that row COUNT(o.order_id) is 0 because COUNT of a column skips NULL, while SUM(o.order_value) is NULL — SUM over nothing has no value — so COALESCE turns it into 0. Conditional aggregation over an unfiltered LEFT JOIN (SUM(CASE WHEN status = 'delivered' …)) gets the same numbers, and two correlated subqueries per store do too, at the cost of reading the orders twice.
Stores can tie on GMV (two stores with nothing delivered both have 0), so store_id completes the order. One pass over the orders plus a group per store.
Reference solution (MySQL)
SELECT s.store_id, s.locality,
COUNT(o.order_id) AS delivered_orders,
COALESCE(SUM(o.order_value), 0) AS gmv
FROM DarkStore s
LEFT JOIN QuickOrder o ON o.store_id = s.store_id AND o.status = 'delivered'
GROUP BY s.store_id, s.locality
ORDER BY gmv DESC, s.store_idAnother way
SELECT s.store_id, s.locality,
SUM(CASE WHEN o.status = 'delivered' THEN 1 ELSE 0 END) AS delivered_orders,
SUM(CASE WHEN o.status = 'delivered' THEN o.order_value ELSE 0 END) AS gmv
FROM DarkStore s LEFT JOIN QuickOrder o ON o.store_id = s.store_id
GROUP BY s.store_id, s.locality
ORDER BY gmv DESC, s.store_idAnother way
SELECT s.store_id, s.locality,
(SELECT COUNT(*) FROM QuickOrder o WHERE o.store_id = s.store_id AND o.status = 'delivered') AS delivered_orders,
(SELECT COALESCE(SUM(order_value), 0) FROM QuickOrder o WHERE o.store_id = s.store_id AND o.status = 'delivered') AS gmv
FROM DarkStore s ORDER BY gmv DESC, s.store_id← Menu Items Nobody Has Ordered · Masked Customer Phone Numbers on Rider Slips →