Microsoft: Game Pass Win-Back Rate by Cancellation Month

Hard
797.

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.

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

ColumnTypeValues
period_idint
member_idint
tierenumCore, Standard, Ultimate, PC
started_ondate
ended_ondate

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.

Example

The rows must come back in the order shown.

GamePassPeriod

period_idmember_idtierstarted_onended_on
11Core2024-01-052024-03-04
21Ultimate2024-03-052024-05-10
31Ultimate2024-06-20NULL
42Standard2024-02-012024-05-31
53PC2024-01-102024-03-15
63PC2024-05-142024-07-01
74Core2024-01-012024-03-20
84Core2024-05-20NULL
95Standard2024-04-012024-05-02
105Ultimate2024-05-022024-05-25
1 more row

Output

lapse_monthlapseswon_backwin_back_pct
2024-032150
2024-053266.67
2024-07100
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.

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

ColumnType
period_idint
member_idint
tierenum(Core, Standard, Ultimate, PC)
started_ondate
ended_ondate

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_idmember_idtierstarted_onended_on
11Core2024-01-052024-03-04
21Ultimate2024-03-052024-05-10
31Ultimate2024-06-20NULL
42Standard2024-02-012024-05-31
53PC2024-01-102024-03-15
63PC2024-05-142024-07-01
74Core2024-01-012024-03-20
84Core2024-05-20NULL
95Standard2024-04-012024-05-02
105Ultimate2024-05-022024-05-25
115Ultimate2024-06-01NULL

Output

lapse_monthlapseswon_backwin_back_pct
2024-032150
2024-053266.67
2024-07100

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_month

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