SQL-01-013-P · Primer — Window Functions
The idea in one sentence
A window function computes an aggregate-style calculation — a running total, a rank, an average — over a window of rows related to the current row, but unlike GROUP BY it hands the answer back on every original row instead of collapsing the group, so you get the summary and the raw detail side by side in one result set.
Why it exists
GROUP BY is powerful but it always destroys the rows it summarizes: ask for SUM(amount) per region and five sales rows fold down into two summary rows. That's exactly what you want for a dashboard, and exactly what you don't want the moment your question is "show me every sale, and next to each one its running total so far" or "rank each employee's salary within their department, but keep every employee visible." Those questions need the summary attached to the detail, not swapped in for it — and with only GROUP BY in hand, people resort to painful self-joins or correlated subqueries that are slow and easy to get wrong.
Window functions exist to close that gap. They let you say "compute this the way an aggregate would, over these related rows, but write the result onto each row without merging anything." Running totals, moving averages, "rank within group," "each row's share of its group's total," "difference from the previous row" — this whole family of everyday analytical questions becomes one clean clause. That's why window functions are the dividing line between beginner SQL and the SQL that shows up in real analytics, finance, and reporting work.
How it works
The magic is one keyword: OVER. Where a plain aggregate collapses rows, the same function followed by OVER (...) keeps every row and just looks around it. Inside the parentheses you describe the window with two optional pieces. PARTITION BY splits the rows into independent groups — like GROUP BY, but the rows survive — so PARTITION BY region restarts the calculation fresh for each region. ORDER BY (inside OVER) sorts the rows within each partition, which is what turns SUM into a running sum: it adds up rows from the start of the partition down to the current one. Ranking functions like ROW_NUMBER() and RANK() need that ordering to know who's first. Here's the whole idea in one runnable query — a running total and a rank, per region, with every original row intact:
CREATE TABLE sales (id INTEGER, region TEXT, day TEXT, amount INTEGER);
INSERT INTO sales VALUES
(1,'East','Mon',100),
(2,'East','Tue',300),
(3,'East','Wed',200),
(4,'West','Mon',250),
(5,'West','Tue',150);
SELECT
region, day, amount,
SUM(amount) OVER (PARTITION BY region ORDER BY day) AS running_total,
RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk
FROM sales
ORDER BY region, day;
-- region day amount running_total rnk
-- East Mon 100 100 3
-- East Tue 300 400 1
-- East Wed 200 600 2
-- West Mon 250 250 1
-- West Tue 150 400 2All five rows are still here — running_total climbs within each region and resets at the West boundary, while rnk orders each region's sales by size. No rows were harmed.
The one gotcha
ROW_NUMBER(), RANK(), and DENSE_RANK() are not interchangeable — they disagree exactly when there are ties, and picking the wrong one silently corrupts "top N" queries. ROW_NUMBER() hands out a strictly unique number (1, 2, 3, 4) even to rows with identical values, breaking ties arbitrarily — so WHERE rn = 1 can drop a legitimate co-winner and keep a coin-flip. RANK() gives tied rows the same rank and then skips the next values: two rows tied for 1st are both 1, and the next row is 3 (there is no 2). DENSE_RANK() also ties at 1 but does not skip — the next row is 2. On the values (300, 300, 200, 100) you get ROW_NUMBER → 1,2,3,4, RANK → 1,1,3,4, DENSE_RANK → 1,1,2,3. So "give me the single latest order per customer" wants ROW_NUMBER(); "give me everyone tied for the top score" wants RANK() = 1; "give me the top three distinct price tiers" wants DENSE_RANK() <= 3. Choose by asking one question: what should happen to ties? Getting that wrong produces results that look right on tie-free test data and quietly go wrong in production, where ties are everywhere.
See it — one window function, computed per row
flowchart TB
A["sales table<br/>5 rows (kept!)"] --> B["OVER clause"]
B --> C["PARTITION BY region<br/>split into East / West<br/>(rows survive)"]
C --> D["ORDER BY day<br/>sort within partition"]
D --> E["Compute per row<br/>SUM = start..current<br/>RANK = position"]
E --> F["Result: 5 rows<br/>detail + summary<br/>side by side"]
G["GROUP BY region<br/>would give 2 rows"] -. "collapses detail<br/>window does NOT" .-> F
The one-line takeaway
Add OVER (PARTITION BY ... ORDER BY ...) to an aggregate or ranking function and it computes across a related window of rows without collapsing them, so you keep every raw row and get its running total, rank, or group summary attached right alongside.
Where you'll meet it
You'll meet window functions the first time a "simple" report refuses to fit into GROUP BY: running totals on a bank statement, a leaderboard that ranks players but still shows all of them, "each product's sales as a percentage of its category total," month-over-month deltas, or the classic "keep only the most recent row per user." They're the backbone of analytics engineering — nearly every dbt model, cohort analysis, and financial time series leans on them — and they're a favorite interview filter precisely because they separate people who only know GROUP BY from people who can answer "per-row and per-group at once." Expect questions like "running total without a self-join?", "difference between RANK and DENSE_RANK?" (know the tie-skipping answer cold), and "deduplicate to the latest row per key" — which is ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) then keep = 1. Once OVER clicks, a whole tier of real-world SQL that used to require gnarly subqueries becomes a single, readable clause.
Named concepts: OVER clause, PARTITION BY, ROW_NUMBER and RANK, running totals vs GROUP BY