Walmart: Walmart+ Membership Spells, Bridging Short Lapses

Hard
1017.

Walmart: Walmart+ Membership Spells, Bridging Short Lapses

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

ColumnType
term_idint
member_idint
start_datedate
end_datedate

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.

Example

The rows must come back in the order shown.

PlusTerm

term_idmember_idstart_dateend_date
710012023-01-012023-01-31
310012023-02-012023-02-28
910012023-03-312023-04-29
110012023-05-312023-06-29
510022023-03-102024-03-09
210022024-05-012025-04-30
410032024-01-152024-02-14

Output

member_idspell_startspell_endterms
10012023-01-012023-04-293
10012023-05-312023-06-291
10022023-03-102024-03-091
10022024-05-012025-04-301
10032024-01-152024-02-141
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.

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

ColumnType
term_idint
member_idint
start_datedate
end_datedate

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_idmember_idstart_dateend_date
710012023-01-012023-01-31
310012023-02-012023-02-28
910012023-03-312023-04-29
110012023-05-312023-06-29
510022023-03-102024-03-09
210022024-05-012025-04-30
410032024-01-152024-02-14

Output

member_idspell_startspell_endterms
10012023-01-012023-04-293
10012023-05-312023-06-291
10022023-03-102024-03-091
10022024-05-012025-04-301
10032024-01-152024-02-141

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_start

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