QUERY SYSTEMS

Window Functions & Analytics Patterns

Chapter 05MediumAnalytics

Orientation

What You'll Master Here

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.

Why data engineers care

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.

Core mental model

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.

Number each buyer’s orders by recency
Input data
orders6 rows
order_idbuyer_idcreated_at
100112026-01-12 08:40
100412026-01-15 14:20
100912026-01-28 18:00
100232026-01-12 10:02
100732026-01-22 14:46
101232026-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;
Query result6 rows
buyer_idorder_idcreated_atorder_recency
110092026-01-28 18:001
110042026-01-15 14:202
110012026-01-12 08:403
310122026-02-05 10:001
310072026-01-22 14:462
310022026-01-12 10:023

Recency restarts at 1 for each buyer because of partition by buyer_id.

Same rows in, same rows out — the window just adds a per-buyer recency number. Nothing is collapsed.
countryorder_idamountrow_number
US1001801
US10042202
GB10021201
GB10106402
Key terms
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.

Common mistake

Using GROUP BY when you still need row-level detail.

You collapse the very rows you needed for ranking, deduplication, or sequence analysis.

Better habit

  • Name the partition out loud.
  • Name the ordering out loud.
  • Decide how ties should break.
Interview note

Say the window as a sentence: “within each buyer, ordered by created_at descending, assign a recency number.” Clarity here is the signal.

Study tip

Use the topic menu as a checklist. Each topic is an analytics pattern you should be able to write from a one-line description.

Remember this

Use window functions when the answer depends on row context but the result still needs row-level detail.