PostgreSQL window functions for analytics queries

Need to compute running totals, rankings, or comparisons to previous rows without complex self-joins or subqueries.

Window functions provide row-level access to aggregate data:

-- Row numbering and ranking
SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rank,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank,
NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM students;

-- Running total
SELECT date, amount,
SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- Compare to previous/next row
SELECT date, revenue,
LAG(revenue) OVER (ORDER BY date) AS prev_day,
revenue - LAG(revenue) OVER (ORDER BY date) AS day_change,
LEAD(revenue) OVER (ORDER BY date) AS next_day
FROM daily_revenue;

-- Per-group ranking (top 3 per category)
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
FROM products
) t WHERE rn <= 3;

-- Moving average
SELECT date, value,
AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM metrics;

-- First/last value in group
SELECT DISTINCT department,
FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner
FROM employees;

Window functions avoid self-joins and subqueries for analytics. They compute across rows while keeping individual row detail.