What are window functions? | Interview Question

Q: What are window functions?

**Target Audience:** Beginners who know `GROUP BY` and want to understand the `OVER (...)` syntax from zero, plus candidates who want the precise vocabulary (partition, frame, peers). **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL facts are cited from its documentation. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First A teacher has a stack of marked exam papers. The head asks two different questions: 1. "What was the average mark for each class?" The teacher writes one line per class on a sticky note. The papers themselves are no longer in the answer; they have bee

The problem

Question presented to candidate: "What is a window function in SQL? How is it different from a normal aggregate with GROUP BY, and what do PARTITION BY and ORDER BY inside the OVER clause actually do?"

What a strong answer should cover:

A window function computes a value for each row from a set of related rows (the window) and, unlike GROUP BY, does not collapse the rows: every input row stays in the result
The OVER (...) clause defines the window: PARTITION BY splits rows into groups, ORDER BY orders rows inside each partition, and an optional frame (ROWS / RANGE / GROUPS) picks which rows around the current one are used
The families: ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE), offset (LAG, LEAD, FIRST_VALUE), and ordinary aggregates used with OVER (SUM, AVG, COUNT)
Window functions are evaluated after WHERE, GROUP BY and HAVING, so they cannot be used in WHERE; filter on them in an outer query or CTE
Engine support: PostgreSQL since 8.4, MySQL since 8.0, SQLite since 3.25.0; adding ORDER BY to OVER silently changes an aggregate into a running one

Code / implementation expected: Yes — a query that shows a per-row value next to a per-group total, and a filter on a window result via a subquery.

Schema used by the examples:

window-functions

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know GROUP BY and want to understand the OVER (...) syntax from zero, plus candidates who want the precise vocabulary (partition, frame, peers). Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL facts are cited from its documentation.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

A teacher has a stack of marked exam papers. The head asks two different questions:

"What was the average mark for each class?" The teacher writes o

The solution is hidden so you can try first

14 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLEach employee next to the department total and their share of it (no rows collapsed)

SQLORDER BY inside OVER: a numbering, a running sum and a whole-table average in one pass

SQLFilter on a window result: wrap it in a subquery (it cannot go in WHERE)

PostgreSQL: the plan shows a Sort feeding a WindowAgg node

EXPLAIN (COSTS OFF)
SELECT name, dept, salary,
       SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM employees;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.