Users Who Churned in February

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain
SQL
Difficulty
hard
Seniority
L4
Asked in
ESPN

Problem

Which users logged at least one session in user_sessions between November 2025 and the end of January 2026, then went all of February 2026 without one? The retention analyst asking wants each lapsed user listed once.

Gone in February.

Tables

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

user_sessions: 10 of 200 rows
session_iduser_iddevice_idsession_startsession_duration_secpages_viewed
70611972302026-02-02 01:00:00533
71222942592026-03-03 02:00:00766
71833912882026-04-04 03:00:00999
72444883172026-05-05 04:00:0012212
73055853462026-06-06 05:00:0014515
73666823752026-07-07 06:00:0016818
74277794042026-08-08 07:00:0019121
7488876NULL2026-09-09 08:00:0021424
75499734622026-01-10 09:00:0023727
761010704912026-02-11 10:00:0026030

Expected output

First 5 of 16 rows
user_id
973
1846
779
1652
585

Worked solution and explanation

What this really is

This is a per-user set difference dressed up as a retention question: everyone seen over the winter, minus everyone seen in February. Anyone can write the exclusion. What separates candidates is remembering that the left side is a session log spanning three months, not a list of users. A user with four winter sessions is four rows until you say otherwise, and if you forget, your lapsed list names your most engaged lapsed users several times each.

Building it

Step 1: Bound the winter window with half-open dates

session_start >= '2025-11-01' AND session_start < '2026-02-01' covers November through January, crosses the year boundary without any month arithmetic, and catches a session at 23:59 on January 31.

Step 2: Exclude the February set

The subquery reads user_sessions a second time for February and returns its user_id values; NOT IN drops any winter row whose user shows up there. The subquery needs no DISTINCT: membership does not care about repeats.

Step 3: Collapse to one row per user

SELECT DISTINCT user_id is the step that turns surviving sessions into surviving users. It belongs on the outer query, where the repeats actually reach the output.

Winter users who went quiet in February
SELECT DISTINCT user_id
FROM user_sessions
WHERE session_start >= '2025-11-01'
  AND session_start < '2026-02-01'
  AND user_id NOT IN (
      SELECT user_id
      FROM user_sessions
      WHERE session_start >= '2026-02-01'
        AND session_start < '2026-03-01'
  )
Without the dedup
SELECT user_id returns one row per qualifying winter session, so a user with three sessions in December appears three times.
With it
SELECT DISTINCT user_id returns each lapsed user exactly once, which is what a list of who left actually means.

Common follow-up questions