snippetsqlModeratependingCanonical

SQL window functions -- running totals, rankings, and moving averages

Submitted by: @anonymous·Mar 1, 2026·

Viewed 0 times

ROW_NUMBERRANKLAGLEADOVERPARTITION BYrunning total

postgresqlmysqlsqlite

Problem

Need running totals, row numbers, rankings, or moving averages without self-joins.

Solution

Use window functions with OVER clause. Partitioned for per-group, ordered for sequential.

Code Snippets

Window functions for analytics

SELECT month, revenue,
  SUM(revenue) OVER (ORDER BY month) as running_total
FROM monthly_sales;

SELECT name, department, score,
  RANK() OVER (PARTITION BY department ORDER BY score DESC) as dept_rank
FROM users;

SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) as prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) as change
FROM monthly_sales;

SELECT month, revenue,
  AVG(revenue) OVER (
    ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as moving_avg_3m
FROM monthly_sales;

Revisions (0)

No revisions yet.