Window Functions

Window functions are the feature that separates SQL beginners from SQL professionals — and the one people are most surprised exists. They let you compute across a set of rows related to the current row (a running total, a rank, "each row versus its group's average") without collapsing those rows into one. That last part is the magic: unlike GROUP BY, a window function keeps every row and adds the computed value alongside.

GROUP BY collapses; windows annotate

GROUP BY answers "the average salary per department" by returning one row per department — the individual employees vanish. But "each employee, and their department's average, and their rank within it" needs every employee row kept, each annotated with group-level facts. That's impossible with GROUP BY and trivial with a window:

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg,
       RANK()      OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Every employee row survives, now carrying its department's average and its rank within the department. The OVER (...) clause is what makes an aggregate a window function — it says "compute this over a window of related rows, but don't collapse them."

Anatomy of OVER

FUNCTION() OVER (PARTITION BY <cols> ORDER BY <cols>)

• PARTITION BY — divide rows into groups (like GROUP BY, but the rows stay). Omit it and the whole result is one window.
• ORDER BY (inside OVER) — order rows within each partition. Essential for ranking and running totals, where "which row am I relative to the others" is the whole point.

The functions you'll actually use

Ranking — the reason most people first reach for windows:

ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)  -- 1,2,3,4… always unique
RANK()       OVER (...)                                      -- ties share a rank, then GAPS (1,1,3)
DENSE_RANK() OVER (...)                                      -- ties share a rank, NO gaps (1,1,2)

The three differ only on ties, and choosing wrong is a classic bug: ROW_NUMBER breaks ties arbitrarily but guarantees distinct numbers (perfect for "top 3 per group" — your exercise); RANK leaves gaps after ties (two 1st places, then 3rd); DENSE_RANK doesn't. Know which your question wants.

Aggregates as windows — any aggregate (SUM, AVG, COUNT, MIN, MAX) works with OVER, giving per-group values on every row, or running totals when combined with an inner ORDER BY:

SUM(amount) OVER (ORDER BY date)                    -- running total
SUM(amount) OVER (PARTITION BY user ORDER BY date)  -- running total per user

Navigation — peek at other rows:

LAG(salary)  OVER (ORDER BY hire_date)   -- the previous row's value
LEAD(salary) OVER (ORDER BY hire_date)   -- the next row's value

LAG/LEAD compute row-to-row changes (this month vs last month) without a self-join — another "I thought I needed two copies of the table" problem that windows dissolve.

The "top N per group" pattern

The single most common window recipe, and your exercise, has a required shape: window functions can't go in a WHERE (they're computed after WHERE, in the SELECT phase), so you rank in a CTE, then filter the rank outside:

WITH ranked AS (
    SELECT name, department, salary,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, salary DESC;

This "rank in a CTE, filter outside" is the canonical answer to every top-N-per-category question, and it combines this lesson with the last one. Memorize the shape.

Your exercise: Top 3 in Each Department

Exactly the pattern above: ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) in a CTE, WHERE rn <= 3 in the outer query, output name|department|salary ordered by department then salary descending. The graded traps are the window essentials: PARTITION BY department (so ranking restarts per department — forget it and you rank across the whole company), ORDER BY salary DESC inside the OVER (so rank 1 is the highest paid), and the filter living outside the CTE (windows can't be used in WHERE directly). Get this one working and you own the technique that shows up in every analytics interview and every real reporting query.