# User Session Roster > Every user paired with their sessions, even users who never logged in Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Ripple ## Problem Produce a complete user-to-session roster that includes users who have never started a session. Working alphabetically through the usernames, show each user's name, account status, signup date, and session start time, and within each person walk their sessions from the earliest start onward. ## 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 341 rows: | username | account_status | signup_date | session_start | | --- | --- | --- | --- | | amelia | pending_verification | 2024-04-04 | 2026-02-27 10:00:00 | | amelia | pending_verification | 2024-04-04 | 2026-03-03 02:00:00 | | Alice | inactive | 2023-05-25 | NULL | | aaron42 | suspended | 2026-03-03 | 2026-01-26 09:00:00 | | abel | active | 2026-04-08 | NULL | ## 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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.