S039 · Window Functions — the Game-Changer

Module M04: Databases & SQL · Session 39 of 130 · Track: DE 🪟

What you'll be able to do after this session

• Explain Window Functions to a friend in one minute, out loud, no notes.
• Recognise it when you see it in real code / systems / papers.
• Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

• S038 — Aggregations — GROUP BY, HAVING, Subqueries

Pre-read (skim before the session)

• 🎥 Intuition (5–15 min) · SQL Window Functions in 10 Minutes — Alex The Analyst — the fastest "aha" for PARTITION BY.
• 🎥 Deep dive (30–60 min) · SQL Window Functions — CMU 15-445 (Andy Pavlo, Advanced SQL) — grad-school treatment; watch the window-frame section carefully.
• 🎥 Hands-on demo (10–20 min) · Mode Analytics — Window Functions Tutorial (video) — running totals & ranking on real datasets.
• 📖 Canonical article · PostgreSQL Docs — Window Functions Tutorial — the definitive intro; five minutes and you'll never forget the frame syntax.
• 📖 Book chapter / tutorial · Mode Analytics — SQL Window Functions — interactive; run queries in-browser.
• 📖 Engineering blog · Stripe Eng — Online Migrations at Scale — real-world use of window functions for chunked backfills.

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: Ranking, running totals, moving averages without joins.

Regular GROUP BY collapses many rows into one row per group — you lose the row-level detail. But you often want both: keep every row and attach a group-wide computation. "Show every employee and their rank within their dept." "Show every day's sale and the running 7-day total." A plain aggregate can't do this. A window function can — it computes over a "window" of related rows but returns one value per input row.

The magic clause is OVER (PARTITION BY … ORDER BY …). PARTITION BY dept says "the window is all rows with the same dept." ORDER BY salary DESC orders the rows within the window. Then RANK() OVER (…) gives each employee's rank in their dept. Same row count as input, extra column of insight. It's the difference between "give me averages per country" and "for each user, show their score and their country's average."

Window functions unlock queries that previously needed self-joins, correlated subqueries, or ugly INSERT INTO temp_table gymnastics. Once you learn ROW_NUMBER() for deduplication, RANK() for leaderboards, LAG/LEAD for period-over-period comparisons, and SUM() OVER for running totals — you've replaced 60% of "fancy SQL" tricks with three keywords.

The forever gotcha: WHERE runs before window functions. You cannot filter on a window value in the same query — you must wrap in a subquery or CTE. E.g., "top 3 per dept" is WHERE rn <= 3 in the outer query, never in the same SELECT.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

Reusing employees:

Query — rank employees within their dept by salary:

SELECT name, dept, salary,
       RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn_in_dept,
       AVG(salary)  OVER (PARTITION BY dept)                       AS dept_avg
FROM employees;

Result — same 6 rows as input, three new columns:

Notice dept_avg repeats for each row in the dept — that's the point.

RANK vs DENSE_RANK vs ROW_NUMBER — the tie-breaker cheat sheet:

Salaries [100, 90, 90, 80]:

Use ROW_NUMBER for deduplication (pick one row per group). Use RANK for leaderboards (Olympics-style). Use DENSE_RANK for percentile grouping.

Running total example — cumulative revenue:

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM orders;

The ROWS BETWEEN … PRECEDING AND CURRENT ROW clause is the frame — the subset of the partition to aggregate.

Frame syntax mental model:

LAG and LEAD — access neighbour rows:

SELECT order_date, amount,
       LAG(amount, 1)  OVER (ORDER BY order_date) AS prev_amount,
       LEAD(amount, 1) OVER (ORDER BY order_date) AS next_amount,
       amount - LAG(amount, 1) OVER (ORDER BY order_date) AS day_over_day_change
FROM orders;

Perfect for "day-over-day," "month-over-month," "step-function detection in time series." Before window functions this needed a self-join with ORDER BY … LIMIT 1 — painful and slow.

Top-N-per-group pattern — the killer app:

SELECT name, dept, salary FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 2;

"Top 2 earners per dept" in 4 lines. Try that with a correlated subquery — it's ugly.

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

SQLite 3.25+ supports window functions (that's basically every Python 3.9+). Save as windows_lab.py:

import sqlite3
conn = sqlite3.connect(":memory:"); cur = conn.cursor()
 
cur.executescript("""
CREATE TABLE employees (id INTEGER, name TEXT, dept TEXT, salary INTEGER, hired TEXT);
INSERT INTO employees VALUES
    (1,'Alice','Eng',120000,'2020-03-01'),
    (2,'Bob','Eng',95000,'2021-06-15'),
    (3,'Carol','HR',80000,'2019-01-10'),
    (4,'Dan','Eng',140000,'2018-09-20'),
    (5,'Eve','Sales',110000,'2022-02-01'),
    (6,'Frank','Sales',60000,'2023-11-05');
 
CREATE TABLE sales (day TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('2026-01-01','South',100),('2026-01-01','North',200),
    ('2026-01-02','South',150),('2026-01-02','North',180),
    ('2026-01-03','South',300),('2026-01-03','North',210),
    ('2026-01-04','South',120),('2026-01-04','North',260),
    ('2026-01-05','South',400),('2026-01-05','North',300);
""")
 
def run(label, sql):
    print(f"\n-- {label} --")
    for row in cur.execute(sql):
        print("  ", row)
 
run("1. Rank, row_number, dept_avg — three windows in one query", """
    SELECT name, dept, salary,
           RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rk,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
           AVG(salary)  OVER (PARTITION BY dept)                       AS dept_avg
    FROM employees ORDER BY dept, rk;""")
 
run("2. Top 2 earners per dept — the killer pattern", """
    SELECT name, dept, salary FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
        FROM employees
    ) WHERE rn <= 2 ORDER BY dept, salary DESC;""")
 
run("3. Running total & 3-day moving average per region", """
    SELECT day, region, amount,
           SUM(amount) OVER (PARTITION BY region ORDER BY day
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running,
           AVG(amount) OVER (PARTITION BY region ORDER BY day
                             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma_3d
    FROM sales ORDER BY region, day;""")
 
run("4. LAG — day-over-day change per region", """
    SELECT day, region, amount,
           LAG(amount) OVER (PARTITION BY region ORDER BY day) AS prev_amt,
           amount - LAG(amount) OVER (PARTITION BY region ORDER BY day) AS delta
    FROM sales ORDER BY region, day;""")
 
run("5. DENSE_RANK vs RANK on tied salaries", """
    WITH t AS (SELECT * FROM employees UNION ALL SELECT 8,'Heidi','Eng',95000,'2024-01-01')
    SELECT name, salary,
           RANK()       OVER (ORDER BY salary DESC) AS rk,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS drk
    FROM t;""")
 
run("6. NTILE(4) — bucket employees into quartiles by salary", """
    SELECT name, salary, NTILE(4) OVER (ORDER BY salary DESC) AS quartile
    FROM employees;""")
 
run("7. FIRST_VALUE / LAST_VALUE — get top salary alongside each row", """
    SELECT name, dept, salary,
           FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_earner
    FROM employees ORDER BY dept, salary DESC;""")
 
# Common trap: window functions can't go in WHERE
try:
    run("8. TRAP: can't filter on window column in same query", """
        SELECT name, RANK() OVER (ORDER BY salary DESC) AS rk
        FROM employees WHERE rk <= 3;""")
except sqlite3.OperationalError as e:
    print("Error (expected):", e)
# Fix: wrap in subquery/CTE — see Query 2.
 
conn.close()

Checklist — observe when you run:

1. Query 1: three windows in one query, no self-joins. dept_avg value repeats per row within a dept.
2. Query 2: "top 2 per group" — memorise this pattern, you'll use it weekly.
3. Query 3: running totals grow monotonically; the 3-day moving average smooths spikes. Try changing to 6 PRECEDING for a weekly moving average.
4. Query 4: LAG returns NULL for the first row per partition (no previous row exists).
5. Query 5: Bob and Heidi both earn 95000. RANK gives 4,4,6 (skips 5); DENSE_RANK gives 4,4,5 (no skip). Choose based on how you show tied leaderboards.
6. Query 6: NTILE(4) puts each employee in Q1–Q4 by salary — instant quartile analysis.
7. Query 7: FIRST_VALUE decorates every row with a per-partition top value — great for "compare to leader" columns.
8. Query 8 errors (or returns wrong result in permissive dialects). Wrap window queries in a CTE (S040) or subquery before filtering.

Try this modification: add a column showing each employee's percentile within dept using PERCENT_RANK() OVER (PARTITION BY dept ORDER BY salary). Then find employees below the 50th percentile in their dept — that's a "low performer" report in 6 lines.

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

Window functions are the difference between "SQL user" and "SQL engineer." Every serious analytics query, deduplication script, session stitching pipeline, cohort analysis, and time-series report uses them. Ignore this session and you'll spend the next decade writing correlated subqueries that don't scale.

War story — deduplication. A payments team had duplicate transaction rows from an at-least-once webhook. Naive fix: DELETE FROM tx WHERE id IN (SELECT id FROM tx GROUP BY hash HAVING COUNT(*) > 1) — which deletes all duplicates including the "good" one. Correct: DELETE FROM tx WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY hash ORDER BY created_at) rn FROM tx) WHERE rn > 1) — keeps the first, deletes rest. This exact pattern is in every serious ETL codebase.

War story — sessionisation at analytics scale. Google Analytics defines a "session" as continuous events with ≤30 min gaps. Naive: self-join for each event's previous event → O(n²). Window-function version: LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time), mark new session when gap > 30min, SUM those markers as a running session id. Linear pass. This exact pattern powers Snowplow, Mixpanel, Amplitude backends.

Gotchas top teams handle:

• Window functions run after WHERE but before ORDER BY. You cannot filter on window columns in the same SELECT. Wrap in a CTE or subquery. If you're using dbt or LookML, this is why models chain up.
• Frame default surprise. SUM(x) OVER (ORDER BY d) defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which behaves differently from ROWS when the ORDER BY column has duplicates. Always specify ROWS explicitly for running totals.
• Ordering ties. ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) — if two employees have the same salary, which gets rn=1? Undefined. Add a tiebreaker: ORDER BY salary DESC, id.
• Performance. Window functions require the entire partition in memory (or on disk if large). Partitioning by user_id on a billion-row event table = fine (small partitions). Partitioning by country = danger (huge partitions). Look at EXPLAIN output for "Sort" and "WindowAgg" nodes.
• Dialect quirks. BigQuery has QUALIFY — a clean way to filter on window results: ... QUALIFY ROW_NUMBER() OVER (PARTITION BY user ORDER BY ts DESC) = 1. Snowflake also supports it. Postgres does not — you need the CTE wrapper.

The senior-engineer tell: whenever you see yourself writing a correlated subquery — stop, ask "is this a window function in disguise?", and 4 out of 5 times it is.

(e) Quiz + exercise · 10 min

1. What's the fundamental difference between GROUP BY AVG(x) and AVG(x) OVER (PARTITION BY …)?
2. Given salaries [100, 90, 90, 80], what do ROW_NUMBER, RANK, and DENSE_RANK return in that order?
3. Write the "top 3 orders per customer" pattern in SQL using a window function.
4. Why can't you write WHERE rn = 1 in the same SELECT as ROW_NUMBER() OVER (...) AS rn?
5. Explain what ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means, and give one business report where you'd use exactly this frame.
6. Stretch (connects to S038 Aggregations): rewrite this session's "top earner per dept" window query as a correlated subquery (like S038 Query 5). Which is more readable? Which is likely faster on 10M rows and why?

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

1. What is Window Functions? (one sentence, no jargon)
2. When would you use it? (one concrete example)
3. When would you NOT use it? (one anti-pattern)

What comes next

• Next session (S040): CTEs & Recursive Queries
• Previous (S038): Aggregations — GROUP BY, HAVING, Subqueries
• Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.