Myntra: EORS First-Time Buyers Who Ordered Again Within 90 Days

Hard
818.

Myntra: EORS First-Time Buyers Who Ordered Again Within 90 Days

Big sale events bring in many first-time shoppers. The growth team wants to know, per sale edition, how many shoppers made their very first purchase during it and how many of them came back soon after.

A shopper's first order is their earliest order with a status other than cancelled. They are a new buyer of an edition when that first order's placed_on falls between the edition's starts_on and ends_on, inclusive. A new buyer came back when they have another non-cancelled order placed on a later day, at most 90 days after the first order's date. For every edition return edition_name, new_buyers and came_back, ordered by starts_on.

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: SaleEdition

ColumnType
edition_idint
edition_namevarchar
starts_ondate
ends_ondate

Primary key: edition_id.

One row per sale event; editions never overlap.

Table: ShopOrder

ColumnTypeValues
order_idint
user_idint
placed_ondate
statusenumdelivered, returned, cancelled

Primary key: order_id.

One row per order.

Example

The rows must come back in the order shown.

SaleEdition

edition_idedition_namestarts_onends_on
1EORS Summer 20242024-06-012024-06-06
2Big Fashion Festival 20242024-10-012024-10-08
3EORS Winter 20242024-12-142024-12-19

ShopOrder

order_iduser_idplaced_onstatus
112024-06-01delivered
212024-08-30delivered
322024-05-20cancelled
422024-06-06delivered
522024-06-06returned
622024-09-05delivered
732024-05-30delivered
832024-06-02delivered
942024-10-08returned
1042024-10-09cancelled
1 more row

Output

edition_namenew_buyerscame_back
EORS Summer 202421
Big Fashion Festival 202411
EORS Winter 202400
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: EORS First-Time Buyers Who Ordered Again Within 90 Days

Big sale events bring in many first-time shoppers. The growth team wants to know, per sale edition, how many shoppers made their very first purchase during…

  • Difficulty: Hard
  • Topics: Subqueries, Dates, Aggregation
  • Dialect: MySQL
  • Problem: #818

Problem statement

Big sale events bring in many first-time shoppers. The growth team wants to know, per sale edition, how many shoppers made their very first purchase during it and how many of them came back soon after.

A shopper's first order is their earliest order with a status other than cancelled. They are a new buyer of an edition when that first order's placed_on falls between the edition's starts_on and ends_on, inclusive. A new buyer came back when they have another non-cancelled order placed on a later day, at most 90 days after the first order's date. For every edition return edition_name, new_buyers and came_back, ordered by starts_on.

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: SaleEdition

ColumnType
edition_idint
edition_namevarchar
starts_ondate
ends_ondate

Primary key: edition_id.

One row per sale event; editions never overlap.

Table: ShopOrder

ColumnType
order_idint
user_idint
placed_ondate
statusenum(delivered, returned, cancelled)

Primary key: order_id.

One row per order.

Examples

Example 1

SaleEdition

edition_idedition_namestarts_onends_on
1EORS Summer 20242024-06-012024-06-06
2Big Fashion Festival 20242024-10-012024-10-08
3EORS Winter 20242024-12-142024-12-19

ShopOrder

order_iduser_idplaced_onstatus
112024-06-01delivered
212024-08-30delivered
322024-05-20cancelled
422024-06-06delivered
522024-06-06returned
622024-09-05delivered
732024-05-30delivered
832024-06-02delivered
942024-10-08returned
1042024-10-09cancelled
1142024-11-01delivered

Output

edition_namenew_buyerscame_back
EORS Summer 202421
Big Fashion Festival 202411
EORS Winter 202400

How to solve Myntra: EORS First-Time Buyers Who Ordered Again Within 90 Days

The question has three layers. First, which orders count: cancelled ones never do, so they are filtered out before anything else. Shopper 2's cancelled order on 20 May would otherwise make 20 May their first order and hide them from the summer sale.

Second, each shopper's first order: MIN(placed_on) per shopper over the valid orders. A shopper is a new buyer of the edition whose window contains that date; joining firsts to SaleEdition with BETWEEN starts_on AND ends_on (inclusive) assigns each shopper to at most one edition, since editions do not overlap. Shopper 3 first bought on 30 May, before the sale, and is nobody's new buyer. The join is a LEFT JOIN from the editions so that the winter edition, with no new buyers, still gets its row with zeros.

Third, the come-back test: another valid order on a later day (placed_on > first_on, so shopper 2's second order on the same day does not count) no more than 90 days on (DATEDIFF <= 90; 90 counts, 91 does not). COUNT(CASE WHEN EXISTS (…) THEN 1 END) counts the shoppers who pass; for an edition with no new buyers there is nothing to count. The alternative uses a window MIN for the first date and finds each shopper's next later order date with a conditional MIN, then tests the gap — one pass instead of a subquery per shopper.

Reference solution (MySQL)

WITH valid_orders AS (
  SELECT user_id, placed_on FROM ShopOrder WHERE status <> 'cancelled'
), firsts AS (
  SELECT user_id, MIN(placed_on) AS first_on FROM valid_orders GROUP BY user_id
)
SELECT e.edition_name,
       COUNT(f.user_id) AS new_buyers,
       COUNT(CASE WHEN EXISTS (SELECT 1 FROM valid_orders v
                               WHERE v.user_id = f.user_id AND v.placed_on > f.first_on
                                 AND DATEDIFF(v.placed_on, f.first_on) <= 90)
                  THEN 1 END) AS came_back
FROM SaleEdition e
LEFT JOIN firsts f ON f.first_on BETWEEN e.starts_on AND e.ends_on
GROUP BY e.edition_id, e.edition_name, e.starts_on
ORDER BY e.starts_on

Another way

WITH valid_orders AS (
  SELECT user_id, placed_on FROM ShopOrder WHERE status IN ('delivered', 'returned')
), ranked AS (
  SELECT user_id, placed_on, MIN(placed_on) OVER (PARTITION BY user_id) AS first_on FROM valid_orders
), seconds AS (
  SELECT user_id, first_on, MIN(CASE WHEN placed_on > first_on THEN placed_on END) AS next_on
  FROM ranked GROUP BY user_id, first_on
)
SELECT e.edition_name,
       COUNT(s.user_id) AS new_buyers,
       SUM(CASE WHEN s.next_on IS NOT NULL AND DATEDIFF(s.next_on, s.first_on) <= 90 THEN 1 ELSE 0 END) AS came_back
FROM SaleEdition e
LEFT JOIN seconds s ON s.first_on >= e.starts_on AND s.first_on <= e.ends_on
GROUP BY e.edition_id, e.edition_name, e.starts_on
ORDER BY e.starts_on

← Myntra: The Best Price Box for Each Style Across Three Offers · Myntra: Each Brand's Three Most-Wishlisted Styles in One Row →