snippetsqlModeratepending

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 to calculate running totals, row numbers, rankings, or moving averages in SQL without self-joins or subqueries. These aggregate calculations need to work per-row rather than collapsing rows.

Solution

Use window functions: aggregate or ranking functions with OVER clause. Partitioned for per-group calculations, ordered for sequential operations.

Code Snippets

Window functions for analytics queries

-- Running total of sales by month
SELECT 
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month) as running_total
FROM monthly_sales;

-- Rank users by score within each department
SELECT 
  name, department, score,
  RANK() OVER (PARTITION BY department ORDER BY score DESC) as dept_rank,
  ROW_NUMBER() OVER (ORDER BY score DESC) as global_rank
FROM users;

-- Compare with previous row (month-over-month change)
SELECT 
  month, revenue,
  LAG(revenue) OVER (ORDER BY month) as prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) as change
FROM monthly_sales;

-- Moving average (3-month window)
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.