Delivered GMV of Every Dark Store

Easy
151.

Delivered GMV of Every Dark Store

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

ColumnType
store_idint
localityvarchar
cityvarchar

Primary key: store_id.

One row per dark store.

Table: QuickOrder

ColumnTypeValues
order_idint
store_idint
ordered_atdatetime
order_valueint
statusenumdelivered, cancelled

Primary key: order_id.

One row per order, in rupees. store_id is always in DarkStore.

Example

The rows must come back in the order shown.

DarkStore

store_idlocalitycity
1KoramangalaBengaluru
2PowaiMumbai
3GachibowliHyderabad
4Salt LakeKolkata

QuickOrder

order_idstore_idordered_atorder_valuestatus
10112024-10-02 08:15:00420delivered
10212024-10-02 09:40:00180cancelled
10322024-10-02 10:05:00600delivered
10412024-10-03 19:20:00180delivered
10532024-10-03 21:10:00250cancelled
10622024-10-04 07:55:0099cancelled

Output

store_idlocalitydelivered_ordersgmv
1Koramangala2600
2Powai1600
3Gachibowli00
4Salt Lake00
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.

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

ColumnType
store_idint
localityvarchar
cityvarchar

Primary key: store_id.

One row per dark store.

Table: QuickOrder

ColumnType
order_idint
store_idint
ordered_atdatetime
order_valueint
statusenum(delivered, cancelled)

Primary key: order_id.

One row per order, in rupees. store_id is always in DarkStore.

Examples

Example 1

DarkStore

store_idlocalitycity
1KoramangalaBengaluru
2PowaiMumbai
3GachibowliHyderabad
4Salt LakeKolkata

QuickOrder

order_idstore_idordered_atorder_valuestatus
10112024-10-02 08:15:00420delivered
10212024-10-02 09:40:00180cancelled
10322024-10-02 10:05:00600delivered
10412024-10-03 19:20:00180delivered
10532024-10-03 21:10:00250cancelled
10622024-10-04 07:55:0099cancelled

Output

store_idlocalitydelivered_ordersgmv
1Koramangala2600
2Powai1600
3Gachibowli00
4Salt Lake00

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_id

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

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