# The Ninety-Day Comeback

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

Canonical URL: <https://datadriven.io/problems/the_ninety_day_comeback>

Domain: SQL · Difficulty: hard · Seniority: L4

## 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).

## 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.

> **Trick to Solving**
>
> Do it in two moves, then count carefully.
> 
> 1. MIN(DATE(`session_start`)) per `user_id` gives the first calendar day
> 2. Join that anchor back to the user's sessions where the date is strictly after the first day and no more than 90 days later, and group to the set of retained users
> 3. LEFT JOIN the retained set onto every session and take COUNT(DISTINCT retained) over COUNT(DISTINCT all), cast to REAL

---

### 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 fifteen sessions still counts once on each side.

---

### The solution

**First-day anchor, 90-day return check, distinct-user ratio**

```sql
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
```

> **Cost Analysis**
>
> The first CTE is a per-user MIN; the second is a windowed self-join that lands each retained user once. The final LEFT JOIN scans every session, and the two COUNT(DISTINCT) dedupe the fan-out from users with many sessions. An index on `(user_id, session_start)` keeps both joins cheap.

> **Interviewers Watch For**
>
> LEFT JOIN is mandatory on the final step. An INNER JOIN drops every user who never returned, so the denominator holds only retained users and the rate reads 100% by construction. They also check that the return is strictly after the first calendar day, not on it.

> **Common Pitfall**
>
> Two classic misses: counting session rows instead of distinct users, so one heavy user swings the rate, and integer division from dividing two counts without CAST, which floors the answer to 0 or 1. Comparing raw timestamps instead of dates also leaks same-day sessions in as returns.

---

## 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`).)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_ninety_day_comeback)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.