The First Year

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

Asked in

Dropbox

Problem

We want the early adopters who came back, not the ones who signed up and vanished: the users who logged at least two sessions within a year of joining. For each of them, surface the user ID, signup date, and how many sessions landed inside that first-year window, smallest user ID first.

Activated early. A good sign.

Tables

users

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

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 is

This looks like a plain users-to-sessions join, but the real skill is two filters stacked on a per-user computed interval. Each user's window opens at their own signup and runs 365 days, and then you keep only the users who came back: at least two sessions inside that window. A plain grouped count hands back every user who logged even one qualifying session, which is exactly the one-and-done crowd you are trying to exclude. The threshold on the aggregate is what separates a returning user from a one-time visitor, and it can only be applied after the grouping. Two traps sit underneath: sessions dated before signup produce a negative day difference and sneak in under an upper-bound-only filter, and the INNER JOIN quietly drops session-less accounts, which is what you want. Miss the aggregate threshold and you fill your sticky-early-adopter cohort with people who visited once and never returned.

Break down the requirements

Step 1: Join users to sessions

INNER JOIN users to user_sessions on user_id so only users with sessions are considered. Accounts with no sessions at all fall out here for free.

Step 2: Filter sessions within 365 days of signup

Filter to sessions whose session_start falls between the signup date and 365 days later using julianday() date arithmetic. The lower bound of 0 is what excludes sessions logged before signup.

Step 3: Aggregate per user

GROUP BY user_id and signup_date and COUNT(*) the qualifying sessions per user.

Step 4: Keep only users who came back

Apply HAVING COUNT(*) >= 2 to keep only users who returned. This runs after the grouping, which is why it belongs in HAVING and not WHERE: the per-user count does not exist until aggregation happens.

The solution

SELECT u.user_id, u.signup_date, COUNT(*) AS session_count
FROM users u
INNER JOIN user_sessions us ON u.user_id = us.user_id
WHERE julianday(us.session_start) - julianday(u.signup_date) BETWEEN 0 AND 365
GROUP BY u.user_id, u.signup_date
HAVING COUNT(*) >= 2
ORDER BY u.user_id

Common follow-up questions

• How would you also list one-time visitors, flagged separately from returning users? (Tests LEFT JOIN with COALESCE plus keeping the HAVING logic off the join side.)
• How would you compute the return rate as a percentage of all signups? (Tests window functions or a separate total count in a CTE.)
• What if session timestamps had timezone offsets? (Tests timezone normalization in date arithmetic.)