SQL Window Functions: Complete Guide for Data Analysts

What Are Window Functions and Why They Matter

Window functions perform calculations across a set of rows related to the current row — without collapsing those rows into a single output like GROUP BY does. They're the single most powerful SQL feature for analytical queries, and the one most often missing from junior analysts' toolkit.

Every ranking, running total, moving average, period-over-period comparison, and row-numbering task in SQL can be written more clearly and efficiently with window functions than with self-joins or subqueries.

Core Syntax

All window functions share the same structure: a function name, followed by OVER() which defines the window (the set of rows to operate on).

function_name(expression)
OVER (
  PARTITION BY column   -- divide rows into groups
  ORDER BY column       -- define order within each group
  ROWS/RANGE BETWEEN ... AND ...  -- optional frame
)

The PARTITION BY clause is like GROUP BY but doesn't collapse rows. The ORDER BY inside OVER() is completely independent of the query's final ORDER BY.

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

-- Number each customer's orders chronologically
SELECT
  order_id,
  customer_id,
  order_date,
  revenue,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_num,
  RANK()       OVER (PARTITION BY customer_id ORDER BY revenue DESC) AS revenue_rank,
  DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY revenue DESC) AS revenue_dense_rank
FROM orders;

Practical: Get each customer's most recent order only

-- Filter to last order per customer using ROW_NUMBER
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

Aggregate Window Functions

All standard aggregate functions (SUM, AVG, COUNT, MIN, MAX) work as window functions when combined with OVER().

SELECT
  order_date,
  revenue,
  -- Running total (cumulative sum)
  SUM(revenue) OVER (ORDER BY order_date) AS running_total,
  -- % of grand total
  ROUND(revenue / SUM(revenue) OVER () * 100, 1) AS pct_of_total,
  -- 7-day moving average
  AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d
FROM daily_revenue;

LAG and LEAD: Period-over-Period Comparisons

LAG() accesses the previous row's value; LEAD() accesses the next row. These replace complex self-joins for MoM/YoY calculations.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  -- Month-over-month growth %
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / LAG(revenue) OVER (ORDER BY month) * 100, 1
  ) AS mom_growth_pct
FROM monthly_revenue;

Window Frames: ROWS vs RANGE

The frame clause controls exactly which rows are included in the calculation. This is critical for running totals and moving averages.

-- Common frame patterns:
SUM(revenue) OVER (ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  -- running total
AVG(revenue) OVER (ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)          -- 3-day moving avg
SUM(revenue) OVER (ORDER BY date
  ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)  -- reverse running total
SUM(revenue) OVER ()                                  -- grand total (all rows)

Real Analyst Use Cases