SQL Mastery
Intermediate 16 min read SQL Mastery

Window Functions

Aggregate without collapsing, compare without self-joining

Window functions compute ranks, running totals and row-over-row comparisons over a window without collapsing rows — the key to moving averages, dedup and session analysis.

ROW_NUMBERRANKLAGLEADPARTITION BYframesrunning total
Prerequisites: Aggregations and GROUP BY
Before we start

What you will learn

  • Write ROW_NUMBER, RANK, LAG/LEAD and SUM() OVER
  • Control the window with PARTITION BY and ORDER BY
  • Deduplicate rows by a natural key with row_number
Introduction

What is this?

A window function computes a value for each row using the surrounding rows in its window, defined by PARTITION BY (groups) and ORDER BY (order within a group). Unlike GROUP BY it does not collapse rows — each input row gets an output row with the window value attached. This powers ranks, running totals, moving averages and row-over-row diffs.

Motivation

Why do you need it?

  • Row-over-row comparisons (LAG/LEAD) replace fragile self-joins.
  • ROW_NUMBER dedup on a natural key is the canonical 'keep the latest per key' pattern.
  • Moving averages smooth noisy metrics without GROUP BY collapse.
Think about it

Mental model

Imagine

Scorekeepers on each lane

Think of

Window functions

Each bowler still gets their own scorecard row (rows aren't deleted), but each lane has a running total and a rank shown alongside. Window functions attach group-derived numbers to individual rows without merging anyone.

Mechanics

How it works

  • PARTITION BY splits rows into independent groups; the window resets at each partition boundary.
  • ORDER BY inside OVER sets the order used by ranking and running totals.
  • ranking: ROW_NUMBER (unique 1..n), RANK (ties share rank, then gap), DENSE_RANK (ties share, no gap).
  • LAG(col, n)/LEAD(col, n) fetch the nth previous/next row's value — NULL at the edges.
  • Frames (ROWS BETWEEN ... AND ...) bound the window for moving/aggregate computations.

A windowed running total partitioned by channel.

Partition
Order within partition
Compute OVER frame
Emit row + value
Hands-on

Examples

Deduplicate to the latest record per key

Deduplicate to the latest record per key
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
  FROM order_updates
)
SELECT order_id, status
FROM ranked
WHERE rn = 1;

Each order_id ranks its updates by recency; rn = 1 selects the latest, deduping without a self-join.

Real world

In production

  • A dedup layer keeps one row per natural key using ROW_NUMBER over a compact partition.
  • A trend table computes a 7-day moving average with AVG() OVER (... ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
  • Session analysis marks a row as 'session start' when LAG(ts) exceeds a gap.
Guidelines

Best practices

  • Partition by the grain you mean; forget the partition and ranks span the whole table.
  • Anchor ORDER BY inside OVER explicitly — ranking without a deterministic order is non-reproducible.
  • Filter window output (WHERE rn = 1) inside a CTE/subquery, not in the same SELECT.
  • Use DENSE_RANK for bucket numbering where gaps would mislabel levels.
Watch out

Common mistakes

  • Omitting PARTITION BY and ranking across a whole table.
  • Putting the window in WHERE directly (dialect errors): wrap in a subquery/CTE first.
  • ORDER BY inside OVER omitted, so 'latest' is an arbitrary row.
  • Using fruitless ROWS BETWEEN defaults when the frame includes the whole partition (fine) but forgetting to now proper bounds for moving windows.
Root cause analysis

Common issues

Error
rn = 1 filter returns more than one row per key
Root cause

Pseudo-duplicate keys (case/whitespace) or wrong partition columns

Fix

Normalize the partition key and re-test with COUNT(*) per key.

Error
LAG of first row is not NULL as documented
Root cause

Frame or ORDER BY omitted changes default behavior

Fix

Explicitly write ORDER BY in OVER and rely on default frame; check the dialect's null handling.

When things break

Debugging tips

  • Print the raw window value before filtering on rn to see why a row was kept.
  • Compare RANK vs ROW_NUMBER counts when 'duplicates' unexpectedly vanish.
  • Take a 3-row partition and check the window output by hand to validate frame logic.
Interviews

Interview checkpoint

Practice

Hands-on lab

Deduplicate, Rank, and Compute Running Totals
30 min · intermediate
In progress
Objectives
  • Rank rows within partitions
  • Dedup to latest-per-key with row_number
  • Compute running totals and row-over-row diffs

Deduplicate, Rank, and Compute Running Totals

30 min · intermediate

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

Quick quiz

0/2
1

ROW_NUMBER() OVER (PARTITION BY cust ORDER BY ts DESC): which row of cust has rn = 1?

2

Window functions collapse rows the way GROUP BY does.

Go deeper

Official documentation

PostgreSQL window functions
docs
SQLite window functions
docs