Aggregation and window functions

Why it matters

"Second highest salary", "top three products per category", "running total by day", "month-over-month growth": these are the SQL questions interviews ask most, and each is a grouping or window problem. Knowing the logical order in which SQL evaluates clauses also explains the error messages you hit when you get it wrong, such as using an alias in WHERE or filtering an aggregate without HAVING.

The idea

Aggregation collapses many rows into one per group: GROUP BY defines the groups, aggregate functions (COUNT, SUM, AVG, MIN, MAX) summarise each, and HAVING filters groups. Window functions compute a value for every row from a set of related rows (its window) without collapsing them: each row keeps its identity and gains a computed column.

The logical order of a query

SQL is written in one order and evaluated in another. Knowing the evaluation order explains what each clause can see.

1. 1

   FROM and JOIN

   Build the combined rows.
2. 2

   WHERE

   Filter individual rows; aggregates and SELECT aliases do not exist yet.
3. 3

   GROUP BY

   Form groups.
4. 4

   HAVING

   Filter groups; aggregates are available.
5. 5

   SELECT

   Compute output columns and aliases; window functions run here, after grouping.
6. 6

   DISTINCT, ORDER BY, LIMIT

   De-duplicate, sort (aliases now visible) and cut.

So WHERE COUNT(*) > 5 is invalid (no groups yet; use HAVING), and filtering on a window function's result needs a subquery or CTE, because WHERE runs before SELECT. Every non-aggregated column in SELECT must appear in GROUP BY (or be functionally dependent on it).

Aggregates and NULL

Aggregates ignore NULLs, except COUNT(*), which counts rows. AVG(bonus) is the average among employees who have a bonus, not over all employees; use AVG(COALESCE(bonus, 0)) if missing means zero. COUNT(DISTINCT x) counts unique non-NULL values.

Window functions

A window function is written function() OVER (PARTITION BY ... ORDER BY ... frame). PARTITION BY splits rows into groups (like GROUP BY, but rows are not collapsed); ORDER BY orders rows within a partition; the frame (ROWS BETWEEN ...) chooses which neighbouring rows count.

Patterns worth memorising

-- top 3 earners per department
SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employee e
) t WHERE rn <= 3;

-- second highest distinct salary
SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employee
) t WHERE r = 2;

-- day-over-day change in revenue
SELECT day, revenue, revenue - LAG(revenue) OVER (ORDER BY day) AS change
FROM daily_revenue;

Choose ROW_NUMBER when exactly N rows per group are wanted, RANK or DENSE_RANK when ties should all be included. The default frame with ORDER BY is "from the start of the partition to the current row, including peers", which is why running totals work without an explicit frame, and why LAST_VALUE often surprises: it sees only up to the current row unless you widen the frame.

Trade-offs

GROUP BY is the right tool when you want one row per group; window functions when you want detail rows enriched with group or neighbour values, which replaces many self joins and correlated subqueries with a single pass. Window functions need a sort per partition ordering, which costs memory and time on large data; an index matching PARTITION BY and ORDER BY can avoid the sort.

In an interview

Name the function and the reason: "Top three per department with ROW_NUMBER partitioned by department and ordered by salary descending, filtered in an outer query because WHERE runs before window functions. If ties at third place should all appear, DENSE_RANK instead." Mentioning the logical query order when explaining why the outer query is needed shows real understanding.