SQL Latest Row per Customer
SQL Latest Row per Customer helps analysts rank rows newest-first within each entity and keep row one.
What & Why
The Latest Row per Customer pattern is used to rank rows newest-first within each entity and keep row one. This keeps the SQL aligned with one concrete business question and makes the result grain explicit before the query is reused.
See How It Works
BUSINESS QUESTION
Growth wants the latest session recorded for every user.
| id | user_id | started_at | ended_at | page_views | device |
|---|---|---|---|---|---|
| 5001 | 101 | 2024-01-16 14:20:00+00 | 2024-01-16 14:52:00+00 | 8 | desktop |
| 5002 | 102 | 2024-02-10 11:10:00+00 | 2024-02-10 11:18:00+00 | 3 | mobile |
| 5003 | 103 | 2024-03-13 08:10:00+00 | 2024-03-13 09:04:00+00 | 12 | desktop |
| 5004 | 104 | 2024-04-03 12:00:00+00 | NULL | 1 | tablet |
| 5005 | 105 | 2024-04-04 09:00:00+00 | 2024-04-04 09:21:00+00 | 7 | mobile |
| 5006 | 106 | 2024-04-05 10:15:00+00 | 2024-04-05 10:42:00+00 | 5 | tablet |
| 5007 | 107 | 2024-04-06 13:05:00+00 | 2024-04-06 13:38:00+00 | 10 | desktop |
EXAMPLE QUERY
WITH ranked_sessions AS (
SELECT
id AS session_id,
user_id,
started_at,
device,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY started_at DESC, id DESC) AS session_rank
FROM growth.sessions
)
SELECT user_id, session_id, started_at, device
FROM ranked_sessions
WHERE session_rank = 1
ORDER BY user_id;RESULT — latest session per user
| user_id | session_id | started_at | device |
|---|---|---|---|
| 101 | 5001 | 2024-01-16 14:20:00+00 | desktop |
| 102 | 5002 | 2024-02-10 11:10:00+00 | mobile |
| 103 | 5003 | 2024-03-13 08:10:00+00 | desktop |
| 104 | 5004 | 2024-04-03 12:00:00+00 | tablet |
| 105 | 5005 | 2024-04-04 09:00:00+00 | mobile |
| 106 | 5006 | 2024-04-05 10:15:00+00 | tablet |
| 107 | 5007 | 2024-04-06 13:05:00+00 | desktop |
Each representative user has one session, so all session rows survive.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario