Run calculations across related rows without collapsing them — number and rank rows with ROW_NUMBER and RANK, look at neighbours with LAG and LEAD, and build running totals with SUM OVER.
Why: unlike GROUP BY, which crushes many rows into one, a window function adds a calculated value to each row while still showing every row. OVER () defines the "window" of rows it looks at — here, the whole table.
SELECT
name,
price,
AVG(price) OVER () AS avg_price,
price - AVG(price) OVER () AS diff_from_avg
FROM products;Why: PARTITION BY splits the rows into groups and restarts the calculation for each group — like GROUP BY, but the individual rows survive. Here each product’s price is compared against the average within its own category.
SELECT
category,
name,
price,
AVG(price) OVER (PARTITION BY category) AS category_avg
FROM products;Why: ROW_NUMBER gives every row a unique position; RANK leaves gaps after ties (1, 1, 3); DENSE_RANK does not (1, 1, 2). ORDER BY inside OVER decides what "first" means. This is how you build leaderboards or "top N per group".
SELECT
category,
name,
price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS row_num,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank
FROM products;Why: LAG reaches back to a previous row and LEAD reaches forward to the next one, in the order you specify. This makes "compared to last month" calculations easy without joining the table to itself. We make a small monthly_revenue table to work with — run the setup first.
-- setup: one revenue figure per month
CREATE TABLE IF NOT EXISTS monthly_revenue (
month date,
revenue numeric(10, 2)
);
INSERT INTO monthly_revenue (month, revenue) VALUES
('2025-01-01', 1000.00),
('2025-02-01', 1500.00),
('2025-03-01', 1200.00),
('2025-04-01', 1800.00);SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;Why: adding ORDER BY to a SUM OVER turns it into a running total that accumulates row by row — the classic "balance after each transaction" or "cumulative sales" report.
SELECT
created_at,
total,
SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders;