Where We Left Off

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

junior

Problem

We run a subscription app, and a user can start many sessions over their lifetime. Before a support call, the agent needs the page count from each user's most recent session, listed by user.

Every user has a last visit on file. Surface the state you last saw them in.

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

This is greatest-n-per-group wearing a support-tooling costume. The real skill: from many sessions per user, pick exactly one row, the most recent, without dropping users or returning two rows when a user's timestamps tie. Plenty of people know to sort by time; the separator is doing it per user in a single pass and keeping precisely one row each. The classic wrong turn is computing MAX(session_start) per user and joining it back, which quietly returns two rows for any user whose latest two sessions share a timestamp, and rescans the table to do it.

Walk the requirements

Step 1: Establish the grain

One user owns many sessions. The output is one row per user, so every decision from here is 'within this user, which single session survives'. Lose sight of that and you either collapse users together or leak duplicates.

Step 2: Rank each user's sessions by recency

Number the sessions inside each user from newest to oldest. session_start sorts correctly as ISO text, so ordering it descending puts the latest session first. Add session_id descending as a tiebreak so two sessions stamped at the same instant still produce a deterministic winner.

Step 3: Keep the newest, then order the report

Filter to the top-ranked row per user, which is the number-one session, and select its pages_viewed. List the result by user_id so the report is stable and easy to scan.

The solution

WITH ranked AS (
  SELECT user_id,
         pages_viewed,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY session_start DESC, session_id DESC) AS rn
  FROM user_sessions
)
SELECT user_id, pages_viewed
FROM ranked
WHERE rn = 1
ORDER BY user_id

`ROW_NUMBER` then filter

One scan. Ranks the sessions inside each user, keeps rank 1, and a session_id tiebreak forces a single winner. One row per user, guaranteed.

`MAX(session_start)` then join back

Two passes over the table. Correct only when timestamps are unique per user; the moment two sessions tie, the user appears twice with different page counts.

Common follow-up questions

• Return the page count from each user's earliest session instead of their latest. (Tests flipping the ranking order to ascending and confirming the direction is the only thing that changes.)
• For each user, return both their first and last session's page counts in one row. (Forces two rankings (or conditional aggregation over the ranked set) and a single collapsed row per user.)
• Now do this per device rather than per user, and break ties on identical timestamps by the higher `session_id`. (Probes swapping the partition key and making the tiebreak an explicit, stated rule.)