What are Window Functions in SQL?
Understand SQL window functions — the OVER clause, PARTITION BY, running totals, and rankings — and how they differ from GROUP BY, with clear query examples.
Expected Interview Answer
Window functions perform calculations across a set of rows related to the current row — a 'window' — without collapsing those rows into a single output like GROUP BY does.
Defined with the OVER clause, a window function keeps every row while adding aggregate, ranking, or offset calculations computed over a partition of the data. PARTITION BY splits rows into groups, ORDER BY defines the ordering within each partition, and an optional frame (ROWS/RANGE) narrows which rows the function sees. Common examples include SUM() OVER, AVG() OVER, ROW_NUMBER(), RANK(), LAG(), and LEAD(), enabling running totals, moving averages, rankings, and row-to-row comparisons.
- Keeps individual rows while adding aggregate context
- Enables running totals and moving averages
- Powers rankings without self-joins
- Allows row-to-row comparison with LAG/LEAD
- Cleaner and faster than correlated subqueries
AI Mentor Explanation
A batter's running strike rate is shown after every ball while each delivery still appears on the scorecard. The window function is that live per-ball tally — it looks back over the innings so far (the partition) and recomputes the average, yet never merges the individual balls into one summary row.
Step-by-Step Explanation
Step 1
Add the OVER clause
Turn an aggregate or ranking function into a window function by appending OVER (...) instead of using GROUP BY.
Step 2
Partition the rows
Use PARTITION BY to split rows into independent groups the function resets over, like per department or per region.
Step 3
Order within the partition
Add ORDER BY inside OVER to define the sequence — required for ranking, LAG/LEAD, and running totals.
Step 4
Define the frame (optional)
Use ROWS or RANGE to limit which rows the function sees, e.g. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a moving average.
Step 5
Keep all rows
Unlike GROUP BY, the result preserves every input row and simply attaches the computed value to each one.
What Interviewer Expects
- Explains window functions do not collapse rows like GROUP BY
- Knows the OVER clause with PARTITION BY and ORDER BY
- Can name examples like ROW_NUMBER, RANK, SUM OVER, LAG, LEAD
- Understands frames (ROWS/RANGE) for moving windows
- Gives a real use case such as running total or ranking
Common Mistakes
- Confusing window functions with GROUP BY aggregation
- Forgetting ORDER BY inside OVER for ranking or running totals
- Trying to use a window function directly in a WHERE clause
- Omitting PARTITION BY when the calculation should reset per group
- Not defining a frame when a moving average is intended
Best Answer (HR Friendly)
“Window functions let you calculate things like running totals or rankings across a group of rows while still showing every individual row. Unlike a normal grouped summary that squashes many rows into one, they add the calculated value beside each row, which is great for reports and analytics.”
Code Example
-- Running total of sales per region, ordered by date
SELECT
region,
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY region
ORDER BY sale_date
) AS running_total
FROM sales;
-- Rank employees by salary within each department
SELECT
name,
department,
salary,
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank
FROM employees;Follow-up Questions
- How do window functions differ from GROUP BY?
- What does PARTITION BY do inside the OVER clause?
- How would you compute a 7-day moving average?
- Why can't you filter on a window function in WHERE?
- What is the difference between ROWS and RANGE frames?
MCQ Practice
1. How do window functions differ from GROUP BY aggregates?
Window functions keep all input rows and attach the calculation to each, unlike GROUP BY which collapses groups into single rows.
2. Which clause defines a window function?
The OVER clause defines the window; PARTITION BY and ORDER BY appear inside it.
3. What does PARTITION BY do?
PARTITION BY divides rows into independent groups so the window function restarts its calculation per group.
Flash Cards
What is a window function? — A function that computes over a set of rows related to the current row without collapsing them, via the OVER clause.
What does PARTITION BY do? — Splits rows into groups the window function resets over, similar to grouping but without collapsing rows.
Name three window functions — ROW_NUMBER(), RANK(), and SUM() OVER — plus LAG() and LEAD() for row-to-row offsets.
What is a window frame? — A ROWS or RANGE clause that limits which rows the function sees, e.g. for a moving average.