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 session service is supposed to cap every user at 3 sessions in user_sessions, but the cap was never enforced at write time: which users break it, and how many sessions does each one hold?

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 cardinality-constraint check dressed up as a session cap. The rule says no user may hold more than three sessions, and that rule belongs to a user's whole set of rows, not to any one of them. No row in user_sessions can tell you whether its owner is over the cap, so the count has to exist first and the check has to run on it. Everyone writes the count. What separates candidates is where the check 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 violators silently pass. Read 'cap of 3' as 'three is already too many' and the list grows from 20 users to 48.

Getting to the answer

Step 1: Stay inside user_sessions

Everything the check needs, 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

The cap is on sessions, and each row is a session, so COUNT(*) is the measure. COUNT(device_id) counts only non-NULL devices, and 23 sessions have no device recorded. User 876 holds five sessions, three without a device, so the column count reports 2 and hides a real violation.

Step 3: Check the rule once the count exists

The rule 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, and 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 holds 5 sessions and breaks the cap, and user 100 holds 8. Twenty users are over the cap.

Counting a column

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

Common follow-up questions

• The cap becomes 3 sessions in any 30-day stretch ending today. Where does the date condition go, and why does it differ from the cap itself? (Tests row-level vs group-level filtering in one query: the date is a `WHERE` on `session_start`, and the count check stays in `HAVING`.)
• Support wants each violator's `username` next to the count so they can contact them. How do you add it without counting sessions twice? (Tests aggregating before joining to `users`, so the join cannot fan out the counts.)
• The service should have rejected every session past the third. How would you list the offending sessions themselves, not just the users? (Tests moving from a per-group violation to the specific rows that broke it, ordering each user's sessions by `session_start`.)