# Inactive Users in Date Range

> Ghost accounts. Active signup, zero sessions.

Canonical URL: <https://datadriven.io/problems/inactive_users_in_date_range>

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.

## Tables

### users

- user_id INTEGER
- username TEXT
- email TEXT
- signup_date TEXT
- account_status TEXT
- age_bucket TEXT

Sample rows (10 of 200):

| user_id | username | email | signup_date | account_status | age_bucket |
| --- | --- | --- | --- | --- | --- |
| 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 |
| 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 |
| 294 |  amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 |
| 391 | arjun  | arjun@example.com | 2025-05-05 | active | 55-64 |
| 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ |
| 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL |
| 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 |
| 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 |
| 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 |
| 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 |

### user_sessions

- session_id INTEGER
- user_id INTEGER
- device_id INTEGER
- session_start TEXT
- session_duration_sec INTEGER
- pages_viewed INTEGER

Sample rows (10 of 200):

| session_id | user_id | device_id | session_start | session_duration_sec | pages_viewed |
| --- | --- | --- | --- | --- | --- |
| 7061 | 197 | 230 | 2026-02-02 01:00:00 | 53 | 3 |
| 7122 | 294 | 259 | 2026-03-03 02:00:00 | 76 | 6 |
| 7183 | 391 | 288 | 2026-04-04 03:00:00 | 99 | 9 |
| 7244 | 488 | 317 | 2026-05-05 04:00:00 | 122 | 12 |
| 7305 | 585 | 346 | 2026-06-06 05:00:00 | 145 | 15 |
| 7366 | 682 | 375 | 2026-07-07 06:00:00 | 168 | 18 |
| 7427 | 779 | 404 | 2026-08-08 07:00:00 | 191 | 21 |
| 7488 | 876 | NULL | 2026-09-09 08:00:00 | 214 | 24 |
| 7549 | 973 | 462 | 2026-01-10 09:00:00 | 237 | 27 |
| 7610 | 1070 | 491 | 2026-02-11 10:00:00 | 260 | 30 |

## 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.

> **Trick to Solving**
>
> For a LEFT JOIN anti-pattern with a date range, the date filter must go in the ON clause. If you put it in WHERE, it converts the LEFT JOIN to an INNER JOIN for the date condition.
> 
> 1. LEFT JOIN on `user_id` AND session date range
> 2. Place the date filter in the ON clause
> 3. Filter WHERE `session.user_id` IS NULL

---

### 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**

```sql
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
```

> **Cost Analysis**
>
> The date filter in the ON clause limits which sessions are joined. An index on `user_sessions(user_id, session_start)` is essential.

> **Interviewers Watch For**
>
> The interviewer specifically checks ON vs. WHERE placement. This is a common source of bugs in production SQL.

> **Common Pitfall**
>
> Moving the date filter to WHERE drops users with no sessions entirely (their NULL `session_start` fails the date check).

---

## Common follow-up questions

- Why must the date filter be in ON, not WHERE? _(Tests LEFT JOIN filter placement semantics.)_
- How would you find users inactive for 3 consecutive months? _(Tests gap-and-island analysis.)_
- How would you parameterize the date range? _(Tests parameterized queries.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/inactive_users_in_date_range)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.