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

Our recency model keeps scoring users on stale activity because it reads every row of user_sessions rather than one latest start per user. Give each user's most recent session start, 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. user_sessions holds one row per session, the model wants one row per user, and the only thing that survives the collapse is the freshest session_start. Anyone can find a latest timestamp. What separates candidates is whether they fold the group or just trim it. SELECT DISTINCT user_id, session_start looks tidy, but it still returns one row per session. A user with eight sessions shows up eight times, and the recency model joins on eight 'latest' dates. The opposite slip, MAX with no grouping, returns one global date for the whole table.

Walking through it

Step 1: Fold every session into its user

GROUP BY user_id is what makes the output one row per user. Without it the aggregate spans all of user_sessions. With DISTINCT instead, you only remove exact repeated pairs, so every session that started at a different moment survives.

Step 2: Take the latest start inside each group

MAX(session_start) returns each user's freshest start. session_start is TEXT, but it is stored as zero-padded 'YYYY-MM-DD HH:MM:SS', so string order is time order and no parsing is needed.

Step 3: List users from the lowest ID up

The roster is read lowest user_id first, and that order is part of the answer, so ORDER BY user_id stays. The grouping leaves exactly one row per user_id, so the sort has no ties to break.

The solution

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

Trims the rows

SELECT DISTINCT user_id, session_start removes only repeated pairs. Every session with its own start survives, so the result keeps the session grain.

Folds the group

GROUP BY user_id with MAX(session_start) turns each user's sessions into one row, so the result has exactly one row per user: 59 on this data.

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 timestamp 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.)