Latest Event Per User SQL Query | SQL Interview Question

Problem statement

Prompt

Given the events table below, write a query that returns, for each user, the full row of their most recent event.

"Most recent" means the largest event_time; when two events share a timestamp, the one with the larger `event_id` wins.
Output columns: user_id, event_time, event_type.
Each user appears exactly once.

Example

event_id	user_id	event_time	event_type
1	1	2024-01-01 10:00:00	view
2	1	2024-01-02 09:30:00	click
3	2	2024-01-01 08:00:00	view

Expected result:

user_id	event_time	event_type
1	2024-01-02 09:30:00	click
2	2024-01-01 08:00:00	view

Constraints

events.event_id is the primary key.
events.event_time is not NULL.

Tips

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC, event_id DESC) then keep rn = 1.
Without the event_id tiebreaker, ties are resolved arbitrarily.

Schema

CREATE TABLE events (
    event_id   INTEGER PRIMARY KEY,
    user_id    INTEGER NOT NULL,
    event_time TEXT NOT NULL,
    event_type TEXT NOT NULL
);

Sample data

INSERT INTO events (event_id, user_id, event_time, event_type) VALUES
  (1, 1, '2024-01-01 10:00:00', 'view'),
  (2, 1, '2024-01-02 09:30:00', 'click'),
  (3, 1, '2024-01-02 11:15:00', 'purchase'),
  (4, 2, '2024-01-01 08:00:00', 'view'),
  (5, 2, '2024-01-03 12:00:00', 'click'),
  (6, 3, '2024-01-04 07:45:00', 'view');

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: one full row per user for their most recent event.
WITH ranked AS (
    SELECT user_id, event_time, event_type,
           ROW_NUMBER() OVER (
               PARTITION BY user_id
               ORDER BY event_time DESC, event_id DESC
           ) AS rn
    FROM events
)
SELECT user_id, event_time, event_type
FROM ranked
WHERE rn = 1
ORDER BY user_id;

Key concepts

ROW_NUMBER
PARTITION BY
Tiebreakers

Latest Event Per User