Swiggy: Swiggy One Plans With No Order Placed — SQL Problem
The Swiggy One membership team wants to reach members who paid for a plan and then never used it — no delivered food order at any point while the plan was…
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #952
Problem statement
The Swiggy One membership team wants to reach members who paid for a plan and then never used it — no delivered food order at any point while the plan was active.
Return membership_id and customer_id for every membership during which its customer had no delivered order with order_date between the plan's start_date and end_date, both days included. Cancelled orders do not count as use. 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: OneMembership
| Column | Type |
|---|---|
| membership_id | int |
| customer_id | int |
| start_date | date |
| end_date | date |
Primary key: membership_id.
One row per plan bought. A customer can buy several plans over time; they never overlap.
Table: FoodOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| order_date | date |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per food order, by members and non-members alike.
Examples
Example 1
OneMembership
| membership_id | customer_id | start_date | end_date |
|---|---|---|---|
| 1 | 101 | 2025-01-01 | 2025-03-31 |
| 2 | 102 | 2025-01-15 | 2025-02-14 |
| 3 | 101 | 2025-04-01 | 2025-04-30 |
| 4 | 103 | 2025-02-01 | 2025-04-30 |
| 5 | 104 | 2025-03-01 | 2025-03-31 |
FoodOrder
| order_id | customer_id | order_date | status |
|---|---|---|---|
| 9001 | 101 | 2025-03-31 | delivered |
| 9002 | 102 | 2025-02-20 | delivered |
| 9003 | 103 | 2025-03-10 | cancelled |
| 9004 | 104 | 2025-03-01 | delivered |
| 9005 | 101 | 2025-05-02 | delivered |
| 9006 | 105 | 2025-03-05 | delivered |
Output
| membership_id | customer_id |
|---|---|
| 2 | 102 |
| 3 | 101 |
| 4 | 103 |
How to solve Swiggy: Swiggy One Plans With No Order Placed
The question is about absence: a membership qualifies when no order of a certain kind exists for it. NOT EXISTS states that directly. For each membership the subquery looks for a delivered order by the same customer whose date falls between start_date and end_date; BETWEEN is inclusive, so an order on the plan's first or last day counts as use, and an order one day outside does not.
The LEFT JOIN … IS NULL alternative is the same idea in join form. Every condition that defines a match — status and the date window — must be inside ON. Put the status test in WHERE instead and the NULL-extended rows of unused plans would be thrown away, emptying the answer. The NOT IN version first collects the memberships that were used and keeps the rest; it is safe here because membership_id is never NULL.
Cancelled orders and orders by customers who never joined are simply never matched. With an index on FoodOrder(customer_id, order_date) each membership is checked with a short range lookup.
Reference solution (MySQL)
SELECT m.membership_id, m.customer_id
FROM OneMembership m
WHERE NOT EXISTS (
SELECT 1 FROM FoodOrder o
WHERE o.customer_id = m.customer_id
AND o.status = 'delivered'
AND o.order_date BETWEEN m.start_date AND m.end_date
)Another way
SELECT m.membership_id, m.customer_id
FROM OneMembership m
LEFT JOIN FoodOrder o ON o.customer_id = m.customer_id AND o.status = 'delivered'
AND o.order_date >= m.start_date AND o.order_date <= m.end_date
WHERE o.order_id IS NULLAnother way
SELECT membership_id, customer_id FROM OneMembership
WHERE membership_id NOT IN (
SELECT m.membership_id FROM OneMembership m JOIN FoodOrder o
ON o.customer_id = m.customer_id AND o.status = 'delivered' AND o.order_date BETWEEN m.start_date AND m.end_date
)← Swiggy: Saved Addresses Without a Valid Pincode · Swiggy: Packaging Charges Above the Per-Item Cap →