SQL for Analytics
Advanced 15 min read SQL for Analytics

Window Functions

Aggregation that keeps the detail rows

Window functions compute values across a sliding frame over rows — running totals, rank, lag, moving averages — without collapsing rows like GROUP BY. They're the engine of cohort, funnel and trend analysis.

window functionsROW_NUMBERRANKLAGmoving averageOVERPARTITION BY
Prerequisites: SQL Aggregations
Before we start

What you will learn

  • Write OVER, PARTITION BY and ORDER BY windows
  • Use ROW_NUMBER, RANK, LAG/LEAD and running totals
  • Choose a window frame with ROWS BETWEEN
Introduction

What is this?

Window functions run a computation over a 'window' of rows related to the current row — the same partition, ordered, over a frame such as 'all rows to date'. Unlike GROUP BY they do not collapse rows; every input row survives with the computed value attached. Core tools: ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD, SUM/AVG over a frame.

Motivation

Why do you need it?

  • Running totals, ranks, 'previous period' and moving averages all need per-row context.
  • Cohort and retention tables are materially easier with windows than with subquery gymnastics.
  • RANK picks top-N per group in one pass.
  • LAG compares each row to its predecessor — the engine of trend and delta analysis.
Think about it

Mental model

Imagine

Scoreboard showing each runner's split vs the leader

Think of

Window functions

GROUP BY reports only final standings; a window shows every runner's split in context — their rank, gap to the leader, and their own last-lap time (LAG) — while keeping every runner on the board.

Mechanics

How it works

  • OVER (PARTITION BY grp ORDER BY ord) defines window; PARTITION splits rows into groups.
  • ORDER BY inside the window orders rows within each partition and sets the default frame (to current row).
  • ROW_NUMBER() assigns a sequential position; RANK leaves gaps after ties; DENSE_RANK does not.
  • LAG(x, n) grabs the value n rows earlier in the window; LEAD looks forward.
  • SUM(x) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is a running total.

How a window frames rows for an ordering-based function.

Source rows
PARTITION BY
ORDER BY
Window
Result
Hands-on

Examples

The analyst's window toolkit

The analyst's window toolkit
-- Schema of the cohort table
SELECT
  user_id,
  day,
  amount,
  SUM(amount)   OVER (PARTITION BY user_id ORDER BY day)            AS running_total,
  ROW_NUMBER()  OVER (PARTITION BY user_id ORDER BY day)            AS order_number,
  amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY day)     AS day_over_day,
  AVG(amount)   OVER (PARTITION BY user_id ORDER BY day
                 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)          AS mov_avg_7,
  RANK()        OVER (ORDER BY amount DESC)                          AS amount_rank
FROM purchases;

Each row keeps its identity but gains contextual numbers: a running total, a sequence, a delta from yesterday, a 7-row moving average, and a global rank.

Real world

In production

  • Top-10 customers per month via ROW_NUMBER filtered to rank <= 10.
  • Retention tables using ROW_NUMBER over user cohorts for 'nth month' flags.
  • Anomaly detection comparing each day's metric to its 30-day moving average via window.
Guidelines

Best practices

  • Read windowed SELECTs left to right: which columns come from the window vs the row.
  • Pick ROW_NUMBER vs RANK deliberately — ties behave differently.
  • Define PARTITION BY at the true unit of analysis (user, cohort, sku).
  • Be explicit about windows frames for moving windows to avoid surprises.
Watch out

Common mistakes

  • Forgetting PARTITION BY, so the window spans the whole table.
  • Using RANK where you needed sequence without ties (ROW_NUMBER).
  • Assuming ORDER BY in the window sorts the output rows (it only orders within the window).
  • Putting a window function in a computed part before the window exists — filter with a subquery.
When things break

Debugging tips

  • Rank replays oddly? Inspect partition membership with a small PARTITION BY user_id sample.
  • LAG returns NULL on the first row of a partition — coalesce or filter as intended.
  • Running total degraded? Your frame is picking the wrong BETWEEN range.
  • See doubles in ROW_NUMBER? The glob key in PARTITION repeats — partition by the true identity.
Interviews

Interview checkpoint

Practice

Hands-on lab

Build a Cohort Table With Window Functions
35 min · advanced
In progress
Objectives
  • Tag first purchase per user with ROW_NUMBER
  • Create cohort_months with LAG and frames
  • Verify cohort membership row by row

Build a Cohort Table With Window Functions

35 min · advanced

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

Quick quiz

0/2
1

Which function returns the previous row's value within a window?

2

Window functions collapse multiple rows into one row per group.

Go deeper

Official documentation

PostgreSQL window functions
docs
BigQuery analytic window functions
docs