English for SQL Window Functions

Window functions let you compute a running total or a rank without collapsing rows the way GROUP BY does, but the vocabulary — partition, frame, ranking function — trips up a lot of otherwise strong SQL writers when they first need to explain what a query is doing.

Key Vocabulary

Window function — a function that computes a value across a set of related rows without collapsing them into one row per group, unlike an aggregate function used with GROUP BY. “We needed each order row to show its own running total, so a window function was the right tool — GROUP BY would have collapsed the rows we needed to keep.”

Partition — the PARTITION BY clause that divides rows into independent groups for the window function to operate within, restarting the calculation for each group. “Partitioning by customer ID means the running total resets for each customer, instead of accumulating across the entire result set.”

Frame — the specific subset of rows within a partition that a window function considers for each row’s calculation, defined with ROWS BETWEEN or RANGE BETWEEN, controlling things like a trailing 7-day sum. “The frame is ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, which gives us a 7-row trailing window for the moving average.”

Ranking function — a category of window functions (ROW_NUMBER, RANK, DENSE_RANK) that assign an ordinal position to each row within its partition, differing in how they handle ties. “RANK leaves gaps after ties — two rows tied for first both get rank 1, and the next row gets rank 3 — while DENSE_RANK would give it rank 2 instead.”

ORDER BY (within OVER) — the ordering clause inside a window function’s OVER clause that determines the sequence rows are processed in for ranking, running totals, or frame calculations, distinct from the query’s outer ORDER BY. “The ORDER BY inside the OVER clause determines the order the running total accumulates in — it’s independent of how the final result set gets sorted.”

Common Phrases

• “Do we need this partitioned by customer, or should it run across the whole result set?”
• “What’s the frame here — is it a trailing window, or the whole partition?”
• “Should we use RANK or DENSE_RANK — does this need gaps after ties or not?”
• “Is that ORDER BY inside the window function, or the query’s final sort?”
• “This should be a window function, not a self-join — we don’t want to collapse the rows.”

Example Sentences

Explaining a query choice in a design review: “We’re using ROW_NUMBER() partitioned by user ID and ordered by timestamp descending, so we can grab just the latest event per user without a separate subquery per user.”

Debugging an incorrect ranking result: “The rankings looked wrong because we forgot the partition clause — it was ranking every row against the entire table instead of within each region separately.”

Describing a moving-average calculation: “The frame is set to the current row and the six preceding rows, giving us a proper 7-day trailing average instead of an average over the whole dataset.”

Professional Tips

• Say window function, not “aggregate,” when the goal is per-row output alongside a calculation — conflating the two in a discussion usually means someone will reach for GROUP BY and lose rows they needed.
• Always state the partition clause explicitly when describing a window query — “ranked” or “running total” is ambiguous until you say what it’s scoped to.
• Specify the frame precisely (trailing N rows, whole partition, etc.) when discussing moving averages or running totals — the default frame isn’t always what people assume.
• Choose the right ranking function deliberately and name the reason — RANK, DENSE_RANK, and ROW_NUMBER handle ties differently, and picking the wrong one silently changes results.

Practice Exercise

1. Write a sentence explaining the difference between GROUP BY and a window function.
2. Explain what a frame controls in a window function.
3. Describe the difference between RANK and DENSE_RANK on tied rows.