Who Stayed

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

L5

Asked in

General Assembly

Problem

One calendar month without a session in user_sessions breaks a streak, and only months from January through June of 2026 count toward one. List every user active in that window with their longest run of back-to-back active months and a standing numbered by streak length rather than by user, longest streaks first.

Day after day after day. Who kept coming back?

Tables

user_sessions

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

Expected output

Worked solution and explanation

What this problem really is

This is gaps-and-islands wearing a retention costume. Strip the story and the real move is this: collapse each user's sessions to one row per active month, number those months in order, and subtract. That difference stays constant across a run of back-to-back months and jumps the moment a month is skipped, so grouping by it hands you every streak. Anyone can count active months per user. The trap is that a count is not a streak: a user seen in January, February and June has three active months and a longest run of two, and a count-based answer quietly promotes every scattered visitor past the people who actually stayed.

Building it up

Step 1: Collapse sessions to active months

In active_months, keep sessions with session_start >= '2026-01-01' and session_start < '2026-07-01', reduce each to EXTRACT(MONTH FROM session_start), and group by user_id and that month. Ten sessions in March become one active March; without this, a busy month numbers as several rows and breaks its own run.

Step 2: Derive a run key per user

In numbered, compute active_month - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_month) as run_id. Both sides climb by one per month inside a run, so the difference holds still; a skipped month bumps the month by two while the row number moves by one, and run_id shifts.

Step 3: Measure runs, keep the longest

In runs, COUNT(*) per user_id and run_id is each run's length. In longest, MAX(run_length) keeps the one run that matters per user. A user with two runs of two still has a streak of two, not four.

Step 4: Assign standings that ties share

DENSE_RANK() OVER (ORDER BY streak_length DESC) numbers streak lengths, not users: everyone on the longest length shares standing 1 and the next length takes 2. Sort the output by streak_length DESC; rows inside a tie may come in any order.

The solution

WITH active_months AS (
    SELECT
        user_id,
        EXTRACT(MONTH FROM session_start) AS active_month
    FROM user_sessions
    WHERE session_start >= '2026-01-01'
      AND session_start < '2026-07-01'
    GROUP BY user_id, EXTRACT(MONTH FROM session_start)
),
numbered AS (
    SELECT
        user_id,
        active_month - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_month) AS run_id
    FROM active_months
),
runs AS (
    SELECT user_id, run_id, COUNT(*) AS run_length
    FROM numbered
    GROUP BY user_id, run_id
),
longest AS (
    SELECT user_id, MAX(run_length) AS streak_length
    FROM runs
    GROUP BY user_id
)
SELECT
    user_id,
    streak_length,
    DENSE_RANK() OVER (ORDER BY streak_length DESC) AS standing
FROM longest
ORDER BY streak_length DESC

Active months

COUNT(*) of active months per user. January, February and June scores 3. It measures how often someone showed up, not whether they stayed.

Longest unbroken run

MAX over run lengths keyed by active_month - ROW_NUMBER(). January, February and June scores 2, because June starts a new run.

Common follow-up questions

• How would you change the query if one missed month is forgiven and the streak continues? (Tests adapting the run key to a tolerance.)
• If the window ran from October to March, why does a bare month number break, and what index would you use instead? (Tests the year boundary the window currently avoids.)
• How would you also return the first and last month of each user's longest run, and which run wins when a user has two of equal length? (Tests reporting the run itself, not just its length.)
• `session_start` is stored in UTC. How would a session late on the last night of a month in the user's local time affect their streak, and how would you fix it? (Tests time zone handling at month boundaries.)