Window Functions

Explanation

A window function performs a calculation across a set of rows related to the current row, but — unlike GROUP BY — it does not collapse the rows. Every original row stays, with the computed value added as a new column.

That makes window functions perfect for:

• Ranking rows (ROW_NUMBER, RANK, DENSE_RANK)
• Running totals and moving averages (SUM(...) OVER (...))
• Comparing each row to its group's average

The OVER (...) clause defines the "window" (the set of rows) the function sees. PARTITION BY splits the window into groups; ORDER BY sets the row order inside it.

Syntax

SELECT
  col,
  AGGREGATE(col) OVER (PARTITION BY group_col ORDER BY order_col) AS calc
FROM table;

Interactive Example

Rank employees by salary within each department. Then show each employee's salary next to a running total ordered by salary.

Common Mistakes

• Confusing window ORDER BY with query ORDER BY. The one inside OVER defines how the window is processed; a trailing ORDER BY orders the final output.
• Expecting GROUP BY behavior. Window functions never reduce row count — if your result "doubled," you likely wanted a join, not a window.
• Forgetting PARTITION BY when you meant per-group. Without it, the window is the whole table.

Best Practices

• Use PARTITION BY to restart calculations per group (e.g., per department).
• Prefer ROW_NUMBER() when you need a unique rank; RANK() leaves gaps on ties.
• Combine with CTEs: compute the window in a CTE, then filter (e.g. WHERE dept_rank <= 3).

Practice Question

Using a window function, return each employee with their department_id and the average salary of their department shown alongside each row, ordered by department.

Summary

Window functions compute values across a row's "window" via OVER (PARTITION BY ... ORDER BY ...) while keeping every row. They deliver ranks, running totals, and per-group comparisons that GROUP BY alone cannot.

FAQ