# Last Seen > Everyone has a most recent session. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## 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. ## 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. > **Trick to solving** > > You are not selecting a latest row, you are aggregating a column. MAX(session_start) grouped by user_id gives one date per user in a single pass, no self-join and no window function required. --- ### 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 **MAX aggregation per user** ```sql SELECT user_id, MAX(session_start) AS latest_session_start FROM user_sessions GROUP BY user_id ORDER BY user_id ``` > **Common pitfall** > > The most common miss is dropping GROUP BY entirely and writing SELECT user_id, MAX(session_start). Some engines error on the ungrouped user_id, but permissive ones return one arbitrary user_id next to the global maximum date and call it a day. Always confirm your result has as many rows as there are distinct users. > **Interviewers watch for** > > A strong candidate says out loud why aggregation beats a self-join here: the self-join scans and rematches 50M rows to find each user's latest, while the grouped MAX does it in one pass. Naming that cost difference is the seniority tell. > **Performance insight** > > With 50M rows collapsing to about 4M users, this is a single grouped scan. A composite index on (user_id, session_start) lets the planner walk each user's group and read the trailing key as the MAX, turning the aggregate into an index-only skip scan on engines that support it. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/last_seen) - [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.