Inactive Users in Date Range

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
Instacart

Problem

The data quality team flagged stale records in a dimension table. Identify users who had zero sessions between June 1 and July 1, 2026, counting both users who were active outside that window and users who never had any sessions at all. Return the results as a clean alphabetical roster of usernames, A to Z.

Ghost accounts. Active signup, zero sessions.

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 185 rows
username
amelia
Alice
abel
aiden
andre

Worked solution and explanation

Why this problem exists in real interviews

Finding users with zero sessions in a specific date range tests anti-join with temporal constraints. The critical detail is placing the date filter in the ON clause, not the WHERE clause.

Break down the requirements

Step 1: Left join with date range in ON

LEFT JOIN users to user_sessions, pushing the June 1 to July 1 2026 date filter into the ON clause so non-matching users are preserved.

Step 2: Filter for unmatched users

Use a half-open upper bound (< '2026-07-02') so sessions dated July 1 are included as 'between June 1 and July 1' regardless of time-of-day.

The solution

Anti-join scoped to a date range
SELECT u.username
FROM users u
LEFT JOIN user_sessions us
  ON u.user_id = us.user_id
  AND us.session_start >= '2026-06-01'
  AND us.session_start < '2026-07-02'
WHERE us.session_id IS NULL
ORDER BY u.username

Common follow-up questions