Swiggy: Swiggy One Plans That Paid for Themselves

Medium
958.

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. 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

ColumnType
membership_idint
customer_idint
start_datedate
end_datedate
plan_feeint

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

ColumnTypeValues
order_idint
customer_idint
order_datedate
statusenumdelivered, cancelled
fee_waivedint

Primary key: order_id.

One row per food order. fee_waived is the delivery fee the membership saved on it, in rupees.

Example

The rows must come back in the order shown.

OneMembership

membership_idcustomer_idstart_dateend_dateplan_fee
11012025-01-012025-03-31299
21022025-01-012025-01-31149
31032025-02-012025-04-30299
41012025-04-012025-06-30299

FoodOrder

order_idcustomer_idorder_datestatusfee_waived
11012025-01-05delivered120
21012025-02-11delivered99
31012025-03-31delivered80
41012025-03-15cancelled70
51012025-04-20delivered45
61022025-01-05delivered49
71022025-01-20delivered59
81022025-01-31delivered45
91022025-02-01delivered50

Output

membership_idcustomer_idplan_feefees_waived
1101299299
2102149153
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: 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

ColumnType
membership_idint
customer_idint
start_datedate
end_datedate
plan_feeint

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

ColumnType
order_idint
customer_idint
order_datedate
statusenum(delivered, cancelled)
fee_waivedint

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_idcustomer_idstart_dateend_dateplan_fee
11012025-01-012025-03-31299
21022025-01-012025-01-31149
31032025-02-012025-04-30299
41012025-04-012025-06-30299

FoodOrder

order_idcustomer_idorder_datestatusfee_waived
11012025-01-05delivered120
21012025-02-11delivered99
31012025-03-31delivered80
41012025-03-15cancelled70
51012025-04-20delivered45
61022025-01-05delivered49
71022025-01-20delivered59
81022025-01-31delivered45
91022025-02-01delivered50

Output

membership_idcustomer_idplan_feefees_waived
1101299299
2102149153

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_id

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