Window Functions — PARTITION BY, ORDER BY & Frames
OVER () computes an aggregate without collapsing rows. Interviews love frames because the default is surprising. Master PARTITION BY, ORDER BY, and ROWS vs RANGE before ranking and running totals.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
ROWS vs RANGE when ORDER BY values tie
Prefer
Name the frame
Production SQL says ROWS or RANGE out loud. ROWS keeps each peer as its own position. RANGE includes every row that shares the current ORDER BY value.
- Strict N-row windows want ROWS.
- Peer-aware cumulative sums want RANGE, written explicitly.
- A trailing 6 PRECEDING is six rows, not a calendar week.
Alternative
Omit the frame and hope
With ORDER BY, the default is roughly RANGE from unbounded preceding through the current row. Ties silently merge into the running aggregate.
- Two events on the same day both see both amounts.
- Calendar gaps are not row offsets. Build a date spine.
- Huge partitions sort and can spill. Read that on the EXPLAIN page, not here.
Overview
OVER () is the analytics primitive: compute an aggregate without collapsing rows. Interviews love frames because the default is surprising. Master PARTITION BY, ORDER BY, and ROWS vs RANGE before ranking and running totals.
<fn>() OVER (
[PARTITION BY expr, ...]
[ORDER BY expr [ASC|DESC], ...]
[frame_clause]
)- PARTITION BY resets the window. It is a group key that keeps detail rows.
- ORDER BY is peer order inside the partition. Required for ranking, running sums, and
LAG. - Frame is the subset of the partition visible to the current row.
Flow
- 1
1 Input rows
- next2 PARTITION BY keys
- 2
2 PARTITION BY keys
- next3 ORDER BY in the partition
- 3
3 ORDER BY in the partition
- next4 Apply ROWS or RANGE
- 4
4 Apply ROWS or RANGE
- next5 Evaluate the window function
- 5
5 Evaluate the window function
- next6 Emit same grain plus columns
- 6
6 Emit same grain plus columns
Lesson map
Window Functions — PARTITION BY, ORDER BY & Frames
OVER () computes an aggregate without collapsing rows. Interviews love frames because the default is surprising. Master PARTITION BY, ORDER BY, and ROWS vs RANGE before ranking and running totals.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB a["1 Input rows"] b["2 PARTITION BY keys"] c["3 ORDER BY in the partition"] d["4 Apply ROWS or RANGE"] a -->|1 Input rows to 2 PARTITION BY keys| b b -->|2 PARTITION BY keys| c c -->|3 ORDER BY in the partition| d
ROWS vs RANGE
| Mode | What it sees | Prefer when |
|---|---|---|
| ROWS | Physical offset in the ordered partition. Peers stay distinct positions. | Strict N-row windows |
| RANGE | Logical range versus the current ORDER BY value. Peers are included together. | Peer-aware cumulative sums |
| GROUPS | Peer-group steps (Postgres). Rarer in interviews. | You truly mean peer groups |
Omitting the frame when ORDER BY is present defaults to roughly RANGE from unbounded preceding through the current row. That is a running, peer-aware window. Always name the frame in production SQL.
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY txn_day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total_rows,
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY txn_day
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total_rangeOn a tied txn_day, the ROWS sum steps once per row. The RANGE sum jumps by every peer that shares that day.
Common frames
ROWS BETWEEN 6 PRECEDING AND CURRENT ROWis a trailing 7-row window, not a calendar week.ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGis the whole partition.GROUPSis peer-group based. Mention it, then go back toROWSandRANGE.
Windows vs GROUP BY
| GROUP BY | Window | |
|---|---|---|
| Grain | One row per group | Same grain as the input, plus columns |
| Filter groups | HAVING | Outer query. Postgres has no QUALIFY |
| Detail rows | Collapsed | Kept |
SELECT
user_id,
event_at,
events,
AVG(events) OVER (
PARTITION BY user_id
ORDER BY event_at
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3
FROM daily_events;SELECT
sku,
revenue,
revenue * 100.0 / SUM(revenue) OVER () AS pct_of_all,
revenue * 100.0 / SUM(revenue) OVER (PARTITION BY category) AS pct_of_cat
FROM sku_revenue;Empty OVER () is valid. The whole result is one partition. Multiple windows in one SELECT are valid. The planner may share sorts. If the plan is the question, read it on EXPLAIN. This page does not teach scan types.
Where the frame goes wrong
- 1
Default RANGE
Tied ORDER BY values share the running sum. - 2
ROWS as a calendar
6 PRECEDING is six rows, including gaps and duplicates. - 3
Named ROWS frame
Unbounded preceding through current row is a true running total. - 4
Date spine
generate_series plus a time predicate for last-7-days.
Sandboxes
ROWS running sum steps once per input value. The Range-like sandbox accumulates a whole day before every row on that day sees the total.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Day 2’s two rows both receive the same running total. That is the peer surprise RANGE encodes and ROWS does not.
Pros and cons of naming frames
- Pros: readable intent, no peer surprises, a portable mental model.
- Cons: verbosity.
ROWSmisused as a calendar window. Preferdate_truncorgenerate_seriesfor time.
Deep dive · Plans, not access methods
Large partitions sort and can spill to temp files. That shows up when you read a plan. How to read EXPLAIN (ANALYZE, BUFFERS) is EXPLAIN and EXPLAIN ANALYZE. Stop there. Do not design the supporting index on this page.
Pitfalls
Amounts 1, 2, 2, 5 with days 1, 2, 2, 3. Write both frames. Which running totals differ on day 2? Then say why “last 7 days” is not 6 PRECEDING.
Interview Q&A
What does PARTITION BY do?
Answer
It splits rows into independent windows. The function resets per partition and the detail rows stay.
What is the default frame with ORDER BY?
Answer
Usually RANGE from unbounded preceding through the current row. Running, and peer-aware.
ROWS vs RANGE on ties?
Answer
ROWS treats each row as its own position. RANGE lumps equal ORDER BY values into the frame together.
Can I use WHERE on a window column?
Answer
Not at the same SELECT level in Postgres. Wrap a subquery or CTE, then filter. There is no QUALIFY.
Is empty OVER () valid?
Answer
Yes. The whole result set is one partition. Useful for percent-of-total.
How do I get a moving average of the last 7 days?
Answer
Build a date spine and a time predicate. 6 PRECEDING is six rows, not seven calendar days.
Can one SELECT have multiple windows?
Answer
Yes. The planner may share sorts when partition and order match. Different windows can each sort.
Window vs an aggregate subquery join?
Answer
A window keeps the input grain and is often one pass. A joined aggregate collapses, then joins back. If you need costs, measure on EXPLAIN.