Last Seen

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Moore Capital Management

Problem

The retention team is building a recency model and needs each user's most recent session start alongside their user ID, with the roster running from the lowest user ID up to the highest.

Everyone has a most recent session.

Tables

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 really is

Under the retention costume this is a grain-collapse: one user owns many sessions, and you have to return exactly one row per user carrying the freshest timestamp. Everyone reaches for something. The tell is whether you reach for the cheap thing. Candidates who self-join the table back to itself on the latest date, or lean on DISTINCT, are solving a problem that was never here. The trap is subtler than it looks: forget to collapse per user and MAX quietly hands you a single global date for the whole table, which looks like a valid answer until someone checks the row count.

Walking through it

Step 1: Collapse to one row per user

GROUP BY user_id folds every one of a user's sessions into a single group. This is the step that guarantees one output row per user; skip it and the aggregate spans the entire table.

Step 2: Take the latest start per group

MAX(session_start) inside each group returns that user's latest start. Because session_start sorts lexically as an ISO timestamp string, MAX picks the most recent moment without any date parsing.

Step 3: Order for stable output

ORDER BY user_id makes the output deterministic so it lines up with the expected preview. The sort is presentation only; it does not change which rows come back.

The solution

SELECT user_id, MAX(session_start) AS latest_session_start
FROM user_sessions
GROUP BY user_id
ORDER BY user_id

Common follow-up questions

• Now return the session_id of that latest session too, not just its start time. How does the query change? (Aggregation returns the date but not the session that owns it; this forces a window function or a join back on (user_id, session_start).)
• If a user has two sessions that start at the identical timestamp, which one wins, and does your answer still return a single row? (Tests tie handling: two sessions sharing the exact maximum timestamp for one user.)
• At this scale, what index would you build so this stays cheap as the table grows? (Tests indexing intuition on a high-cardinality grouping key at 50M rows.)