PhonePe: Delivered Pincode Orders per Partner Store, Zeros Included

Easy
879.

PhonePe: Delivered Pincode Orders per Partner Store, Zeros Included

The Pincode app routes grocery orders to neighbourhood partner stores. The partner team's weekly sheet lists every store with how many orders it delivered and their value, so stores with no deliveries stand out.

Return store_id, store_name, delivered_orders and delivered_value for every store. Only orders with status = 'delivered' count; a store with none shows 0 in both columns, never NULL. Return the rows in any order.

A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.

Tables

Table: PincodeStore

ColumnType
store_idint
store_namevarchar
pincodevarchar

Primary key: store_id.

One row per partner store.

Table: PincodeOrder

ColumnTypeValues
order_idint
store_idint
order_valueint
statusenumdelivered, cancelled, returned
placed_ondate

Primary key: order_id.

One row per order; store_id always names a store.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

PincodeStore

store_idstore_namepincode
1Annapurna Stores560034
2Daily Needs Mart560038
3Green Leaf Grocers560102
4Balaji Provisions560095

PincodeOrder

order_idstore_idorder_valuestatusplaced_on
1011640delivered2025-05-05
1021220delivered2025-05-06
1031410cancelled2025-05-06
1042980returned2025-05-07
1053155delivered2025-05-08
1062300cancelled2025-05-09

Output

store_idstore_namedelivered_ordersdelivered_value
1Annapurna Stores2860
2Daily Needs Mart00
3Green Leaf Grocers1155
4Balaji Provisions00
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.

PhonePe: Delivered Pincode Orders per Partner Store, Zeros Included

The Pincode app routes grocery orders to neighbourhood partner stores.

  • Difficulty: Easy
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #879

Problem statement

The Pincode app routes grocery orders to neighbourhood partner stores. The partner team's weekly sheet lists every store with how many orders it delivered and their value, so stores with no deliveries stand out.

Return store_id, store_name, delivered_orders and delivered_value for every store. Only orders with status = 'delivered' count; a store with none shows 0 in both columns, never NULL. Return the rows in any order.

A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.

Tables

Table: PincodeStore

ColumnType
store_idint
store_namevarchar
pincodevarchar

Primary key: store_id.

One row per partner store.

Table: PincodeOrder

ColumnType
order_idint
store_idint
order_valueint
statusenum(delivered, cancelled, returned)
placed_ondate

Primary key: order_id.

One row per order; store_id always names a store.

Examples

Example 1

PincodeStore

store_idstore_namepincode
1Annapurna Stores560034
2Daily Needs Mart560038
3Green Leaf Grocers560102
4Balaji Provisions560095

PincodeOrder

order_idstore_idorder_valuestatusplaced_on
1011640delivered2025-05-05
1021220delivered2025-05-06
1031410cancelled2025-05-06
1042980returned2025-05-07
1053155delivered2025-05-08
1062300cancelled2025-05-09

Output

store_idstore_namedelivered_ordersdelivered_value
1Annapurna Stores2860
2Daily Needs Mart00
3Green Leaf Grocers1155
4Balaji Provisions00

How to solve PhonePe: Delivered Pincode Orders per Partner Store, Zeros Included

"Every store, zeros included" means the store table drives the query and is outer-joined to the orders. The delivered filter goes inside the ON clause, so a store with only cancelled or returned orders still keeps one row, padded with NULLs, instead of being removed by a WHERE.

After grouping by store, COUNT(o.order_id) counts only real matches — the NULL padding of an unmatched store counts as 0, whereas COUNT(*) would wrongly report 1. SUM(o.order_value) over only NULLs is NULL, so COALESCE(…, 0) turns it into the 0 the sheet needs.

The alternatives show two other routes: scalar subqueries per store (simple to read, two lookups per store), and a plain left join with conditional sums, where the CASE decides what counts and an unmatched store's NULL status adds 0. With an index on PincodeOrder(store_id, status) each is a linear pass.

Reference solution (MySQL)

SELECT s.store_id, s.store_name,
       COUNT(o.order_id) AS delivered_orders,
       COALESCE(SUM(o.order_value), 0) AS delivered_value
FROM PincodeStore s
LEFT JOIN PincodeOrder o ON o.store_id = s.store_id AND o.status = 'delivered'
GROUP BY s.store_id, s.store_name

Another way

SELECT s.store_id, s.store_name,
  (SELECT COUNT(*) FROM PincodeOrder o WHERE o.store_id = s.store_id AND o.status = 'delivered') AS delivered_orders,
  (SELECT IFNULL(SUM(order_value), 0) FROM PincodeOrder o WHERE o.store_id = s.store_id AND o.status = 'delivered') AS delivered_value
FROM PincodeStore s

Another way

SELECT s.store_id, s.store_name, 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 delivered_value FROM PincodeStore s LEFT JOIN PincodeOrder o ON o.store_id = s.store_id GROUP BY s.store_id, s.store_name

← PhonePe: Collect Requests Still Pending After Their Expiry · PhonePe: Labelling Scratch Card Rewards for the Rewards Page →