What are window functions in SQL?
Why Interviewers Ask This
Candidates at the intermediate level are expected to not only know this concept but explain the trade-offs involved. Interviewers use this question to see if you can reason about design decisions, not just recall facts.
Answer
Window functions perform calculations across a set of rows related to the current row (a "window") without collapsing rows like GROUP BY. They are applied with the OVER() clause. Unlike aggregates (which produce one row per group), window functions return a value for every row while still being able to look at related rows. Key window functions: ROW_NUMBER() — assigns a unique sequential number to each row within the partition (1, 2, 3...); RANK() — like ROW_NUMBER but ties get the same rank and the next rank is skipped (1, 1, 3); DENSE_RANK() — ties get the same rank, next rank is not skipped (1, 1, 2); LAG(col, n) / LEAD(col, n) — access the value of a column n rows before/after the current row; SUM() OVER(), AVG() OVER() — running totals/averages; NTILE(n) — divides rows into n equal buckets; FIRST_VALUE() / LAST_VALUE(). OVER() syntax: ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC). MySQL 8.0+ supports window functions fully.
Pro Tip
Demonstrate both theoretical understanding and practical experience. Say what it is, then give an example of how you actually used it in a MySQL / SQL codebase.