Swiggy: Food Customers Who Later Tried Instamart
The cross-sell team measures how long it takes a food-delivery customer to try Instamart grocery for the first time.
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic, Dates
- Dialect: MySQL
- Problem: #961
Problem statement
The cross-sell team measures how long it takes a food-delivery customer to try Instamart grocery for the first time.
Using delivered orders only, find each customer's first food order date and first Instamart order date. Return customer_id, first_food_date, first_instamart_date and days_to_instamart (the days between the two) for every customer whose first Instamart order came strictly after their first food order. Customers who started with Instamart, tried both on the same day, or never used one of the two are left out. Genie tasks are ignored. Return the rows in any order.
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: SwiggyOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| business | enum(food, instamart, genie) |
| order_date | date |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per order across the app's businesses.
Examples
Example 1
SwiggyOrder
| order_id | customer_id | business | order_date | status |
|---|---|---|---|---|
| 1 | 1 | food | 2025-01-03 | delivered |
| 2 | 1 | instamart | 2025-01-10 | delivered |
| 3 | 2 | instamart | 2025-01-02 | delivered |
| 4 | 2 | food | 2025-01-05 | delivered |
| 5 | 3 | food | 2025-01-04 | cancelled |
| 6 | 3 | food | 2025-01-08 | delivered |
| 7 | 3 | instamart | 2025-01-08 | delivered |
| 8 | 4 | food | 2025-01-01 | delivered |
| 9 | 4 | genie | 2025-01-02 | delivered |
| 10 | 4 | instamart | 2025-02-01 | cancelled |
| 11 | 4 | instamart | 2025-02-15 | delivered |
| 12 | 5 | food | 2025-01-06 | delivered |
Output
| customer_id | first_food_date | first_instamart_date | days_to_instamart |
|---|---|---|---|
| 1 | 2025-01-03 | 2025-01-10 | 7 |
| 4 | 2025-01-01 | 2025-02-15 | 45 |
How to solve Swiggy: Food Customers Who Later Tried Instamart
Two minimums per customer, each over a different subset of rows, is the classic job for conditional aggregation. After keeping only delivered orders, MIN(CASE WHEN business = 'food' THEN order_date END) looks at food rows only — the CASE yields NULL for every other row and MIN skips NULLs — and the same expression for instamart gives the other first date. Genie rows are NULL in both and change nothing.
The outer filter first_instamart_date > first_food_date encodes the whole rule. Strictly greater drops same-day starters and those who began with Instamart, and a customer who never used one of the businesses has NULL on that side, so the comparison is unknown and the row is dropped too. Filtering cancelled orders first matters: a cancelled food order does not make the customer a food user that day. DATEDIFF(later, earlier) gives the days in between.
The alternative builds the two firsts as separate grouped CTEs and inner-joins them, which drops one-business customers in the join. It reads the table twice; conditional aggregation reads it once.
Reference solution (MySQL)
SELECT customer_id, first_food_date, first_instamart_date,
DATEDIFF(first_instamart_date, first_food_date) AS days_to_instamart
FROM (
SELECT customer_id,
MIN(CASE WHEN business = 'food' THEN order_date END) AS first_food_date,
MIN(CASE WHEN business = 'instamart' THEN order_date END) AS first_instamart_date
FROM SwiggyOrder
WHERE status = 'delivered'
GROUP BY customer_id
) f
WHERE first_instamart_date > first_food_dateAnother way
WITH food AS (SELECT customer_id, MIN(order_date) AS d FROM SwiggyOrder WHERE status = 'delivered' AND business = 'food' GROUP BY customer_id),
im AS (SELECT customer_id, MIN(order_date) AS d FROM SwiggyOrder WHERE status = 'delivered' AND business = 'instamart' GROUP BY customer_id)
SELECT food.customer_id, food.d AS first_food_date, im.d AS first_instamart_date, TIMESTAMPDIFF(DAY, food.d, im.d) AS days_to_instamart
FROM food JOIN im ON im.customer_id = food.customer_id
WHERE im.d > food.d← Swiggy: Longest Idle Gap in a Partner's Day · Swiggy: Genie Tasks Flagged by Blocked Keywords →