Spotify mid LAGPARTITION BYCOALESCEWindow FunctionsCASE WHENCTE

For each user and month in listening_stats, use LAG() to find that same user's previous month minutes_streamed (ordered by month). Treat a NULL minutes_streamed as 0 when comparing. Flag a row as churn_risk = 1 if minutes dropped by more than 50% compared to the previous month, otherwise churn_risk = 0. A user's first month in the data has no previous month to compare against, so churn_risk should be 0 for that row. Return user_name, month, minutes_streamed, previous_month_minutes, and churn_risk, sorted by user_name and month.

Spotify · Listener Churn Signals — practice this real-world SQL scenario live in your browser.

📖 Story Spotify · Listener Churn Signals

Wednesday morning at Spotify's Listener Insights team. The product team wants an early-warning signal for churn — users whose monthly listening time drops sharply before they cancel their subscription. The listening_stats table logs total minutes streamed per user per month, but some months have no value logged at all, because the app was never opened that month — a different situation from a user who opened it and streamed nothing. Before churn_risk can be flagged reliably, each user's current month needs to be compared against their own previous month, with missing months treated as zero minutes.

🎯 Your Mission

📋 Table Structure

🗂 listening_stats

id INTEGER 1

user_id INTEGER 101

user_name TEXT Aarav

month TEXT 2026-03

minutes_streamed INTEGER 1200

⚡ Step-by-Step Walkthrough

Look at the raw monthly listening data

query.sql

SELECT *
FROM listening_stats
ORDER BY user_id, month;

💡 Explanation

listening_stats logs one row per user per month; user_id repeats across three months (2026-03 through 2026-05) for each of the three users.
Priya's 2026-04 row has NULL minutes_streamed — the app logged no session that month, different from a row that would show 0 if she'd opened it and streamed nothing.
Aarav's minutes drop sharply from 1100 in April to 300 in May, while Karthik climbs steadily every month — spotting these patterns visually is the goal before writing the LAG comparison.
Nothing is filtered or compared yet — this step just confirms what the raw data looks like before the month-over-month logic gets built.

Pull each user's previous month using LAG, treating missing months as zero

query.sql

WITH monthly AS (
  SELECT user_id,
         user_name,
         month,
         minutes_streamed,
         LAG(COALESCE(minutes_streamed, 0)) OVER (PARTITION BY user_id ORDER BY month) AS previous_month_minutes
  FROM listening_stats
)
SELECT *
FROM monthly
ORDER BY user_id, month;

💡 Explanation

PARTITION BY user_id keeps each user's month sequence separate — LAG() never reaches across users, so Karthik's March never becomes 'the previous month' for Priya.
ORDER BY month inside OVER() puts each user's rows in chronological order before LAG() looks one row back; since month is stored as 'YYYY-MM' text, sorting it alphabetically is the same as sorting it chronologically.
COALESCE(minutes_streamed, 0) is applied inside LAG()'s argument, not after — that turns Priya's missing April into a 0 before it gets carried forward as May's previous_month_minutes, instead of leaving a NULL that would silently break the next step's comparison.
Aarav's and Karthik's first row (2026-03) still gets previous_month_minutes = NULL — COALESCE only rewrites NULL minutes_streamed values that already exist in the data; it can't invent a previous month where none exists in the partition.

Flag churn risk where minutes dropped more than 50%

query.sql

WITH monthly AS (
  SELECT user_id,
         user_name,
         month,
         minutes_streamed,
         LAG(COALESCE(minutes_streamed, 0)) OVER (PARTITION BY user_id ORDER BY month) AS previous_month_minutes
  FROM listening_stats
)
SELECT user_name,
       month,
       minutes_streamed,
       previous_month_minutes,
       CASE
         WHEN previous_month_minutes IS NULL OR previous_month_minutes = 0 THEN 0
         WHEN COALESCE(minutes_streamed, 0) < previous_month_minutes * 0.5 THEN 1
         ELSE 0
       END AS churn_risk
FROM monthly
ORDER BY user_name, month;

💡 Explanation

The first WHEN catches two different 'nothing to compare against' cases in one guard: previous_month_minutes IS NULL (a user's first month) and previous_month_minutes = 0 (Priya's May, where April got coalesced to 0) — both correctly get churn_risk = 0 instead of a misleading spike.
The second WHEN compares COALESCE(minutes_streamed, 0) — not the raw column — against half of the previous month's minutes, so Priya's April (NULL, treated as 0) correctly trips churn_risk = 1 against her March total of 800.
Aarav's May (300 minutes) compares against April's 1100: 300 is less than 550 (half of 1100), so churn_risk = 1 — a genuine cross-month drop, not a NULL artifact.
Karthik never triggers churn_risk across any month because his minutes climb every time; the CASE expression only ever sees his current value staying above half of the previous one.

▶ Practice this live ← Browse all questions