Zomato: Gold Membership Renewal Rate by Plan

Medium
1031.

Zomato: Gold Membership Renewal Rate by Plan

The Zomato Gold team tracks how many members renew when a membership runs out, separately for the quarterly and the annual plan.

A membership is eligible when its end_date is on or before 2024-06-30. It is renewed when the same user has another membership whose start_date is after this one's start_date and no later than 7 days after this one's end_date (an early renewal that overlaps counts). For each plan with at least one eligible membership, return plan, ended (eligible memberships), renewed (how many of them were renewed — each counted once, however many follow-ups it has) and renewal_rate_pct = 100 × renewed / ended rounded to 2 decimals. The plan is that of the ending membership. 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
planenumquarterly, annual
start_datedate
end_datedate

Primary key: membership_id.

One row per membership bought; both dates inclusive. A user may hold several over time.

Example

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

GoldMembership

membership_iduser_idplanstart_dateend_date
11quarterly2024-01-012024-03-31
21quarterly2024-04-052024-07-04
32annual2023-03-012024-02-29
42quarterly2024-03-082024-06-07
53quarterly2024-02-012024-04-30
63annual2024-05-072025-05-06
74annual2023-06-012024-05-31
85quarterly2024-01-152024-04-14
95quarterly2024-03-202024-06-19

Output

planendedrenewedrenewal_rate_pct
annual200
quarterly5360
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 Membership Renewal Rate by Plan — SQL Problem

The Zomato Gold team tracks how many members renew when a membership runs out, separately for the quarterly and the annual plan.

  • Difficulty: Medium
  • Topics: Joins, Aggregation, Conditional Logic, Dates
  • Dialect: MySQL
  • Problem: #1031

Problem statement

The Zomato Gold team tracks how many members renew when a membership runs out, separately for the quarterly and the annual plan.

A membership is eligible when its end_date is on or before 2024-06-30. It is renewed when the same user has another membership whose start_date is after this one's start_date and no later than 7 days after this one's end_date (an early renewal that overlaps counts). For each plan with at least one eligible membership, return plan, ended (eligible memberships), renewed (how many of them were renewed — each counted once, however many follow-ups it has) and renewal_rate_pct = 100 × renewed / ended rounded to 2 decimals. The plan is that of the ending membership. 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
planenum(quarterly, annual)
start_datedate
end_datedate

Primary key: membership_id.

One row per membership bought; both dates inclusive. A user may hold several over time.

Examples

Example 1

GoldMembership

membership_iduser_idplanstart_dateend_date
11quarterly2024-01-012024-03-31
21quarterly2024-04-052024-07-04
32annual2023-03-012024-02-29
42quarterly2024-03-082024-06-07
53quarterly2024-02-012024-04-30
63annual2024-05-072025-05-06
74annual2023-06-012024-05-31
85quarterly2024-01-152024-04-14
95quarterly2024-03-202024-06-19

Output

planendedrenewedrenewal_rate_pct
annual200
quarterly5360

How to solve Zomato: Gold Membership Renewal Rate by Plan

The question has two levels: a yes/no for each eligible membership, then a count of yeses per plan. Keeping those levels apart is the whole difficulty.

A membership is eligible when it has ended by 30 June (end_date <= '2024-06-30'), so its outcome is known. It is renewed when some other membership of the same user starts after it began and no later than seven days after it ended. EXISTS asks precisely "is there at least one such row", so a membership with two qualifying follow-ups still counts once. Requiring the follow-up to start after g.start_date excludes the membership itself and lets an early, overlapping renewal count.

SUM(CASE WHEN EXISTS (…) THEN 1 ELSE 0 END) turns the flags into the renewed count, COUNT(*) is the eligible count, and the rate is their ratio × 100, rounded. Plans with no eligible membership form no group and are left out.

The alternative self-joins with LEFT JOIN and collapses the matches with MAX(…) per membership before aggregating by plan — the explicit version of the de-duplication EXISTS does for free. A naive COUNT(n.membership_id) without that step would over-count. With an index on (user_id, start_date) each check is a short range scan.

Reference solution (MySQL)

SELECT g.plan,
       COUNT(*) AS ended,
       SUM(CASE WHEN EXISTS (
             SELECT 1 FROM GoldMembership n
             WHERE n.user_id = g.user_id
               AND n.start_date > g.start_date
               AND n.start_date <= DATE_ADD(g.end_date, INTERVAL 7 DAY)
           ) THEN 1 ELSE 0 END) AS renewed,
       ROUND(100 * SUM(CASE WHEN EXISTS (
             SELECT 1 FROM GoldMembership n
             WHERE n.user_id = g.user_id
               AND n.start_date > g.start_date
               AND n.start_date <= DATE_ADD(g.end_date, INTERVAL 7 DAY)
           ) THEN 1 ELSE 0 END) / COUNT(*), 2) AS renewal_rate_pct
FROM GoldMembership g
WHERE g.end_date <= '2024-06-30'
GROUP BY g.plan

Another way

WITH flagged AS (
  SELECT g.membership_id, g.plan,
         MAX(CASE WHEN n.membership_id IS NULL THEN 0 ELSE 1 END) AS is_renewed
  FROM GoldMembership g
  LEFT JOIN GoldMembership n
    ON n.user_id = g.user_id
   AND n.start_date > g.start_date
   AND n.start_date <= DATE_ADD(g.end_date, INTERVAL 7 DAY)
  WHERE g.end_date <= '2024-06-30'
  GROUP BY g.membership_id, g.plan
)
SELECT plan, COUNT(*) AS ended, SUM(is_renewed) AS renewed,
       ROUND(100 * SUM(is_renewed) / COUNT(*), 2) AS renewal_rate_pct
FROM flagged
GROUP BY plan

← Zomato: Hyperpure Invoices Paid Late or Still Overdue · Zomato: Cost per Order of Each Promoted Listing →