Users Without Sessions

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
Amazon

Problem

Find users who have never started a session. Show the user ID, username, and email for each.

Account created. Never logged in.

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 141 rows
user_idusernameemail
2040cynthiacynthia@example.com
2137colin42NULL
2234cara_zcara_z@example.com
2428celineceline@example.com
2525danieldaniel@example.com

Worked solution and explanation

Why this problem exists in real interviews

This tests the anti-join pattern. Interviewers verify that you can find unmatched records between two tables and return specific columns from the base table.

Break down the requirements

Step 1: Left join users to sessions

LEFT JOIN user_sessions ON users.user_id = user_sessions.user_id preserves all users.

Step 2: Filter to unmatched users

WHERE user_sessions.session_id IS NULL isolates users with no session records.

Step 3: Return required columns

Select user_id, username, and email for each sessionless user.

The solution

Anti-join for users with no sessions
SELECT u.user_id, u.username, u.email
FROM users u
LEFT JOIN user_sessions s ON u.user_id = s.user_id
WHERE s.session_id IS NULL

Common follow-up questions