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
| Column | Type |
|---|---|
| store_id | int |
| store_name | varchar |
| pincode | varchar |
Primary key: store_id.
One row per partner store.
Table: PincodeOrder
| Column | Type |
|---|---|
| order_id | int |
| store_id | int |
| order_value | int |
| status | enum(delivered, cancelled, returned) |
| placed_on | date |
Primary key: order_id.
One row per order; store_id always names a store.
Examples
Example 1
PincodeStore
| store_id | store_name | pincode |
|---|---|---|
| 1 | Annapurna Stores | 560034 |
| 2 | Daily Needs Mart | 560038 |
| 3 | Green Leaf Grocers | 560102 |
| 4 | Balaji Provisions | 560095 |
PincodeOrder
| order_id | store_id | order_value | status | placed_on |
|---|---|---|---|---|
| 101 | 1 | 640 | delivered | 2025-05-05 |
| 102 | 1 | 220 | delivered | 2025-05-06 |
| 103 | 1 | 410 | cancelled | 2025-05-06 |
| 104 | 2 | 980 | returned | 2025-05-07 |
| 105 | 3 | 155 | delivered | 2025-05-08 |
| 106 | 2 | 300 | cancelled | 2025-05-09 |
Output
| store_id | store_name | delivered_orders | delivered_value |
|---|---|---|---|
| 1 | Annapurna Stores | 2 | 860 |
| 2 | Daily Needs Mart | 0 | 0 |
| 3 | Green Leaf Grocers | 1 | 155 |
| 4 | Balaji Provisions | 0 | 0 |
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_nameAnother 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 sAnother 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 →