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
- 1Partition by the entity whose rows should be compared, such as user id, so values do not leak across groups.
- 2Order within the partition by the column that defines sequence, and add a tiebreaker so the order is deterministic.
- 3Choose the frame explicitly: rows between unbounded preceding and current row for a running total, or the whole partition for a share of total.
- 4Use rownumber with a deduplication ordering to keep one row per key, then filter on the rank in an outer query.
- 5Check the plan for a sort per partition, since window functions often require one.
- 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.
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.
- Partition by the entity whose rows should be compared, such as user id, so values do not leak across groups.
- Order within the partition by the column that defines sequence, and add a tiebreaker so the order is deterministic.
- Choose the frame explicitly: rows between unbounded preceding and current row for a running total, or the whole partition for a share of total.
- Use row_number with a deduplication ordering to keep one row per key, then filter on the rank in an outer query.
- Check the plan for a sort per partition, since window functions often require one.
- Compare results against a group-by version on a small sample before trusting the frame.
Trade-offs
| Choice | Good for | Cost | Fails when |
|---|---|---|---|
| ROW_NUMBER deduplication | One row per key with a clear rule | Requires a deterministic ordering | Two rows tie and the choice is arbitrary |
| Running total with default frame | Cumulative metrics in one pass | Frame surprises when ORDER BY is added | You wanted a partition-wide total |
| Window over a large partition | No self-join and one scan | Memory and sort cost | Partitions are huge and skew is severe |
| Group-by plus join | Portable and easy to reason about | Extra scan and more code | The table is large and scanned repeatedly |
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
- Two rows tie and the choice is arbitrary
- You wanted a partition-wide total
- Partitions are huge and skew is severe
- 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
- PostgreSQL documentation, window functions tutorial — partitions, ordering and frames with examples.
- PostgreSQL documentation, SELECT — the normative window clause and frame syntax.
- DuckDB documentation, window functions — frame behaviour and a second implementation to compare.