Window Functions

Window functions are the dividing line in SQL interviews. They compute a value **per row using a set of related rows**, without collapsing anything — which is the one thing `GROUP BY` cannot do. Top-N-per-group, running totals, and comparing a row to the previous one all become straightforward.

The anatomy

SELECT
    first_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Every employee row survives, and each carries its department's average alongside. With GROUP BY you would get one row per department and lose the names.

function() OVER (
    PARTITION BY  …   -- split into independent groups (optional)
    ORDER BY      …   -- order within each group (required for ranking/offset)
    ROWS/RANGE    …   -- which rows of the group to include (the frame)
)

An empty OVER () means the whole result set — COUNT(*) OVER () gives every row the total row count, which is handy for percentages.

The three ranking functions

They differ only on ties, and the difference is asked in almost every SQL interview:

SELECT
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees;

salary   row_num   rank   dense_rank
5000        1        1        1
5000        2        1        1
4000        3        3        2      ← rank skips 2, dense_rank does not
3000        4        4        3

• `ROW_NUMBER` — always distinct, ties broken arbitrarily. Use for deduplication and

pagination.

• `RANK` — ties share a rank, then it skips. Use for competition-style placings.
• `DENSE_RANK` — ties share a rank, no gaps. Use for "the Nth distinct salary".

Add NTILE(4) for quartiles, and PERCENT_RANK/CUME_DIST for distributions.

Top N per group

The single most common window-function question. You cannot filter on a window function in WHERE — it is computed after WHERE — so it must be wrapped:

WITH ranked AS (
    SELECT
        first_name, department, salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT first_name, department, salary
FROM ranked
WHERE rnk <= 3;

DENSE_RANK here because "the top 3 salaries" usually means three salary levels including ties; ROW_NUMBER gives exactly three rows. Ask which is wanted — noticing the ambiguity scores better than either answer alone.

Deduplication

ROW_NUMBER keeping only the first of each key is the standard duplicate removal idiom:

WITH numbered AS (
    SELECT id, email,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM numbered WHERE rn > 1);

LAG and LEAD

Reach into the previous or next row of the partition — the basis of every "compare to yesterday" question.

SELECT
    order_date,
    revenue,
    LAG(revenue)  OVER (ORDER BY order_date) AS prev_day,
    revenue - LAG(revenue) OVER (ORDER BY order_date) AS change,
    LEAD(revenue) OVER (ORDER BY order_date) AS next_day
FROM daily_sales;

Both take an optional offset and default: LAG(revenue, 7, 0) is the value seven rows back, or 0 at the start rather than NULL. Without the default, arithmetic on the first row of each partition yields NULL, which is usually the intended behaviour but should be said out loud.

FIRST_VALUE, LAST_VALUE and NTH_VALUE reach into fixed positions in the partition — though LAST_VALUE needs an explicit frame to do what people expect (see below).

Frames: running totals

Adding ORDER BY inside OVER changes the default frame from the whole partition to "everything up to the current row", which is exactly a running total:

SELECT
    order_date,
    revenue,
    SUM(revenue) OVER (ORDER BY order_date) AS running_total,
    AVG(revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales;

`ROWS` counts rows; `RANGE` counts values. With ties, the default RANGE frame

includes every peer row with the same ORDER BY value, which makes a "running total" jump at duplicates. If that surprises you, you wanted ROWS. This is also why LAST_VALUE(x) OVER (ORDER BY y) returns the current row rather than the partition's last — the default frame ends at the current row, and the fix is an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Where window functions can go

SELECT and ORDER BY only. Not WHERE, not GROUP BY, not HAVING — they are computed after all of those. Filtering on one always means a subquery or CTE.

Mistakes that cost the interview

• Filtering on a window function in `WHERE`. Wrap it.
• `RANK` where `DENSE_RANK` was meant, so "the 3rd highest salary" skips levels after

a tie.

• Forgetting `PARTITION BY`, ranking across the whole table instead of per group.
• `LAST_VALUE` without an explicit frame.
• Assuming `ROWS` when the default is `RANGE`, on data with ties.
• Using a window function where `GROUP BY` was enough — if you do not need the detail

rows, grouping is cheaper.

Practise these on the SQL sheet, then read gaps and islands for what LAG and ranking unlock together.