What You'll Master Here
partition chooses the group, order chooses the sequence, frame chooses how much of the sequence is visible.
Window functions let you compare rows while keeping row detail. They are the bridge between simple aggregation and real analytics questions.
Instead of collapsing rows with GROUP BY, a window function looks across a partition of related rows and writes the result back onto each row.
By the end you should be able to solve “latest per entity”, “top N per group”, “previous event”, “running metric”, and “retention by cohort” without guessing — and explain each OVER clause out loud.
A window is a lens over nearby rows: partition chooses the group, order chooses the sequence, frame chooses how much of the sequence is visible.
Data-engineering interviews lean on windows to test whether you can deduplicate, rank, sequence, and explain analytical state without losing grain. Production analytics needs the same.
- partition
- The group of rows a window function operates within (like GROUP BY, but rows are kept).
- order
- The sequence inside each partition that ranking and offsets follow.
- frame
- The slice of ordered rows an aggregate window can see (e.g. all rows up to the current one).
- offset
- A previous or next row reached by LAG or LEAD.
| order_id | buyer_id | created_at |
|---|---|---|
| 1001 | 1 | 2026-01-12 08:40 |
| 1004 | 1 | 2026-01-15 14:20 |
| 1009 | 1 | 2026-01-28 18:00 |
| 1002 | 3 | 2026-01-12 10:02 |
| 1007 | 3 | 2026-01-22 14:46 |
| 1012 | 3 | 2026-02-05 10:00 |
Two buyers shown; each has three orders.
select
buyer_id,
order_id,
created_at,
row_number() over (
partition by buyer_id
order by created_at desc
) as order_recency
from orders
where buyer_id in (1, 3)
order by buyer_id, order_recency;| buyer_id | order_id | created_at | order_recency |
|---|---|---|---|
| 1 | 1009 | 2026-01-28 18:00 | 1 |
| 1 | 1004 | 2026-01-15 14:20 | 2 |
| 1 | 1001 | 2026-01-12 08:40 | 3 |
| 3 | 1012 | 2026-02-05 10:00 | 1 |
| 3 | 1007 | 2026-01-22 14:46 | 2 |
| 3 | 1002 | 2026-01-12 10:02 | 3 |
Recency restarts at 1 for each buyer because of partition by buyer_id.
Using GROUP BY when you still need row-level detail. You collapse the very rows you needed for ranking, deduplication, or sequence analysis.
Name the partition out loud.
Name the ordering out loud.
Decide how ties should break.
Say the window as a sentence: “within each buyer, ordered by created_at descending, assign a recency number.” Clarity here is the signal.
Use the topic menu as a checklist. Each topic is an analytics pattern you should be able to write from a one-line description.
Use window functions when the answer depends on row context but the result still needs row-level detail.