SQL
Advanced 16 min read SQL

Window Functions

Per-row context without collapsing the table

Window functions compute values across a row's surrounding rows without collapsing them — enabling rankings, running totals, moving averages and value-of-peer comparisons within one query.

window functionsOVERPARTITION BYROW_NUMBERRANKmoving averageLAG
Prerequisites: Time-Series SQL Aggregates and Grouping
Before we start

What you will learn

  • Use PARTITION BY and ORDER BY inside OVER
  • Apply row/range frames for running and moving computations
  • Solve rankings, dedup and value-of-first patterns
Introduction

What is this?

Window functions perform aggregation-like work over a window of rows relative to the current row, WITHOUT grouping them away: ROW_NUMBER and RANK for ordering, LAG/LEAD for neighbors, SUM/AVG with a frame for running totals and moving averages. They are the workhorse for analytical SQL that keeps every row.

Motivation

Why do you need it?

  • Group-by loses the surrounding context window functions preserve.
  • Rankings, deduplication, deltas against neighbors and moving averages need per-row windowing.
  • One query replaces a tangle of self-joins and subqueries — and usually runs faster.
  • Moving averages smooth noisy operational metrics and trend signals.
Think about it

Mental model

Imagine

Progress at a timestamped magazine

Think of

Window over rows

GROUP BY is a chapter summary; window functions are the margin notes next to each line — you keep every line, and each note references what's above or around it.

Mechanics

How it works

  • OVER defines the partition (PARTITION BY) and order (ORDER BY) of the window.
  • Frames (ROWS/RANGE BETWEEN ...) bound which preceding/following rows compute with the current one.
  • LAG/LEAD read neighbors; ROW_NUMBER/RANK assign positions within the partition.
  • Window aggregates (SUM/AVG OVER) return a value per row while retaining all rows.
  • Windows execute after joins/grouping but before ORDER BY in logical processing.

How a window attaches context per row.

Partition
Order
Frame
Compute
Output
Hands-on

Examples

Top region by year and running average

Top region by year and running average
SELECT region, year, revenue,
       ROW_NUMBER() OVER (PARTITION BY year ORDER BY revenue DESC) AS rank_in_year,
       AVG(revenue) OVER (ORDER BY year ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS mavg
FROM region_yearly;

The ranking restarts each year (PARTITION BY) and the moving average uses a 3-row frame over the time-ordered table.

Real world

In production

  • A ranking report shows each sales rep's rank within their team partition.
  • A pipeline dedupes events with ROW_NUMBER partitioned by natural key, keeping the newest.
  • A support SLA report smooths daily p95 response with a 7-day moving average.
Guidelines

Best practices

  • Partition scope to the comparison (per team, per region) — windowing 'globally' misleads.
  • Choose a frame deliberately; default frames differ between running and peer semantics.
  • Use ROW_NUMBER for dedup, RANK for competitions with ties.
  • Test on a tiny set where the expected window values are hand-computable.
Watch out

Common mistakes

  • Omitting ORDER BY inside OVER, giving a partition-wide aggregate, not a running one.
  • Forgetting frame boundaries, so the moving average includes unintended rows.
  • Calling ROW_NUMBER 'RANK' and breaking tied competitions.
  • Using window functions where a simple GROUP BY would do, adding cost.
Root cause analysis

Common issues

Error
Running total equals grand total on every row
Root cause

No ORDER BY in the window, so the frame is the entire partition

Fix

Add ORDER BY inside OVER to make the frame cumulative.

Error
Dedup keeps the wrong row
Root cause

ORDER BY in the ranking picks the wrong 'newest' definition

Fix

Order the window by a monotonic vector (updated_at, event_id).

When things break

Debugging tips

  • If ROW_NUMBER yields unexpected output, check the ORDER BY column and tie behavior.
  • If a moving average looks flat, verify the frame (PRECEDING/FOLLOWING) direction.
  • If a window aggregate equals the whole-partition total, the ORDER BY is missing.
Interviews

Interview checkpoint

Practice

Hands-on lab

Rank, Dedupe and Smooth with Windows
40 min · advanced
In progress
Objectives
  • Rank teams with PARTITION BY
  • Dedupe events keeping the newest row
  • Compute a 3-day moving average

Rank, Dedupe and Smooth with Windows

40 min · advanced

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which function would you use to keep the newest row per natural key?

2

A window function always reduces the number of rows in the result.

Go deeper

Official documentation

PostgreSQL window functions
docs