The Ninety-Day Comeback

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

TravelClick

Problem

A user is considered 'retained' if they start at least one additional session strictly after their very first session, but no later than 90 calendar days after that first session. Working from the user_sessions table, treat each user's earliest session_start (by calendar date) as their first session. Calculate the proportion of retained users out of all users who have any session. Return a single retention rate as a REAL value (retained users divided by total users).

Everyone shows up once. Who comes back before the quarter ends?

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 really measures

Strip the product framing and this is first-touch anchored, bounded-window retention: anchor every user to their first calendar day, then ask what share of them come back strictly later but inside 90 days. A plain GROUP BY on user_id cannot say that; you have to re-join each user's first date to their own sessions on an inequality window. The trap is subtle. Compare raw timestamps and a second session later on the same first day leaks in as a return; reach for an INNER JOIN and every user who never came back vanishes from the denominator, pinning the rate at 100%; divide two integer counts and it collapses to 0 or 1. Miss any one and the number you report is meaningless.

Break down the requirements

Step 1: Anchor each user's first day

GROUP BY user_id with MIN(DATE(session_start)) collapses each user to a single first calendar date. Using DATE() here, not the raw timestamp, is what stops a later session on that same first day from counting as a comeback.

Step 2: Flag the users who came back in time

Join the first-day anchor back to user_sessions where DATE(us.session_start) > fs.first_date and <= DATE(fs.first_date, '+90 day'), then GROUP BY user_id to get the distinct set of retained users. The strict > excludes the first day; the inclusive <= keeps day 90.

Step 3: Divide distinct retained by distinct total

LEFT JOIN the retained set onto the full session table and compute CAST(COUNT(DISTINCT ru.user_id) AS REAL) / COUNT(DISTINCT us.user_id). Counting over every session row is exactly why the DISTINCT matters: a user with eight sessions still counts once on each side.

The solution

WITH first_sessions AS (
    SELECT user_id, MIN(DATE(session_start)) AS first_date
    FROM user_sessions
    GROUP BY user_id
),
retained_users AS (
    SELECT fs.user_id
    FROM first_sessions fs
    JOIN user_sessions us
        ON fs.user_id = us.user_id
        AND DATE(us.session_start) > fs.first_date
        AND DATE(us.session_start) <= DATE(fs.first_date, '+90 day')
    GROUP BY fs.user_id
)
SELECT CAST(COUNT(DISTINCT ru.user_id) AS REAL) / COUNT(DISTINCT us.user_id) AS retention_rate
FROM user_sessions us
LEFT JOIN retained_users ru ON us.user_id = ru.user_id

Common follow-up questions

• How would you compute 30-day and 60-day retention alongside the 90-day rate? (Tests extending with additional windowed joins or conditional aggregation.)
• How would you break retention by first-session cohort (e.g. by week)? (Tests adding GROUP BY on the first session date.)
• What if a user has many return sessions within the 90-day window? (Tests ensuring users are counted once via COUNT(DISTINCT `user_id`).)