SQL · Queries that survive scale

What are window functions and when should you use them?

Mediumwindow functionsanalyticspartitioningUpdated Oct 2026

Short answer

Window functions compute a value across a set of related rows without collapsing them. They are ideal for running totals, rankings, moving averages, and comparing a row with its group.

A window function uses an OVER clause to define a partition and an ordering. Unlike GROUP BY, every input row remains in the output, so detail and aggregate values can appear side by side.

sql

SELECT
  employee_id,
  department_id,
  salary,
  SUM(salary) OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_payroll
FROM employees;

The running total is computed per department while each employee row stays visible.

What the window controls

PARTITION BY splits rows into independent groups
ORDER BY defines the sequence within each partition
The frame clause controls which rows contribute to the current value
Without ORDER BY, the default frame covers the whole partition
Named windows with WINDOW can reuse the same definition

Ranking functions such as ROW_NUMBER, RANK, and DENSE_RANK are window functions. Aggregate functions such as SUM, AVG, and COUNT can also be used as window functions, which is often clearer than a correlated subquery.

Window functions run after WHERE, GROUP BY, and HAVING logically, so you cannot filter on a window result in WHERE. Wrap the query in a CTE or subquery and filter outside.

Follow-ups you should expect

Interviewers use these to check whether the first answer was understanding or memorisation.

1How is a window function different from GROUP BY?
2Why can you not filter a window result in WHERE?
3What does the frame clause change?
4How would you compute a seven-day moving average?

Say it in the interview

A window function computes across related rows without collapsing them, so detail rows and aggregates can appear together. I define the partition and ordering in OVER, and the frame controls which rows contribute. It is my preferred tool for running totals, rankings, and moving averages. Because windows are evaluated after WHERE and GROUP BY, I filter on their results in an outer query.

Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.

Now say it out loud.

Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.

Start a free SQL interview Browse all questions

What the window controls

Follow-ups you should expect

Related questions

Now say it out loud.