Swiggy: Swiggy One Plans That Paid for Themselves
A Swiggy One member gets free delivery, and each order records the delivery fee that was waived.
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #958
Problem statement
A Swiggy One member gets free delivery, and each order records the delivery fee that was waived. The membership team calls a plan paid off when the fees waived during it reach the price of the plan.
For every membership whose customer's delivered orders dated from start_date to end_date (both included) waived at least the plan's plan_fee in total, return membership_id, customer_id, plan_fee and fees_waived. Cancelled orders waive nothing. Order the rows by membership_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: OneMembership
| Column | Type |
|---|---|
| membership_id | int |
| customer_id | int |
| start_date | date |
| end_date | date |
| plan_fee | int |
Primary key: membership_id.
One row per plan bought; a customer's plans never overlap. plan_fee is what the customer paid, in rupees.
Table: FoodOrder
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| order_date | date |
| status | enum(delivered, cancelled) |
| fee_waived | int |
Primary key: order_id.
One row per food order. fee_waived is the delivery fee the membership saved on it, in rupees.
Examples
Example 1
OneMembership
| membership_id | customer_id | start_date | end_date | plan_fee |
|---|---|---|---|---|
| 1 | 101 | 2025-01-01 | 2025-03-31 | 299 |
| 2 | 102 | 2025-01-01 | 2025-01-31 | 149 |
| 3 | 103 | 2025-02-01 | 2025-04-30 | 299 |
| 4 | 101 | 2025-04-01 | 2025-06-30 | 299 |
FoodOrder
| order_id | customer_id | order_date | status | fee_waived |
|---|---|---|---|---|
| 1 | 101 | 2025-01-05 | delivered | 120 |
| 2 | 101 | 2025-02-11 | delivered | 99 |
| 3 | 101 | 2025-03-31 | delivered | 80 |
| 4 | 101 | 2025-03-15 | cancelled | 70 |
| 5 | 101 | 2025-04-20 | delivered | 45 |
| 6 | 102 | 2025-01-05 | delivered | 49 |
| 7 | 102 | 2025-01-20 | delivered | 59 |
| 8 | 102 | 2025-01-31 | delivered | 45 |
| 9 | 102 | 2025-02-01 | delivered | 50 |
Output
| membership_id | customer_id | plan_fee | fees_waived |
|---|---|---|---|
| 1 | 101 | 299 | 299 |
| 2 | 102 | 149 | 153 |
How to solve Swiggy: Swiggy One Plans That Paid for Themselves
An order is matched to a membership by a range join: same customer, and the order date between the plan's start and end. Adding status = 'delivered' to the same ON clause keeps cancelled orders out. After the join every membership row is repeated once per qualifying order, so grouping by membership and summing fee_waived gives the savings of that plan, and HAVING SUM(fee_waived) >= plan_fee keeps the plans that paid off — equality counts, as the statement says "at least".
The plans of one customer never overlap, so no order can be counted towards two memberships. A membership with no delivered orders forms no group at all under the inner join; it could never have paid off anyway, since every plan costs something.
The alternative computes the savings per membership with a correlated scalar subquery (with COALESCE so an unused plan shows 0 rather than NULL) and filters in an outer query. It is easier to read as "for each plan, add up its savings" and with an index on FoodOrder(customer_id, order_date) each lookup is a short range scan; the join-and-group version does the same work in one pass.
Reference solution (MySQL)
SELECT m.membership_id, m.customer_id, m.plan_fee, SUM(o.fee_waived) AS fees_waived
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
GROUP BY m.membership_id, m.customer_id, m.plan_fee
HAVING SUM(o.fee_waived) >= m.plan_fee
ORDER BY m.membership_idAnother way
SELECT membership_id, customer_id, plan_fee, fees_waived FROM (
SELECT m.membership_id, m.customer_id, m.plan_fee,
(SELECT COALESCE(SUM(o.fee_waived), 0) FROM FoodOrder o
WHERE o.customer_id = m.customer_id AND o.status = 'delivered'
AND o.order_date >= m.start_date AND o.order_date <= m.end_date) AS fees_waived
FROM OneMembership m
) t
WHERE fees_waived >= plan_fee
ORDER BY membership_id← Swiggy: Instamart SKUs Out of Stock Across a Whole City · Swiggy: Orders That Stacked Two Platform Coupons →