LLM Best Practices

Postgres Window Functions Cheatsheet

Last updated 3 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

A window function computes a value per row over related rows without collapsing them like GROUP BY. It runs after WHERE, GROUP BY, and HAVING and before ORDER BY and LIMIT. Syntax: fn(args) OVER ([PARTITION BY ...] [ORDER BY ...] [frame]). Plain aggregates are on postgres-functions.

Ranking and offset

Use dense_rank() for no gaps and row_number() for unique rows.

FunctionResult for scores 90, 90, 80
row_number()1, 2, 3 (ties broken arbitrarily).
rank()1, 1, 3 (gap after ties).
dense_rank()1, 1, 2.
ntile(n)Bucket number 1 to n.
percent_rank(), cume_dist()(rank-1)/(rows-1); fraction of rows at or before.
lag(col, n, default), lead(col, n, default)Value n rows before / after (default offset 1; default replaces NULL at the edges).
first_value(col), last_value(col), nth_value(col, n)Value at a position in the frame.
SELECT name, score,
       rank()       OVER (ORDER BY score DESC) AS rnk,
       dense_rank() OVER (ORDER BY score DESC) AS dense
FROM results;
 
SELECT day, revenue, revenue - lag(revenue, 1, 0) OVER (ORDER BY day) AS delta
FROM daily_revenue;

Aggregates as windows

Add OVER to any aggregate; rows are kept.

PatternGives
sum(x) OVER (ORDER BY d)Running total.
sum(x) OVER (PARTITION BY dept)Department total on every row.
avg(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)3-row moving average.
100.0 * x / sum(x) OVER (PARTITION BY dept)Share of the group.
SELECT user_id, event_date,
       sum(events) OVER (PARTITION BY user_id ORDER BY event_date) AS running_total
FROM user_events;

Name a repeated definition once: ... OVER w ... WINDOW w AS (PARTITION BY dept ORDER BY salary).

Frames

State the frame explicitly whenever you use last_value or a moving window.

CaseDefault frame
ORDER BY presentRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (start to current row and its peers).
No ORDER BYThe whole partition.
FrameCovers
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGCurrent row and its neighbors.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGThe whole partition.
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROWRows within 7 days by the ORDER BY value (date or timestamp).

ROWS counts physical rows; RANGE compares values and treats ties as peers.

Gotchas

Avoid these; each yields a running value or an error.

GotchaFix
Window functions are not allowed in WHERE or HAVING.Compute them in a CTE or subquery, then filter outside.
last_value(x) with the default frame returns the current row’s peer, not the partition’s last.Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
An aggregate window with ORDER BY is a running value, not a partition value.Drop the ORDER BY for a partition-wide value.
ORDER BY inside OVER does not order the result.Add a query-level ORDER BY.
WindowAgg usually sits on a Sort.Index the partition and order columns so the plan can skip the sort.

Backlinks