Window Functions

Compute values across sets of rows related to the current row, without collapsing rows like GROUP BY.

OVER with PARTITION BY and ORDER BY

SELECT
  o.id,
  o.customer_id,
  o.total,
  SUM(o.total) OVER (PARTITION BY o.customer_id) AS spend_per_customer
FROM orders o;

Ranking functions

SELECT
  id,
  total,
  RANK()       OVER (ORDER BY total DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY total DESC) AS dense,
  ROW_NUMBER() OVER (ORDER BY total DESC) AS rownum
FROM orders;

Running totals and moving averages

SELECT
  created_at,
  total,
  SUM(total) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running,
  AVG(total) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

Frames define which neighboring rows are included.

Lag/lead and differences

SELECT
  id,
  total,
  LAG(total)  OVER (ORDER BY id) AS prev,
  LEAD(total) OVER (ORDER BY id) AS next,
  total - LAG(total) OVER (ORDER BY id) AS delta
FROM orders;

Percentiles and distribution (engine-specific)

SELECT PERCENT_RANK() OVER (ORDER BY total) FROM orders;

Tips

• Always specify ORDER BY inside window clauses when order matters
• Partition wisely to control scope and performance
• Add indexes to support the partition/order keys

Summary

• Window functions add powerful analytics without collapsing rows
• Use frames to control running/moving computations