Swiggy: Swiggy One Plans With No Order Placed

Easy
952.

Swiggy: Swiggy One Plans With No Order Placed

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

ColumnType
membership_idint
customer_idint
start_datedate
end_datedate

Primary key: membership_id.

One row per plan bought. A customer can buy several plans over time; they never overlap.

Table: FoodOrder

ColumnTypeValues
order_idint
customer_idint
order_datedate
statusenumdelivered, cancelled

Primary key: order_id.

One row per food order, by members and non-members alike.

Example

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

OneMembership

membership_idcustomer_idstart_dateend_date
11012025-01-012025-03-31
21022025-01-152025-02-14
31012025-04-012025-04-30
41032025-02-012025-04-30
51042025-03-012025-03-31

FoodOrder

order_idcustomer_idorder_datestatus
90011012025-03-31delivered
90021022025-02-20delivered
90031032025-03-10cancelled
90041042025-03-01delivered
90051012025-05-02delivered
90061052025-03-05delivered

Output

membership_idcustomer_id
2102
3101
4103
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 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

ColumnType
membership_idint
customer_idint
start_datedate
end_datedate

Primary key: membership_id.

One row per plan bought. A customer can buy several plans over time; they never overlap.

Table: FoodOrder

ColumnType
order_idint
customer_idint
order_datedate
statusenum(delivered, cancelled)

Primary key: order_id.

One row per food order, by members and non-members alike.

Examples

Example 1

OneMembership

membership_idcustomer_idstart_dateend_date
11012025-01-012025-03-31
21022025-01-152025-02-14
31012025-04-012025-04-30
41032025-02-012025-04-30
51042025-03-012025-03-31

FoodOrder

order_idcustomer_idorder_datestatus
90011012025-03-31delivered
90021022025-02-20delivered
90031032025-03-10cancelled
90041042025-03-01delivered
90051012025-05-02delivered
90061052025-03-05delivered

Output

membership_idcustomer_id
2102
3101
4103

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 NULL

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