# 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

## 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. Include both users who were active outside that window and users who never had any sessions at all.

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

Place `s.session_start >= '2025-06-01' AND s.session_start < '2025-09-01'` in the ON clause to limit which sessions are considered.

#### Step 2: Filter for unmatched users

`WHERE s.user_id IS NULL` isolates users with no sessions in that window.

---

### The solution

**Anti-join scoped to a date range**

```sql
SELECT u.user_id, u.username, u.email
FROM users u
LEFT JOIN user_sessions s
    ON u.user_id = s.user_id
    AND s.session_start >= '2025-06-01'
    AND s.session_start < '2025-09-01'
WHERE s.user_id IS NULL
ORDER BY u.user_id
```

> **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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.