Microsoft: Game Pass Win-Back Rate by Cancellation Month
The Xbox Game Pass retention team measures win-backs: members who let their membership lapse and came back within 60 days.
- Difficulty: Hard
- Topics: Window Functions, Dates, Aggregation
- Dialect: MySQL
- Problem: #797
Problem statement
The Xbox Game Pass retention team measures win-backs: members who let their membership lapse and came back within 60 days.
Each member's periods never overlap. When a period ends, look at the member's next period: if it starts 0 or 1 day after the end, the member just switched tier — not a lapse. Otherwise the end is a lapse, and it is won back when the next period starts 2 to 60 days after the end (inclusive). An ended period with no next period is a lapse that was not won back. For each month of the lapse's end date ('YYYY-MM'), return lapse_month, lapses, won_back and win_back_pct (100 × won_back ÷ lapses, rounded to 2 decimals), ordered by lapse_month. Periods still running (ended_on NULL) are not lapses.
A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.
Tables
Table: GamePassPeriod
| Column | Type |
|---|---|
| period_id | int |
| member_id | int |
| tier | enum(Core, Standard, Ultimate, PC) |
| started_on | date |
| ended_on | date |
Primary key: period_id.
One row per continuous membership period on one tier; ended_on is NULL for a period still running. A member's periods never overlap, and a new one starts on or after the previous end.
Examples
Example 1
GamePassPeriod
| period_id | member_id | tier | started_on | ended_on |
|---|---|---|---|---|
| 1 | 1 | Core | 2024-01-05 | 2024-03-04 |
| 2 | 1 | Ultimate | 2024-03-05 | 2024-05-10 |
| 3 | 1 | Ultimate | 2024-06-20 | NULL |
| 4 | 2 | Standard | 2024-02-01 | 2024-05-31 |
| 5 | 3 | PC | 2024-01-10 | 2024-03-15 |
| 6 | 3 | PC | 2024-05-14 | 2024-07-01 |
| 7 | 4 | Core | 2024-01-01 | 2024-03-20 |
| 8 | 4 | Core | 2024-05-20 | NULL |
| 9 | 5 | Standard | 2024-04-01 | 2024-05-02 |
| 10 | 5 | Ultimate | 2024-05-02 | 2024-05-25 |
| 11 | 5 | Ultimate | 2024-06-01 | NULL |
Output
| lapse_month | lapses | won_back | win_back_pct |
|---|---|---|---|
| 2024-03 | 2 | 1 | 50 |
| 2024-05 | 3 | 2 | 66.67 |
| 2024-07 | 1 | 0 | 0 |
How to solve Microsoft: Game Pass Win-Back Rate by Cancellation Month
Everything hinges on pairing each period with the next one of the same member, which LEAD(started_on) OVER (PARTITION BY member_id ORDER BY started_on) does in one pass. Because periods never overlap and start in order, the next start is the only one that can tell us what happened after an end.
The gap DATEDIFF(next_start, ended_on) then classifies the end. A gap of 0 or 1 day is a tier switch (member 5 moved from Standard to Ultimate on the same day) — removed entirely, because counting it as a lapse would inflate both numerator and denominator. A gap of 2–60 days is a lapse that was won back (member 3 returned after exactly 60 days, inside the window); more than 60, or no next period at all, is a lapse that stayed lost (member 4 came back on day 61). Periods still running are not lapses. Each lapse is bucketed by the month it ended and the rate is 100 * won / lapses, rounded.
The alternative replaces LEAD with EXISTS probes over date windows: no period starting within a day of the end (not a switch), and one starting 2–60 days after (won back).
Reference solution (MySQL)
WITH with_next AS (
SELECT member_id, ended_on,
LEAD(started_on) OVER (PARTITION BY member_id ORDER BY started_on) AS next_start
FROM GamePassPeriod
), lapses AS (
SELECT DATE_FORMAT(ended_on, '%Y-%m') AS lapse_month,
CASE WHEN next_start IS NOT NULL AND DATEDIFF(next_start, ended_on) <= 60 THEN 1 ELSE 0 END AS won
FROM with_next
WHERE ended_on IS NOT NULL
AND (next_start IS NULL OR DATEDIFF(next_start, ended_on) >= 2)
)
SELECT lapse_month, COUNT(*) AS lapses, SUM(won) AS won_back,
ROUND(100 * SUM(won) / COUNT(*), 2) AS win_back_pct
FROM lapses
GROUP BY lapse_month
ORDER BY lapse_monthAnother way
SELECT LEFT(p.ended_on, 7) AS lapse_month, COUNT(*) AS lapses,
SUM(CASE WHEN EXISTS (SELECT 1 FROM GamePassPeriod n WHERE n.member_id = p.member_id
AND n.started_on BETWEEN DATE_ADD(p.ended_on, INTERVAL 2 DAY) AND DATE_ADD(p.ended_on, INTERVAL 60 DAY))
THEN 1 ELSE 0 END) AS won_back,
ROUND(100 * SUM(CASE WHEN EXISTS (SELECT 1 FROM GamePassPeriod n WHERE n.member_id = p.member_id
AND n.started_on BETWEEN DATE_ADD(p.ended_on, INTERVAL 2 DAY) AND DATE_ADD(p.ended_on, INTERVAL 60 DAY))
THEN 1 ELSE 0 END) / COUNT(*), 2) AS win_back_pct
FROM GamePassPeriod p
WHERE p.ended_on IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM GamePassPeriod s WHERE s.member_id = p.member_id
AND s.started_on BETWEEN p.ended_on AND DATE_ADD(p.ended_on, INTERVAL 1 DAY))
GROUP BY lapse_month
ORDER BY lapse_month← Microsoft: Bing Search Sessions That Ended Without a Click · Myntra: Orders Placed in the EORS Insider Early-Access Hours →