Flipkart: Sale-Acquired Buyers Who Came Back for the Next Sale

Hard
669.

Flipkart: Sale-Acquired Buyers Who Came Back for the Next Sale

Marketing asks whether big sales bring in customers who stay. A customer is acquired by a sale event when their first non-cancelled order ever falls within that event's dates (inclusive). An acquired customer came back if they placed a non-cancelled order during the next sale event (the event with the next later starts_on).

For every sale event return event_name, acquired (customers it acquired, 0 if none) and returned_next (how many of them came back in the next event). For the latest event there is no next event yet, so returned_next is NULL. Order by the event's starts_on.

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

Tables

Table: SaleEvent

ColumnType
event_idint
event_namevarchar
starts_ondate
ends_ondate

Primary key: event_id.

One row per sale event; events never overlap.

Table: CustomerOrder

ColumnTypeValues
order_idint
customer_idint
ordered_ondate
order_statusenumdelivered, returned, cancelled

Primary key: order_id.

Every order a customer has ever placed, sale or not. Returned orders still count as orders; cancelled ones never do.

Example

The rows must come back in the order shown.

SaleEvent

event_idevent_namestarts_onends_on
1Big Billion Days 20232023-10-082023-10-15
2Big Saving Days Jan 20242024-01-142024-01-19
3Big Saving Days Jul 20242024-07-202024-07-25
4Big Billion Days 20242024-09-272024-10-06

CustomerOrder

order_idcustomer_idordered_onorder_status
11012023-10-09delivered
21012024-01-15delivered
31022023-10-10cancelled
41022023-11-20delivered
51032023-10-12delivered
61032024-07-21delivered
71042024-01-16returned
81042024-07-22cancelled
91052024-07-20delivered
101052024-09-27delivered
1 more row

Output

event_nameacquiredreturned_next
Big Billion Days 202321
Big Saving Days Jan 202410
Big Saving Days Jul 202411
Big Billion Days 20241NULL
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.

Flipkart: Sale-Acquired Buyers Who Came Back for the Next Sale

Marketing asks whether big sales bring in customers who stay.

  • Difficulty: Hard
  • Topics: Window Functions, Subqueries, Joins, Dates
  • Dialect: MySQL
  • Problem: #669

Problem statement

Marketing asks whether big sales bring in customers who stay. A customer is acquired by a sale event when their first non-cancelled order ever falls within that event's dates (inclusive). An acquired customer came back if they placed a non-cancelled order during the next sale event (the event with the next later starts_on).

For every sale event return event_name, acquired (customers it acquired, 0 if none) and returned_next (how many of them came back in the next event). For the latest event there is no next event yet, so returned_next is NULL. Order by the event's starts_on.

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

Tables

Table: SaleEvent

ColumnType
event_idint
event_namevarchar
starts_ondate
ends_ondate

Primary key: event_id.

One row per sale event; events never overlap.

Table: CustomerOrder

ColumnType
order_idint
customer_idint
ordered_ondate
order_statusenum(delivered, returned, cancelled)

Primary key: order_id.

Every order a customer has ever placed, sale or not. Returned orders still count as orders; cancelled ones never do.

Examples

Example 1

SaleEvent

event_idevent_namestarts_onends_on
1Big Billion Days 20232023-10-082023-10-15
2Big Saving Days Jan 20242024-01-142024-01-19
3Big Saving Days Jul 20242024-07-202024-07-25
4Big Billion Days 20242024-09-272024-10-06

CustomerOrder

order_idcustomer_idordered_onorder_status
11012023-10-09delivered
21012024-01-15delivered
31022023-10-10cancelled
41022023-11-20delivered
51032023-10-12delivered
61032024-07-21delivered
71042024-01-16returned
81042024-07-22cancelled
91052024-07-20delivered
101052024-09-27delivered
111062024-09-30delivered

Output

event_nameacquiredreturned_next
Big Billion Days 202321
Big Saving Days Jan 202410
Big Saving Days Jul 202411
Big Billion Days 20241NULL

How to solve Flipkart: Sale-Acquired Buyers Who Came Back for the Next Sale

The question chains three steps. Acquisition: each customer's first non-cancelled order, MIN(ordered_on) over their non-cancelled orders; a customer is acquired by the event whose dates contain that day. A customer whose first order was cancelled is judged by the next real order — which may fall outside every sale, in which case no event acquired them.

The next event: LEAD(event_id) OVER (ORDER BY starts_on) gives every event its successor in time, and NULL for the latest. Coming back: join each acquired customer to the next event's dates and to their non-cancelled orders inside it; DISTINCT makes a customer with three orders in the next sale count once.

The final SELECT starts from the events, so an event that acquired nobody still appears with acquired = 0 and returned_next = 0, while the latest event shows NULL because its next_id is NULL — the CASE keeps "not measurable yet" apart from "nobody came back". The alternative finds the next event with a correlated MIN(starts_on) > this start and counts returners with EXISTS inside SUM. Either is a handful of passes over orders and events.

Reference solution (MySQL)

WITH firsts AS (
  SELECT customer_id, MIN(ordered_on) AS first_on
  FROM CustomerOrder WHERE order_status <> 'cancelled' GROUP BY customer_id
), ev AS (
  SELECT event_id, event_name, starts_on, ends_on, LEAD(event_id) OVER (ORDER BY starts_on) AS next_id
  FROM SaleEvent
), acq AS (
  SELECT e.event_id, e.next_id, f.customer_id
  FROM ev e JOIN firsts f ON f.first_on BETWEEN e.starts_on AND e.ends_on
), came_back AS (
  SELECT DISTINCT a.event_id, a.customer_id
  FROM acq a
  JOIN SaleEvent n ON n.event_id = a.next_id
  JOIN CustomerOrder o ON o.customer_id = a.customer_id AND o.order_status <> 'cancelled'
   AND o.ordered_on BETWEEN n.starts_on AND n.ends_on
)
SELECT e.event_name,
       (SELECT COUNT(*) FROM acq a WHERE a.event_id = e.event_id) AS acquired,
       CASE WHEN e.next_id IS NULL THEN NULL
            ELSE (SELECT COUNT(*) FROM came_back c WHERE c.event_id = e.event_id) END AS returned_next
FROM ev e
ORDER BY e.starts_on

Another way

WITH f AS (
  SELECT customer_id, MIN(ordered_on) AS first_on FROM CustomerOrder WHERE order_status <> 'cancelled' GROUP BY customer_id
), a AS (
  SELECT s.event_id, f.customer_id FROM SaleEvent s JOIN f ON f.first_on >= s.starts_on AND f.first_on <= s.ends_on
)
SELECT s.event_name, COUNT(a.customer_id) AS acquired,
       CASE WHEN NOT EXISTS (SELECT 1 FROM SaleEvent z WHERE z.starts_on > s.starts_on) THEN NULL
            ELSE SUM(CASE WHEN EXISTS (
                   SELECT 1 FROM CustomerOrder o JOIN SaleEvent nx ON o.ordered_on BETWEEN nx.starts_on AND nx.ends_on
                   WHERE o.customer_id = a.customer_id AND o.order_status <> 'cancelled'
                     AND nx.starts_on = (SELECT MIN(z.starts_on) FROM SaleEvent z WHERE z.starts_on > s.starts_on))
                 THEN 1 ELSE 0 END) END AS returned_next
FROM SaleEvent s LEFT JOIN a ON a.event_id = s.event_id
GROUP BY s.event_id, s.event_name, s.starts_on
ORDER BY s.starts_on

← Flipkart: Month-to-Month Sales Quartile Movement Matrix · Flipkart: Unscanned Ekart AWB Numbers as Ranges per Hub →