How to Use Window Functions in PostgreSQL

How to Use Window Functions in PostgreSQL

Window functions add analytic power to SELECT statements by computing running totals, rankings, moving averages, and more without losing row-level detail.

What are PostgreSQL window functions?

Window functions are aggregate-like functions that operate over a specified “window” of rows returned by the query.Unlike GROUP BY, they return one value per input row, keeping detail intact.

Why choose window functions over GROUP BY?

Use window functions when you need both row detail and aggregates in the same result set—e.g., showing each order with its customer’s running total.

How do I write a basic window function?

Place the function in the SELECT list and add an OVER() clause. Specify PARTITION BY to reset calculations per group and ORDER BY to define row order.Frame clauses refine the window.

Example: running total per customer

SELECT customer_id,
order_date,
amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders
ORDER BY customer_id, order_date;

What frame clauses can I use?

ROWS or RANGE BETWEEN defines how many rows around the current one are included.Common shortcuts: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (running), ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING (moving window).

Which built-in functions work as window functions?

Most aggregates (SUM, AVG, COUNT, MIN, MAX) and specialized ones like ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE().

Can I reuse a window definition?

Yes. Use WINDOW clause: WINDOW w AS (PARTITION BY ...ORDER BY ...) then SUM(amount) OVER w to keep SQL DRY.

Best practices for window functions?

Index columns in PARTITION BY and ORDER BY for speed, keep frames explicit for readability, and avoid mixing windowed and non-windowed aggregates without GROUP BY.

Common mistakes and fixes

Omitting ORDER BY causes nondeterministic results in ranking or running totals. Always set ORDER BY when order matters.

Forgetting frame clause nuances can yield unexpected rows.Remember RANGE uses logical values, while ROWS uses physical row counts.

Further examples

Top-N per category

SELECT *
FROM (
SELECT product_id, category_id, sales,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn
FROM product_sales
) sub
WHERE rn <= 3;

Month-over-month difference

SELECT month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_revenue;.