Zomato: Gold Members Yet to Use a Single Benefit
Zomato Gold gives members free delivery and dining discounts.
- Difficulty: Easy
- Topics: Joins, Subqueries, Dates
- Dialect: MySQL
- Problem: #1025
Problem statement
Zomato Gold gives members free delivery and dining discounts. The membership team is planning a nudge for members who are paying for Gold but have never used it.
Return the membership_id and member_name of every membership that is active on 2024-06-30 (its start_date is on or before that day and its end_date on or after it) and has no redemption at all in BenefitRedemption. Return the rows in any order.
A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.
Tables
Table: GoldMembership
| Column | Type |
|---|---|
| membership_id | int |
| user_id | int |
| member_name | varchar |
| plan | enum(quarterly, annual) |
| start_date | date |
| end_date | date |
Primary key: membership_id.
One row per Gold membership bought; both dates are inclusive.
Table: BenefitRedemption
| Column | Type |
|---|---|
| redemption_id | int |
| membership_id | int |
| redeemed_on | date |
| benefit | enum(free_delivery, dining_discount) |
Primary key: redemption_id.
One row each time a member used a Gold benefit; membership_id always names a membership.
Examples
Example 1
GoldMembership
| membership_id | user_id | member_name | plan | start_date | end_date |
|---|---|---|---|---|---|
| 1 | 101 | Aarav | annual | 2024-01-10 | 2025-01-09 |
| 2 | 102 | Diya | quarterly | 2024-04-01 | 2024-06-30 |
| 3 | 103 | Kabir | quarterly | 2024-06-30 | 2024-09-29 |
| 4 | 104 | Meera | annual | 2023-05-01 | 2024-04-30 |
| 5 | 105 | Rohan | annual | 2024-02-15 | 2025-02-14 |
| 6 | 106 | Zara | quarterly | 2024-07-01 | 2024-09-30 |
BenefitRedemption
| redemption_id | membership_id | redeemed_on | benefit |
|---|---|---|---|
| 1 | 1 | 2024-03-02 | free_delivery |
| 2 | 5 | 2024-05-20 | dining_discount |
| 3 | 5 | 2024-06-01 | free_delivery |
| 4 | 4 | 2024-03-01 | free_delivery |
Output
| membership_id | member_name |
|---|---|
| 2 | Diya |
| 3 | Kabir |
How to solve Zomato: Gold Members Yet to Use a Single Benefit
Two tests decide each membership. The first is a date range: a membership is active on 2024-06-30 when start_date <= '2024-06-30' and end_date >= '2024-06-30'. Both comparisons are inclusive, so a membership that starts or ends on that exact day counts, while one starting on 1 July does not.
The second test is an anti join — there must be no row in BenefitRedemption for the membership. NOT EXISTS with a correlated subquery says exactly that and stops at the first redemption it finds. Redemptions are tied to the membership, not to the person, so a member's earlier, expired membership with plenty of use does not rescue the current one.
The LEFT JOIN … WHERE b.redemption_id IS NULL version keeps the memberships whose join found nothing. NOT IN is safe here because BenefitRedemption.membership_id is never NULL; with a NULL in that list, NOT IN would return nothing at all, which is why NOT EXISTS is the habit to keep. With an index on BenefitRedemption(membership_id) each probe is a single lookup.
Reference solution (MySQL)
SELECT g.membership_id, g.member_name
FROM GoldMembership g
WHERE g.start_date <= '2024-06-30'
AND g.end_date >= '2024-06-30'
AND NOT EXISTS (SELECT 1 FROM BenefitRedemption b WHERE b.membership_id = g.membership_id)Another way
SELECT g.membership_id, g.member_name
FROM GoldMembership g
LEFT JOIN BenefitRedemption b ON b.membership_id = g.membership_id
WHERE '2024-06-30' BETWEEN g.start_date AND g.end_date
AND b.redemption_id IS NULLAnother way
SELECT membership_id, member_name FROM GoldMembership WHERE start_date <= '2024-06-30' AND end_date >= '2024-06-30' AND membership_id NOT IN (SELECT membership_id FROM BenefitRedemption)← Zomato: Veg, Non-Veg and Egg Dishes on Every Menu · Zomato: Build the URL Slug of Every Restaurant Page →