Detailed explanation
A window function calculates a value using rows related to the current row while preserving individual rows in the query result. This related set of rows is called a window and is defined using the OVER clause. Unlike GROUP BY, which reduces each group to one output row, a window function attaches a calculated value to each row. This makes it useful when you need both detailed records and group-level comparisons, such as showing each employee’s salary alongside their department’s average salary.
In the example SELECT name, dept_id, salary, AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg FROM employees, PARTITION BY separates employees into department-specific windows. AVG calculates the average of non-NULL salaries within each department, and that average appears beside every employee in the same department. There is no window ORDER BY here, so the aggregate uses the entire partition. Omitting PARTITION BY would make all rows available to the window function one partition.
Window definitions can also include ORDER BY and a frame clause. ORDER BY determines the sequence used for ranking, offsets, or cumulative calculations; it does not guarantee the final output order. A frame selects a subset within the partition for functions that use frames. For example, SUM(salary) OVER (PARTITION BY dept_id ORDER BY employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) produces a running total. Explicit frames matter because an ordered aggregate’s default frame can include tied ordering values together rather than advancing one physical row at a time.
Common window functions include ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and windowed aggregates such as SUM and AVG. Logically, window calculations occur after WHERE filtering, grouping, and HAVING, so their results generally cannot be filtered directly in WHERE at the same query level. Use a CTE or subquery, or QUALIFY where supported. Interviewers typically check whether you understand preserved row granularity, partitioning versus ordering, tie handling, and frame behaviour. Execution may require sorting and buffering, so performance depends on the database, data size, and execution plan.
Key points to remember
- OVER identifies a window calculation without collapsing its input rows.
- PARTITION BY defines independent groups; omitting it creates one partition.
- Window ORDER BY controls calculation order, not final result ordering.
- Frames control which partition rows contribute to frame-aware calculations.
- Use windows for rankings, running totals, previous-row comparisons, and group averages.
Common mistakes candidates make
- Confusing PARTITION BY with GROUP BY and expecting one output row per partition.
- Ignoring ordering ties or default frames, causing unexpected rankings or running totals.
- Trying to reference a window result directly in WHERE at the same query level.
Likely follow-up questions
How do ROW_NUMBER, RANK, and DENSE_RANK differ?
ROW_NUMBER assigns distinct sequential numbers, while RANK gives ties the same rank and leaves gaps afterward. DENSE_RANK also gives ties the same rank but leaves no gaps; use a unique tie-breaker when you need deterministic ROW_NUMBER assignments.
What is the difference between ROWS and RANGE?
ROWS defines frame boundaries using row positions within the ordered partition, whereas RANGE uses ordering values. With RANGE ending at CURRENT ROW, rows tied on the ordering values are included together; ROWS can advance one row at a time.
How would you find the top three employees by salary in each department?
Calculate ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, employee_id) in a CTE, then filter for row numbers up to three in the outer query. If ties should be included, choose RANK or DENSE_RANK based on whether you want rank gaps or the top three distinct salary levels.
Interview tip: Start with the contrast to GROUP BY, explain the department-average example, and then mention ordering, frames, and one practical use case.