Swiggy: Third Delivered Order of Each New Customer

Hard
966.

Swiggy: Third Delivered Order of Each New Customer

New customers get free delivery on their first three delivered orders, but only if those orders come within 30 days of signing up. The growth team wants the order on which each customer used up the offer — their third delivered order — and whether it landed inside the window.

Count a customer's delivered orders in order of ordered_at; two orders placed at the same moment count in order of order_id. Cancelled orders are never counted. For every customer with at least three delivered orders, return customer_id, third_order_id, third_ordered_at, days_to_third (days from signup_date to the date of that order) and within_offer — 1 when days_to_third is at most 30, else 0. Customers with fewer than three delivered orders are left out. Order the rows by customer_id.

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

Tables

Table: Customer

ColumnType
customer_idint
signup_datedate

Primary key: customer_id.

One row per account, with the day it signed up.

Table: SwiggyOrder

ColumnTypeValues
order_idint
customer_idint
ordered_atdatetime
statusenumdelivered, cancelled

Primary key: order_id.

One row per order across food and Instamart, never before the customer's signup. Order ids are not in time order.

Example

The rows must come back in the order shown.

Customer

customer_idsignup_date
12025-05-01
22025-05-03
32025-05-10
42025-05-12

SwiggyOrder

order_idcustomer_idordered_atstatus
1512025-05-02 13:10:00delivered
1112025-05-09 20:00:00cancelled
1812025-05-20 21:30:00delivered
1212025-05-31 19:45:00delivered
2012025-06-02 12:00:00delivered
1322025-05-05 09:00:00delivered
2122025-05-05 09:00:00delivered
1422025-06-10 22:15:00delivered
1632025-05-11 08:30:00delivered
1732025-05-12 08:30:00delivered
1 more row

Output

customer_idthird_order_idthird_ordered_atdays_to_thirdwithin_offer
1122025-05-31 19:45:00301
2142025-06-10 22:15:00380
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.

Swiggy: Third Delivered Order of Each New Customer

New customers get free delivery on their first three delivered orders, but only if those orders come within 30 days of signing up.

  • Difficulty: Hard
  • Topics: Window Functions, Dates, Conditional Logic
  • Dialect: MySQL
  • Problem: #966

Problem statement

New customers get free delivery on their first three delivered orders, but only if those orders come within 30 days of signing up. The growth team wants the order on which each customer used up the offer — their third delivered order — and whether it landed inside the window.

Count a customer's delivered orders in order of ordered_at; two orders placed at the same moment count in order of order_id. Cancelled orders are never counted. For every customer with at least three delivered orders, return customer_id, third_order_id, third_ordered_at, days_to_third (days from signup_date to the date of that order) and within_offer — 1 when days_to_third is at most 30, else 0. Customers with fewer than three delivered orders are left out. Order the rows by customer_id.

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

Tables

Table: Customer

ColumnType
customer_idint
signup_datedate

Primary key: customer_id.

One row per account, with the day it signed up.

Table: SwiggyOrder

ColumnType
order_idint
customer_idint
ordered_atdatetime
statusenum(delivered, cancelled)

Primary key: order_id.

One row per order across food and Instamart, never before the customer's signup. Order ids are not in time order.

Examples

Example 1

Customer

customer_idsignup_date
12025-05-01
22025-05-03
32025-05-10
42025-05-12

SwiggyOrder

order_idcustomer_idordered_atstatus
1512025-05-02 13:10:00delivered
1112025-05-09 20:00:00cancelled
1812025-05-20 21:30:00delivered
1212025-05-31 19:45:00delivered
2012025-06-02 12:00:00delivered
1322025-05-05 09:00:00delivered
2122025-05-05 09:00:00delivered
1422025-06-10 22:15:00delivered
1632025-05-11 08:30:00delivered
1732025-05-12 08:30:00delivered
1942025-05-12 18:00:00delivered

Output

customer_idthird_order_idthird_ordered_atdays_to_thirdwithin_offer
1122025-05-31 19:45:00301
2142025-06-10 22:15:00380

How to solve Swiggy: Third Delivered Order of Each New Customer

"The n-th event per key" is a numbering problem. Filter to delivered orders, then ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id) gives each customer's orders the positions 1, 2, 3, … in the order they happened. The second sort key is essential: two orders at the same instant would otherwise be numbered arbitrarily, and the answer could change from run to run. Order ids are deliberately not in time order, so sorting by id alone would be wrong. ROW_NUMBER is the right function rather than RANK, which would give tied orders the same position and could skip 3.

Keeping nth = 3 leaves exactly one row per customer who reached a third delivered order; customers with fewer have no third row and drop out without a HAVING. Filtering cancellations before numbering is what makes a cancelled order not use up a position — filtering after would leave holes in the numbering.

DATEDIFF(ordered_at, signup_date) counts calendar days, ignoring the time of day, and the offer flag is a CASE on it: day 30 is inside, day 31 is not. The alternative finds the third order without window functions — an order is third when exactly three delivered orders of the customer come at or before it in (time, id) order. That is quadratic per customer; the window version sorts once.

Reference solution (MySQL)

WITH numbered AS (
  SELECT order_id, customer_id, ordered_at,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id) AS nth
  FROM SwiggyOrder
  WHERE status = 'delivered'
)
SELECT n.customer_id, n.order_id AS third_order_id, n.ordered_at AS third_ordered_at,
       DATEDIFF(n.ordered_at, c.signup_date) AS days_to_third,
       CASE WHEN DATEDIFF(n.ordered_at, c.signup_date) <= 30 THEN 1 ELSE 0 END AS within_offer
FROM numbered n
JOIN Customer c ON c.customer_id = n.customer_id
WHERE n.nth = 3
ORDER BY n.customer_id

Another way

SELECT o.customer_id, o.order_id AS third_order_id, o.ordered_at AS third_ordered_at,
       DATEDIFF(o.ordered_at, c.signup_date) AS days_to_third,
       IF(DATE(o.ordered_at) <= DATE_ADD(c.signup_date, INTERVAL 30 DAY), 1, 0) AS within_offer
FROM SwiggyOrder o
JOIN Customer c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
  AND (SELECT COUNT(*) FROM SwiggyOrder p
       WHERE p.customer_id = o.customer_id AND p.status = 'delivered'
         AND (p.ordered_at < o.ordered_at OR (p.ordered_at = o.ordered_at AND p.order_id <= o.order_id))) = 3
ORDER BY o.customer_id

← Swiggy: Swiggy Money Debits That Overdrew the Wallet · Swiggy: Restaurant Offline Minutes Within Opening Hours →