# User Session Roster

> Every user paired with their sessions, even users who never logged in

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Produce a complete user-to-session roster that includes users who have never started a session. Show each user's name, account status, signup date, and session start time, by username and then session start.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests LEFT JOIN to preserve all users including those without sessions. Interviewers check whether you choose the right join type and order the results correctly with a compound sort.

---

### 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, including those with zero sessions (session columns will be NULL).

#### Step 2: Select required columns

Return `username`, `account_status`, `signup_date`, and `session_start` for the roster.

#### Step 3: Order by username then session start

`ORDER BY username, session_start` sorts alphabetically by user, then chronologically by session.

---

### The solution

**Left join for complete user-session roster**

```sql
SELECT u.username, u.account_status, u.signup_date, s.session_start
FROM users u
LEFT JOIN user_sessions s ON u.user_id = s.user_id
ORDER BY u.username, s.session_start
```

> **Cost Analysis**
>
> The LEFT JOIN produces up to 80M rows (all sessions matched to users, plus unmatched users). The ORDER BY on 80M rows by two columns is the bottleneck. An index on `user_sessions(user_id, session_start)` helps.

> **Interviewers Watch For**
>
> Using LEFT JOIN instead of INNER JOIN. The prompt explicitly says "includes users who have never started a session."

> **Common Pitfall**
>
> Using INNER JOIN, which silently drops the ~200K users who have no sessions. This is a common error when candidates do not read the requirements carefully.

---

## Common follow-up questions

- How would you show a count of sessions per user instead of one row per session? _(Switch to GROUP BY with COUNT(s.session_id).)_
- What if you wanted to show 'No sessions' instead of NULL for session_start? _(Use COALESCE(s.session_start, 'No sessions').)_
- How would the query perform with 100M sessions? _(The sort on the full result set is expensive; consider pagination or pre-filtering.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/user_session_roster)
- [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.