Skip to content
AI Engineer Guide.

concept · data · intermediate · concept 3 of 17

Window functions

0 of 17 complete

Why it matters

Analytics constantly needs a value that depends on neighbouring rows while keeping each row: the latest event per user, a running balance, a rank within a category. A self-join or a group-by plus join can express these, but at the cost of extra scans and awkward SQL. Window functions compute the value in the same pass and keep the row, which is why they are the standard tool for deduplication and ranking.

Mental model

A window function is a moving frame over a sorted table: each row sees a slice of its neighbours and computes from that slice. The metaphor breaks because the frame is defined by the query, not by physical order, and an unstable ORDER BY makes the slice arbitrary.

How it works

Mechanism, step by stepInteractive
  1. 1Partition by the entity whose rows should be compared, such as user id, so values do not leak across groups.
  2. 2Order within the partition by the column that defines sequence, and add a tiebreaker so the order is deterministic.
  3. 3Choose the frame explicitly: rows between unbounded preceding and current row for a running total, or the whole partition for a share of total.
  4. 4Use rownumber with a deduplication ordering to keep one row per key, then filter on the rank in an outer query.
  5. 5Check the plan for a sort per partition, since window functions often require one.
  6. 6Compare results against a group-by version on a small sample before trusting the frame.

Step 1 of 6

Partition by the entity whose rows should be compared, such as user id, so values do not leak across groups.

Step 1/6 · step 1

Figure. The mechanism in this page's own steps. The figure adds sequence, not new claims.

What to notice. A step can be read alone, but the order is the mechanism. The control lets you stop on any one.

Text version

The page's steps: 1. Partition by the entity whose rows should be compared, such as user id, so values do not … 2. Order within the partition by the column that defines sequence, and add a tiebreaker so t… 3. Choose the frame explicitly: rows between unbounded preceding and current row for a runni… 4. Use rownumber with a deduplication ordering to keep one row per key, then filter on the r… 5. Check the plan for a sort per partition, since window functions often require one. 6. Compare results against a group-by version on a small sample before trusting the frame.

  1. Partition by the entity whose rows should be compared, such as user id, so values do not leak across groups.
  2. Order within the partition by the column that defines sequence, and add a tiebreaker so the order is deterministic.
  3. Choose the frame explicitly: rows between unbounded preceding and current row for a running total, or the whole partition for a share of total.
  4. Use row_number with a deduplication ordering to keep one row per key, then filter on the rank in an outer query.
  5. Check the plan for a sort per partition, since window functions often require one.
  6. Compare results against a group-by version on a small sample before trusting the frame.

Trade-offs

ChoiceGood forCostFails when
ROW_NUMBER deduplicationOne row per key with a clear ruleRequires a deterministic orderingTwo rows tie and the choice is arbitrary
Running total with default frameCumulative metrics in one passFrame surprises when ORDER BY is addedYou wanted a partition-wide total
Window over a large partitionNo self-join and one scanMemory and sort costPartitions are huge and skew is severe
Group-by plus joinPortable and easy to reason aboutExtra scan and more codeThe table is large and scanned repeatedly
Trade-off mapInteractive

Good for

One row per key with a clear rule

Cost

Requires a deterministic ordering

Fails when

Two rows tie and the choice is arbitrary

  1. Two rows tie and the choice is arbitrary
  2. You wanted a partition-wide total
  3. Partitions are huge and skew is severe
  4. The table is large and scanned repeatedly

Figure. Each choice with what it buys, what it costs, and the case where it loses.

What to notice. The fourth column is the decision: if the losing case describes your situation, the choice is wrong.

Text version

Four trade-offs: ROWNUMBER deduplication is good for One row per key with a clear rule, costs Requires a deterministic ordering, and fails when Two rows tie and the choice is arbitrary. Running total with default frame is good for Cumulative metrics in one pass, costs Frame surprises when ORDER BY is added, and fails when You wanted a partition-wide total. Window over a large partition is good for No self-join and one scan, costs Memory and sort cost, and fails when Partitions are huge and skew is severe. Group-by plus join is good for Portable and easy to reason about, costs Extra scan and more code, and fails when The table is large and scanned repeatedly

Failure modes

  • Default frame mistake. A total column returns a running value because ORDER BY changed the frame. Debug: compare the value at the last row with a group-by sum over the same partition.
  • Non-deterministic ordering. Deduplication keeps a different row on each run. Debug: run the query twice and diff the kept ids for a sample key.
  • Partition skew. One partition holds most rows and the sort spills to disk. Debug: group by the partition key and inspect the maximum count against the median.

In production

Assert one row per key after deduplication, and watch spill and sort time for skewed partitions. Keep the tiebreaker in the ordering so reruns are reproducible.

Common misconceptions

People think: Window functions are just a convenience over GROUP BY.

Actually: They answer a different question: they return a value per row, which GROUP BY cannot do without a join back.

People think: ORDER BY inside OVER always limits the window to previous rows.

Actually: It changes the default frame, but an explicit frame can still cover the whole partition.

Interview angle

Explain partition, order and frame separately, then use deduplication as the concrete example. Mention determinism and skew. Weak answers write OVER (PARTITION BY ...) and cannot say which rows the frame includes.

Sources