How would you fetch the latest order, login, or event per user in SQL?

Use window functions for latest row per group

Uses ROW_NUMBER with deterministic partition ordering to return one complete latest row per group, then evaluates index support, tie semantics, scale, and read-model alternatives.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

I commonly assign ROW_NUMBER within each parent group, order newest first, and keep row number one. PARTITION BY UserId restarts numbering for each user; ORDER BY CreatedAt DESC, Id DESC defines a deterministic winner when timestamps tie. This returns the complete selected row without joining back on MAX(CreatedAt), which can duplicate results when several rows share the maximum timestamp.

Full answer below

Practice focus

Window functions, ROW_NUMBER, PARTITION BY, ORDER BY, tie-breaking, and practical read queries.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Why this question matters
Taking MAX(timestamp) finds a value, not necessarily the rest of the row, and ties can return several candidates. A latest-per-group query needs both group membership and a deterministic definition of latest.
I commonly assign ROW_NUMBER within each parent group, order newest first, and keep row number one. PARTITION BY UserId restarts numbering for each user; ORDER BY CreatedAt DESC, Id DESC defines a deterministic winner when timestamps tie. This returns the complete selected row without joining back on MAX(CreatedAt), which can duplicate results when several rows share the maximum timestamp.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
A practical shape is:
```
WITH ranked AS (
    SELECT e.*,
           ROW_NUMBER() OVER (
               PARTITION BY e.UserId
               ORDER BY e.CreatedAt DESC, e.Id DESC) AS rn
    FROM dbo.Events AS e
)
SELECT UserId, Id, CreatedAt, EventType
FROM ranked
WHERE rn = 1;
```
The tie-breaker reflects business ordering, not merely convenience. If the API requests a known tenant, time range, or set of users, I apply those predicates inside the ranked input so SQL Server does not rank unrelated history. An index beginning with the partition key and followed by CreatedAt DESC, Id DESC can reduce sorting and logical reads; projected values can be included when measured lookup cost justifies coverage. I inspect the actual plan and test duplicate timestamps to prove both performance and deterministic results.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
In production I confirm whether the query needs the latest row for every group or only a small requested set. Ranking an entire event history may sort or scan far more data than an indexed TOP (1) seek per selected parent, an aggregate strategy with a unique sequence, or a maintained current-state table. The best shape depends on group count, rows per group, filters, indexes, and query frequency, so I compare actual plans, logical reads, memory grants, spills, CPU, and elapsed time at realistic skew. ROW_NUMBER is nondeterministic when the partition and order columns do not produce a unique sequence; adding a stable tie-breaker is part of correctness. If late-arriving events can have older timestamps but newer business sequence, the ordering uses the authoritative sequence rather than ingestion time. A materialized latest-state read model makes reads predictable but adds write consistency, replay, and repair obligations. I validate duplicate timestamps, missing groups, tenant predicates, and concurrent inserts, then monitor read growth. The answer is not simply “use a window function”; it is define latest precisely, return one correct row, and choose an access path whose cost remains acceptable.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Joining back on MAX(CreatedAt) and returning multiple tied rows.
Omitting a deterministic tie-breaker from the window ordering.
Ranking the entire history when only a small parent set is requested.
Treating ingestion timestamp as business order without confirming semantics.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot explain PARTITION BY and ORDER BY.
Ignores duplicate timestamps.
Has no performance plan for a large event table.
Does not know when a read model is cleaner than a live query.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

What index would help this query?
How would you handle two events at the same time?
When would you store the latest state separately?

Continue practice

No explicit flow link is set yet, so these are nearby drills from the same path or taxonomy.