Beyond the Signup

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

Domain

SQL

Difficulty

medium

Seniority

L4

Problem

A month in which every session in user_sessions came from a different person shows no return visits, so the analyst tracking engagement drops it and keeps only months where someone logged more than one session. For each remaining month in the two years leading up to 2026-09-12, report how many users were active and the average session duration.

Anyone can create an account. Fewer actually return.

Tables

users

Columns: user_id INTEGER, signup_date TEXT, account_status TEXT, email TEXT, age_bucket TEXT, username TEXT

user_sessions

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

Expected output

Worked solution and explanation

What this is really asking

This is a monthly rollup whose filter is built out of the very confusion it tests. Anyone can bucket session_start by month. The trap is the word 'active': one user_id can log several sessions in a month, so **COUNT(*) counts sessions, not people**. The month filter turns that trap inside out: 'someone came back' is a fact about the whole month, never about one session row, so it cannot live in WHERE. Miss either half and you keep months nobody returned in, while reporting session totals as people in the ones you keep.

Note that 'active' never touches users. A user is active in a month when one of their sessions falls in it, so user_sessions is the only table the answer reads.

Build it in three moves

Step 1: Cut the window in WHERE

Keep rows where session_start >= date('2026-09-12', '-24 months'). The cutoff rolls back two years from the anchor date, so the oldest bucket is a partial month. Each session either falls in the window or it does not, which makes it a row filter: it belongs in WHERE, before any grouping.

Step 2: Bucket by month, two aggregates side by side

Group on strftime('%Y-%m', session_start) so every session in a month lands in one bucket. Per bucket, COUNT(DISTINCT user_id) gives the people and AVG(session_duration_sec) gives the session-weighted average; NULL durations simply drop out of it.

Step 3: Keep months with a return visit in HAVING

If every session in a month came from a different person, COUNT(*) equals COUNT(DISTINCT user_id). One person logging twice pushes the session count above the people count. So HAVING COUNT(*) > COUNT(DISTINCT user_id) keeps exactly the months someone came back.

The solution

SELECT strftime('%Y-%m', us.session_start) AS month,
       COUNT(DISTINCT us.user_id) AS active_users,
       AVG(us.session_duration_sec) AS avg_duration_sec
FROM user_sessions us
WHERE us.session_start >= date('2026-09-12', '-24 months')
GROUP BY month
HAVING COUNT(*) > COUNT(DISTINCT us.user_id)

Counts sessions

COUNT(*) AS active_users counts every session row, so in every month the filter keeps, the figure is too high by exactly the repeat sessions.

Counts people

COUNT(DISTINCT user_id) collapses each user to one per month, and the raw COUNT(*) stays useful as the other side of the HAVING comparison.

SELECT strftime('%Y-%m', session_start) AS month,
       COUNT(*) AS active_users,
       AVG(session_duration_sec) AS avg_duration_sec
FROM user_sessions
WHERE session_start >= date('2026-09-12', '-24 months')
GROUP BY month

Common follow-up questions

Common follow-up questions

• How would you keep full calendar months only? (Add `AND session_start < date('2026-09-12', 'start of month')` so the partial current month drops out.)
• What if the bar were at least three returning users, not one? (Count sessions per (user, month) first, then count users with two or more in an outer query.)
• How does the answer change if `users.account_status` matters? (Inner-join `users` on `user_id` and filter to 'active' accounts in `WHERE`, before the unique count.)