Myntra: M-Now Deliveries Inside the 30-Minute Promise by Store
M-Now promises fashion delivered fast from neighbourhood dark stores, and the ops team reviews each store's record against a 30-minute promise.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic, Dates
- Dialect: MySQL
- Problem: #805
Problem statement
M-Now promises fashion delivered fast from neighbourhood dark stores, and the ops team reviews each store's record against a 30-minute promise.
Consider only orders that have been delivered (delivered_at is not NULL). For every dark store with at least one such order, return store_name, delivered (its delivered orders) and on_time (those delivered at most 30 minutes after placed_at). Stores with no delivered order do not appear. Return the rows in any order.
A practice scenario set at Myntra: the tables and data are invented, and this is not a question from, or endorsed by, Myntra.
Tables
Table: DarkStore
| Column | Type |
|---|---|
| store_id | int |
| store_name | varchar |
| city | varchar |
Primary key: store_id.
One row per quick-delivery dark store.
Table: MNowOrder
| Column | Type |
|---|---|
| order_id | int |
| store_id | int |
| placed_at | datetime |
| delivered_at | datetime |
Primary key: order_id.
Times are to the whole minute. delivered_at is NULL while the order is still on its way or was cancelled.
Examples
Example 1
DarkStore
| store_id | store_name | city |
|---|---|---|
| 1 | HSR Layout | Bengaluru |
| 2 | Andheri West | Mumbai |
| 3 | Sector 29 | Gurugram |
MNowOrder
| order_id | store_id | placed_at | delivered_at |
|---|---|---|---|
| 1 | 1 | 2024-08-01 10:00:00 | 2024-08-01 10:30:00 |
| 2 | 1 | 2024-08-01 11:00:00 | 2024-08-01 11:31:00 |
| 3 | 1 | 2024-08-01 23:50:00 | 2024-08-02 00:15:00 |
| 4 | 2 | 2024-08-01 12:00:00 | NULL |
| 5 | 2 | 2024-08-01 12:10:00 | 2024-08-01 12:52:00 |
| 6 | 3 | 2024-08-01 09:00:00 | NULL |
Output
| store_name | delivered | on_time |
|---|---|---|
| HSR Layout | 3 | 2 |
| Andheri West | 1 | 0 |
How to solve Myntra: M-Now Deliveries Inside the 30-Minute Promise by Store
The answer is two counts per store, one of them conditional. Undelivered orders (delivered_at NULL) are removed in WHERE, so they count in neither column; after that, COUNT(*) per store is the delivered total, and a store whose orders were all undelivered has no rows left and drops out, as asked.
The on-time count is conditional aggregation: SUM(CASE WHEN … THEN 1 ELSE 0 END) adds one for each order meeting the promise. The test is TIMESTAMPDIFF(MINUTE, placed_at, delivered_at) <= 30; working on full timestamps, it handles an order placed at 23:50 and delivered at 00:15 the next day as the 25 minutes it was, which comparing clock times alone would get wrong. Exactly 30 minutes is on time.
The alternative aggregates the orders first in a derived table and compares delivered_at with DATE_ADD(placed_at, INTERVAL 30 MINUTE) instead of computing a difference, using COUNT(CASE … THEN 1 END) — COUNT ignores the NULL a CASE without ELSE returns. Both make one pass over the orders.
Reference solution (MySQL)
SELECT d.store_name,
COUNT(*) AS delivered,
SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, o.placed_at, o.delivered_at) <= 30 THEN 1 ELSE 0 END) AS on_time
FROM DarkStore d
JOIN MNowOrder o ON o.store_id = d.store_id
WHERE o.delivered_at IS NOT NULL
GROUP BY d.store_id, d.store_nameAnother way
SELECT d.store_name, t.delivered, t.on_time
FROM DarkStore d
JOIN (SELECT store_id, COUNT(*) AS delivered,
COUNT(CASE WHEN delivered_at <= DATE_ADD(placed_at, INTERVAL 30 MINUTE) THEN 1 END) AS on_time
FROM MNowOrder WHERE delivered_at IS NOT NULL GROUP BY store_id) t ON t.store_id = d.store_id← Myntra: Return Requests Raised After the Category's Window · Myntra: Size-Related Return Rate by Article Type and Size →