# Where We Left Off > Every user has a last visit on file. Surface the state you last saw them in. Canonical URL: 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. ## 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 **Page count from each user's most recent session** ```sql 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 ``` *One pass: rank within each user by recency, keep rank 1.* > **One pass beats max-and-join** > > ROW_NUMBER over a partition does the whole job in a single scan: it ranks the sessions and labels the winner as rank 1. You never build a separate MAX table, never join it back, and never risk the duplicate-row surprise that a tie in MAX(session_start) creates. > **Common Pitfall** > > If a user has two sessions at the exact same session_start, filtering on session_start = MAX(session_start) keeps both, and that user shows up twice with conflicting page counts. ROW_NUMBER with a second sort key on session_id breaks the tie and guarantees exactly one row per user. > **Interviewers Watch For** > > Whether you reach for a windowed rank instead of a correlated subquery or a max-and-join, and whether you volunteer a deterministic tiebreak before being asked. Naming out loud that a user with a single session still appears, with that session as their latest, is the seniority tell. > **Cost Analysis** > > The window ranking is one sort of the table partitioned by user_id, then a cheap filter on rn = 1. A max-and-join instead reads the table twice: once to aggregate, once to join back. An index on (user_id, session_start) lets the engine feed the partition in key order and skip the sort, so the plan collapses to a single ordered scan. **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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/day-after-day-consecutive-logins) - [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.