Swiggy: Food Customers Who Later Tried Instamart

Medium
961.

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.

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

ColumnTypeValues
order_idint
customer_idint
businessenumfood, instamart, genie
order_datedate
statusenumdelivered, cancelled

Primary key: order_id.

One row per order across the app's businesses.

Example

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

SwiggyOrder

order_idcustomer_idbusinessorder_datestatus
11food2025-01-03delivered
21instamart2025-01-10delivered
32instamart2025-01-02delivered
42food2025-01-05delivered
53food2025-01-04cancelled
63food2025-01-08delivered
73instamart2025-01-08delivered
84food2025-01-01delivered
94genie2025-01-02delivered
104instamart2025-02-01cancelled
2 more rows

Output

customer_idfirst_food_datefirst_instamart_datedays_to_instamart
12025-01-032025-01-107
42025-01-012025-02-1545
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: 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

ColumnType
order_idint
customer_idint
businessenum(food, instamart, genie)
order_datedate
statusenum(delivered, cancelled)

Primary key: order_id.

One row per order across the app's businesses.

Examples

Example 1

SwiggyOrder

order_idcustomer_idbusinessorder_datestatus
11food2025-01-03delivered
21instamart2025-01-10delivered
32instamart2025-01-02delivered
42food2025-01-05delivered
53food2025-01-04cancelled
63food2025-01-08delivered
73instamart2025-01-08delivered
84food2025-01-01delivered
94genie2025-01-02delivered
104instamart2025-02-01cancelled
114instamart2025-02-15delivered
125food2025-01-06delivered

Output

customer_idfirst_food_datefirst_instamart_datedays_to_instamart
12025-01-032025-01-107
42025-01-012025-02-1545

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_date

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