Past the Threshold

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

L3

Asked in

Google

Problem

Our retention analyst calls a user engaged once they have more than 3 sessions in user_sessions: which users qualify, and how many sessions does each one have?

Somewhere after the third visit, a habit quietly forms. Find the ones who crossed over.

Tables

user_sessions

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

users

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

Expected output

Worked solution and explanation

This is a per-user threshold dressed up as a retention metric. The cutoff belongs to a user's whole history, not to any one session, so no row in user_sessions can tell you whether its owner qualifies: the count has to exist first, and the filter has to run on it. Everyone writes the count. What separates candidates is where the cutoff lands and what gets counted. Put it on the rows and the query errors or filters the wrong thing. Count device_id instead of sessions and four engaged users silently vanish. Read 'more than 3' as 'at least 3' and the list grows from 20 users to 48.

Getting to the answer

Step 1: Stay inside user_sessions

Every piece of the answer, user_id and the sessions themselves, lives in user_sessions. The users table is a temptation: joining it adds a second scan and changes no row of the result, because the session's user_id already identifies the user.

Step 2: Count sessions, not a column

COUNT(*) counts rows, and each row is a session. COUNT(device_id) counts non-NULL devices, and 23 sessions have no device recorded. User 876 has five sessions, three of them without a device, so the column count reports 2 and pushes them below the cutoff.

Step 3: Filter the count once it exists

The condition is about each user's total, so it goes in HAVING, which runs once per group after COUNT(*) is computed. WHERE runs per session before any grouping and cannot see the count; most engines reject WHERE COUNT(*) > 3 outright.

SELECT user_id, COUNT(*) AS session_count
FROM user_sessions
GROUP BY user_id
HAVING COUNT(*) > 3

Counting rows

COUNT(*): user 876 has 5 sessions and qualifies, and user 100 has 8. Twenty users clear the bar.

Counting a column

COUNT(device_id): NULL devices are skipped, so 876 drops to 2 and 100 to 6. Four of the twenty engaged users disappear without any error.

Common follow-up questions

• Engaged now means more than 3 sessions in the last 30 days. Where does the date condition go, and why does it differ from the session cutoff? (Tests row-level vs group-level filtering in one query: the date is a `WHERE` on `session_start`, and the count stays in `HAVING`.)
• Retention also wants each engaged user's `username` next to the count. How do you add it without counting sessions twice? (Tests aggregating before joining to `users`, so the join cannot fan out the counts.)
• What if the cutoff becomes the top 10% of users by session count instead of a fixed 3? (Tests replacing a literal threshold with one computed over every user's count.)