Zomato: Gold Members Yet to Use a Single Benefit

Easy
1025.

Zomato: Gold Members Yet to Use a Single Benefit

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

ColumnTypeValues
membership_idint
user_idint
member_namevarchar
planenumquarterly, annual
start_datedate
end_datedate

Primary key: membership_id.

One row per Gold membership bought; both dates are inclusive.

Table: BenefitRedemption

ColumnTypeValues
redemption_idint
membership_idint
redeemed_ondate
benefitenumfree_delivery, dining_discount

Primary key: redemption_id.

One row each time a member used a Gold benefit; membership_id always names a membership.

Example

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

GoldMembership

membership_iduser_idmember_nameplanstart_dateend_date
1101Aaravannual2024-01-102025-01-09
2102Diyaquarterly2024-04-012024-06-30
3103Kabirquarterly2024-06-302024-09-29
4104Meeraannual2023-05-012024-04-30
5105Rohanannual2024-02-152025-02-14
6106Zaraquarterly2024-07-012024-09-30

BenefitRedemption

redemption_idmembership_idredeemed_onbenefit
112024-03-02free_delivery
252024-05-20dining_discount
352024-06-01free_delivery
442024-03-01free_delivery

Output

membership_idmember_name
2Diya
3Kabir
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.

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

ColumnType
membership_idint
user_idint
member_namevarchar
planenum(quarterly, annual)
start_datedate
end_datedate

Primary key: membership_id.

One row per Gold membership bought; both dates are inclusive.

Table: BenefitRedemption

ColumnType
redemption_idint
membership_idint
redeemed_ondate
benefitenum(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_iduser_idmember_nameplanstart_dateend_date
1101Aaravannual2024-01-102025-01-09
2102Diyaquarterly2024-04-012024-06-30
3103Kabirquarterly2024-06-302024-09-29
4104Meeraannual2023-05-012024-04-30
5105Rohanannual2024-02-152025-02-14
6106Zaraquarterly2024-07-012024-09-30

BenefitRedemption

redemption_idmembership_idredeemed_onbenefit
112024-03-02free_delivery
252024-05-20dining_discount
352024-06-01free_delivery
442024-03-01free_delivery

Output

membership_idmember_name
2Diya
3Kabir

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 NULL

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