User Session Roster

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

L3

Asked in

Ripple

Problem

Our usernames were typed in by hand, so some carry stray spaces or capital letters, and we read each one as its trimmed, lowercase form. From users and user_sessions, build a roster of each user's cleaned name, account status, signup date and session start that keeps people who have never started a session, sorted by cleaned name and then by session start, earliest first.

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

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

Expected output

Worked solution and explanation

What this roster is really asking

This is an outer join dressed up as a reporting chore, with a cleaning step riding on top. Everyone links users to user_sessions on user_id. What separates candidates is whether the people who never logged in survive. Reach for an inner join because the ask sounds like 'users and their sessions' and every session-less user disappears without an error. The headcount comes back short, and nobody notices until onboarding numbers stop adding up.

The second wrinkle is the name itself. The username column holds ' amelia' with a leading space, 'arjun ' with a trailing one, and 'Alice' next to 'alice'. Sorted raw, the padded and capitalized names land in the wrong place. The statement's rule of reading a name as its trimmed, lowercase form has to happen before the sort.

Building it

Step 1: Keep users on the preserved side

Start FROM users and LEFT JOIN user_sessions on user_id. A user with sessions fans out to one row per session. A user with none still yields exactly one row, with session_start filled in as NULL. That NULL row is the whole point of the question.

Step 2: Clean the name once, in the select list

Compute LOWER(TRIM(u.username)) AS username directly in the SELECT and sort on that alias. The cleaned value is written once, so the displayed name and the sort key can never disagree, and no intermediate step is needed for a query this flat.

Step 3: Sort by the cleaned name, then by session start

ORDER BY username, session_start walks the roster alphabetically by cleaned name and, within a name, from the earliest session onward. Because username resolves to the alias, 'Alice' and 'alice' both become 'alice' and their rows sort together by session_start, instead of splitting across the list.

SELECT
    LOWER(TRIM(u.username)) AS 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 username, session_start

Inner join

JOIN user_sessions keeps only users with at least one session. Every account that signed up and never came back is dropped, and the row count looks plausible enough that nobody checks.

Left join

LEFT JOIN user_sessions keeps every row of users. The never-engaged accounts show up once with a NULL session_start, which is exactly the population an onboarding or churn report exists to find.

Common follow-up questions

• How would you show a count of sessions per user instead of one row per session? (Group the joined rows and count `s.session_id`, which counts zero for session-less users where `COUNT(*)` would say one.)
• What if you wanted 'No sessions' instead of NULL in `session_start`? (Wrap the column in `COALESCE`, and notice it changes the type of the column to text.)
• Two different accounts clean to the same name. How would you tell them apart in the roster? (Tests whether the candidate brings `user_id` back as the real identity once the display name is no longer unique.)