Power Users

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

Problem

We're shortlisting repeat users for a case-study writeup, and anyone with 3 or more sessions in user_sessions makes the cut. List each one's username from users with their session count, most sessions first.

Engagement separates tourists from regulars.

Tables

users

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

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

user_sessions

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

user_sessions: 10 of 200 rows
session_iduser_iddevice_idsession_startsession_duration_secpages_viewed
70611972302026-02-02 01:00:00533
71222942592026-03-03 02:00:00766
71833912882026-04-04 03:00:00999
72444883172026-05-05 04:00:0012212
73055853462026-06-06 05:00:0014515
73666823752026-07-07 06:00:0016818
74277794042026-08-08 07:00:0019121
7488876NULL2026-09-09 08:00:0021424
75499734622026-01-10 09:00:0023727
761010704912026-02-11 10:00:0026030

Expected output

First 5 of 48 rows
usernamesession_count
alice8
aaron425
amelia5
jasper3
jin3

Worked solution and explanation

This is a filter on a number that does not exist yet, dressed up as a shortlist. Each user's session count only appears once user_sessions is collapsed per account, so the 3-or-more cut has to run after that collapse, in HAVING, never in WHERE. Anyone can write the join and the count. What separates candidates is where the threshold lives and what the group key is. Put COUNT(*) >= 3 in WHERE and the engine rejects the query. Leave the cut out and 59 users come back instead of 48, the eleven one- and two-session users included.

Walking it

Step 1: Join users to user_sessions

An inner join on user_id gives one row per session, tagged with its account. Inner is enough: a user with no sessions can never reach three. A LEFT JOIN only adds rows the cut throws away, and COUNT(*) would score each of them as 1, not 0.

Step 2: Group on the account, not the name

Use GROUP BY u.user_id, u.username. user_id is what makes a row one person, and username rides along so the SELECT can show it. Grouping on username alone would fold any two accounts that share a display name into one inflated row.

Step 3: Cut after counting

HAVING COUNT(*) >= 3 runs once every group's count is known. That is the whole problem. WHERE sees single session rows, and a single row has no count to compare.

Step 4: Sort by the count alone

Use ORDER BY session_count DESC. On this data one user has 8 sessions, 19 have 5 and 28 have 3. Users tied on a count may come back in any order, so no tiebreaker is owed.

Repeat users, most sessions first
SELECT u.username, COUNT(*) AS session_count
FROM users u
JOIN user_sessions s ON u.user_id = s.user_id
GROUP BY u.user_id, u.username
HAVING COUNT(*) >= 3
ORDER BY session_count DESC
Threshold in WHERE
WHERE COUNT(*) >= 3 asks each session row about a total that has not been computed yet. The engine refuses it: aggregates are not allowed in WHERE.
Threshold in HAVING
HAVING COUNT(*) >= 3 is evaluated per group after GROUP BY. Every account's count exists by then, and the 48 qualifying users survive.

Common follow-up questions