Walmart: Walmart+ Membership Spells, Bridging Short Lapses
The Walmart+ retention team counts a member who lapses briefly and comes back as one continuous relationship.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #1017
Problem statement
The Walmart+ retention team counts a member who lapses briefly and comes back as one continuous relationship. It wants each member's history as spells: runs of membership terms where every lapse between consecutive terms was short.
A member's terms never overlap. Taking a member's terms in start_date order, a term continues the previous term's spell when the days with no membership in between — DATEDIFF(start_date, previous end_date) - 1 — are 30 or fewer; otherwise it starts a new spell. Return member_id, spell_start (first term's start_date), spell_end (last term's end_date) and terms (how many terms the spell holds), ordered by member_id, then spell_start.
A practice scenario set at Walmart: the tables and data are invented, and this is not a question from, or endorsed by, Walmart.
Tables
Table: PlusTerm
| Column | Type |
|---|---|
| term_id | int |
| member_id | int |
| start_date | date |
| end_date | date |
Primary key: term_id.
One row per paid term (a month or a year). Both dates are inclusive; a member's terms never overlap. Term ids are not in date order.
Examples
Example 1
PlusTerm
| term_id | member_id | start_date | end_date |
|---|---|---|---|
| 7 | 1001 | 2023-01-01 | 2023-01-31 |
| 3 | 1001 | 2023-02-01 | 2023-02-28 |
| 9 | 1001 | 2023-03-31 | 2023-04-29 |
| 1 | 1001 | 2023-05-31 | 2023-06-29 |
| 5 | 1002 | 2023-03-10 | 2024-03-09 |
| 2 | 1002 | 2024-05-01 | 2025-04-30 |
| 4 | 1003 | 2024-01-15 | 2024-02-14 |
Output
| member_id | spell_start | spell_end | terms |
|---|---|---|---|
| 1001 | 2023-01-01 | 2023-04-29 | 3 |
| 1001 | 2023-05-31 | 2023-06-29 | 1 |
| 1002 | 2023-03-10 | 2024-03-09 | 1 |
| 1002 | 2024-05-01 | 2025-04-30 | 1 |
| 1003 | 2024-01-15 | 2024-02-14 | 1 |
How to solve Walmart: Walmart+ Membership Spells, Bridging Short Lapses
Spells are islands of terms, but the islands are not separated by any gap at all — only by a gap longer than 30 days — so the usual "value minus row number" trick does not apply. The general method does: mark where each island starts, then number the islands with a running total.
LAG(end_date) over each member's terms, ordered by start_date, puts the previous term's end on every row (the term ids are deliberately out of date order, so ordering by id would be wrong). A term with no previous term, or with more than 30 lapsed days — DATEDIFF(start_date, prev_end) - 1 > 30 — starts a spell and gets flag 1; every other term gets 0. Back-to-back terms have a lapse of 0 days; a lapse of exactly 30 days is bridged and 31 is not.
SUM(is_new) OVER (… ROWS UNBOUNDED PRECEDING) turns the flags into spell numbers 1, 1, 1, 2, …, and grouping by member and spell number gives each spell's first start, last end and term count.
The alternative finds spell starts with NOT EXISTS (no earlier term ended within 31 days), then assigns every term to the latest spell start at or before it, using the next spell's start as the upper bound. That works because terms do not overlap; the window version is a single sort per member.
Reference solution (MySQL)
WITH t AS (
SELECT member_id, start_date, end_date,
LAG(end_date) OVER (PARTITION BY member_id ORDER BY start_date) AS prev_end
FROM PlusTerm
), f AS (
SELECT member_id, start_date, end_date,
CASE WHEN prev_end IS NOT NULL AND DATEDIFF(start_date, prev_end) - 1 <= 30 THEN 0 ELSE 1 END AS is_new
FROM t
), g AS (
SELECT member_id, start_date, end_date,
SUM(is_new) OVER (PARTITION BY member_id ORDER BY start_date ROWS UNBOUNDED PRECEDING) AS spell
FROM f
)
SELECT member_id, MIN(start_date) AS spell_start, MAX(end_date) AS spell_end, COUNT(*) AS terms
FROM g
GROUP BY member_id, spell
ORDER BY member_id, spell_startAnother way
WITH s AS (
SELECT a.member_id, a.start_date
FROM PlusTerm a
WHERE NOT EXISTS (
SELECT 1 FROM PlusTerm b
WHERE b.member_id = a.member_id AND b.end_date < a.start_date
AND DATEDIFF(a.start_date, b.end_date) <= 31
)
), b AS (
SELECT s.member_id, s.start_date AS spell_start,
(SELECT MIN(s2.start_date) FROM s s2 WHERE s2.member_id = s.member_id AND s2.start_date > s.start_date) AS next_start
FROM s
)
SELECT b.member_id, b.spell_start, MAX(p.end_date) AS spell_end, COUNT(*) AS terms
FROM b
JOIN PlusTerm p ON p.member_id = b.member_id AND p.start_date >= b.spell_start
AND (b.next_start IS NULL OR p.start_date < b.next_start)
GROUP BY b.member_id, b.spell_start
ORDER BY b.member_id, b.spell_start← Walmart: Did the Rollback Lift Daily Unit Sales? · Walmart: Truck Routes Rebuilt From Store Geofence Pings →