Myntra: M-Now Deliveries Inside the 30-Minute Promise by Store

Easy
805.

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.

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

ColumnType
store_idint
store_namevarchar
cityvarchar

Primary key: store_id.

One row per quick-delivery dark store.

Table: MNowOrder

ColumnType
order_idint
store_idint
placed_atdatetime
delivered_atdatetime

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.

Example

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

DarkStore

store_idstore_namecity
1HSR LayoutBengaluru
2Andheri WestMumbai
3Sector 29Gurugram

MNowOrder

order_idstore_idplaced_atdelivered_at
112024-08-01 10:00:002024-08-01 10:30:00
212024-08-01 11:00:002024-08-01 11:31:00
312024-08-01 23:50:002024-08-02 00:15:00
422024-08-01 12:00:00NULL
522024-08-01 12:10:002024-08-01 12:52:00
632024-08-01 09:00:00NULL

Output

store_namedeliveredon_time
HSR Layout32
Andheri West10
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.

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

ColumnType
store_idint
store_namevarchar
cityvarchar

Primary key: store_id.

One row per quick-delivery dark store.

Table: MNowOrder

ColumnType
order_idint
store_idint
placed_atdatetime
delivered_atdatetime

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_idstore_namecity
1HSR LayoutBengaluru
2Andheri WestMumbai
3Sector 29Gurugram

MNowOrder

order_idstore_idplaced_atdelivered_at
112024-08-01 10:00:002024-08-01 10:30:00
212024-08-01 11:00:002024-08-01 11:31:00
312024-08-01 23:50:002024-08-02 00:15:00
422024-08-01 12:00:00NULL
522024-08-01 12:10:002024-08-01 12:52:00
632024-08-01 09:00:00NULL

Output

store_namedeliveredon_time
HSR Layout32
Andheri West10

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_name

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