What are Window Functions in SQL?
Learn what SQL window functions are, how the OVER clause works, and why they preserve rows unlike GROUP BY aggregation.
Expected Interview Answer
A window function computes a value across a set of related rows (its window) for each row individually, without collapsing those rows into a single output row the way a GROUP BY aggregate does.
Every window function is written with an OVER clause, which defines the window: an optional PARTITION BY to group rows, an optional ORDER BY to sequence them, and an optional frame clause to bound how many neighboring rows are visible. Because each input row keeps its own output row, window functions let you show a detail row side by side with a computed value like a running total, a rank, or the previous row's value. This makes them the standard tool for analytics such as rankings, moving averages, and period-over-period comparisons that plain aggregation cannot express in one pass.
- Keeps one output row per input row
- Supports ranking, running totals, and lead/lag in a single query
- Avoids self-joins and correlated subqueries for analytics
- PARTITION BY resets calculations per group
AI Mentor Explanation
Picture a scorer who, ball by ball, writes each delivery on its own line but also pencils in the running total of runs scored in that over next to it, without erasing any individual ball. Every ball keeps its own row, yet each row also carries a computed value based on the balls around it. A window function does exactly this: it keeps every input row intact while attaching a value computed over a defined window of neighboring rows.
Step-by-Step Explanation
Step 1
Choose the window function
Pick a ranking, offset, or aggregate function such as RANK, LAG, or SUM to compute per row.
Step 2
Write the OVER clause
Attach OVER() to the function to mark it as a window function rather than a plain aggregate.
Step 3
Define PARTITION BY (optional)
Group rows so the calculation restarts for each partition, such as per department or per customer.
Step 4
Define ORDER BY and frame (optional)
Order rows within the window and, if needed, bound the frame of rows visible to the calculation.
What Interviewer Expects
- Clear distinction between window functions and GROUP BY aggregation
- Correct use of PARTITION BY and ORDER BY inside OVER()
- Awareness that window functions preserve one row per input row
- A concrete example such as a running total or rank per partition
Common Mistakes
- Believing window functions collapse rows the way GROUP BY does
- Forgetting ORDER BY inside OVER() when the function needs row sequencing
- Confusing PARTITION BY with GROUP BY semantics
- Not knowing window functions run after WHERE and GROUP BY in query evaluation order
Best Answer (HR Friendly)
“A window function lets you calculate things like a running total or a rank for each row while still showing every individual row, instead of collapsing everything into one summary row like a normal GROUP BY would. It is written with an OVER clause that defines which rows belong to that calculation.”
Code Example
SELECT
employee_id,
department,
salary,
SUM(salary) OVER (PARTITION BY department ORDER BY employee_id) AS running_total
FROM Employees;
-- Every employee row is preserved; running_total accumulates
-- only within that employee's department.Follow-up Questions
- How is a window function different from a GROUP BY aggregate?
- What does PARTITION BY do inside an OVER clause?
- When in query execution order do window functions run?
- Can you use a window function result directly inside a WHERE clause?
MCQ Practice
1. What is the key difference between a window function and a GROUP BY aggregate?
A window function computes a value per row while preserving every row, unlike GROUP BY which collapses rows into one per group.
2. Which clause marks a function as a window function in SQL?
The OVER clause defines the window (partition, order, and frame) and is what distinguishes a window function call.
3. What does PARTITION BY do inside an OVER clause?
PARTITION BY divides rows into groups so the window function computes independently within each group.
Flash Cards
What is a window function? — A function that computes a value across a set of related rows while keeping one output row per input row.
What clause defines a window? — The OVER clause, which can include PARTITION BY, ORDER BY, and a frame specification.
Window function vs GROUP BY? — GROUP BY collapses rows into one per group; a window function preserves every row and attaches a computed value.
What does PARTITION BY do? — Groups rows so the window calculation resets and runs independently within each partition.