Window Function Patterns

A handful of window recipes cover most of what the technique is used for. Learn these five shapes and you can adapt them to almost anything.

1. Top N per group

The pattern the whole module builds towards, and the most-asked window question in interviews. Rank within each group, then filter outside:

SQL
WITH ranked AS (
  SELECT dept_id, first_name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id) AS rn
  FROM employees
  WHERE salary IS NOT NULL
)
SELECT dept_id, first_name, salary
FROM ranked
WHERE rn <= 2
ORDER BY dept_id, rn;

The two highest-paid people in each department. Note the emp_id tiebreaker — without it, the department with two people on 88,000 would pick one arbitrarily, and possibly a different one on each run.

Before window functions this needed a correlated subquery counting how many colleagues earn more, which is both slower and much harder to read.

Choose the ranking function to match the question. ROW_NUMBER gives exactly two rows per department even on a tie. If ties should all be included, use RANK or DENSE_RANK:

SQL
WITH ranked AS (
  SELECT first_name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
  WHERE salary IS NOT NULL
)
SELECT first_name, salary, dr AS salary_rank
FROM ranked
WHERE dr <= 3
ORDER BY dr, first_name;

2. Deduplication

Number the rows within each duplicate group and keep the first:

SQL
WITH numbered AS (
  SELECT customer_id, name, email,
         ROW_NUMBER() OVER (PARTITION BY lower(name) ORDER BY customer_id) AS rn
  FROM customers
)
SELECT customer_id, name, email
FROM numbered
WHERE rn = 1
ORDER BY customer_id;

Five rows instead of six — the duplicate customer that differs only in capitalisation is gone, and the earliest id was kept.

Flip the condition to rn > 1 and you have the list of rows to remove:

SQL
WITH numbered AS (
  SELECT customer_id, name,
         ROW_NUMBER() OVER (PARTITION BY lower(name) ORDER BY customer_id) AS rn
  FROM customers
)
SELECT customer_id, name AS would_be_deleted
FROM numbered
WHERE rn > 1;

Always run the rn > 1 version and read it before deleting anything. The PARTITION BY defines what "duplicate" means, and getting that definition wrong is how a deduplication script removes real data. ORDER BY decides which row survives — oldest id, most recent update, most complete record.

3. First and last row per group

FIRST_VALUE and LAST_VALUE with an explicit frame, plus the named-window syntax that saves repeating it:

SQL
SELECT DISTINCT
       customer_id,
       FIRST_VALUE(order_id) OVER w AS first_order,
       LAST_VALUE(order_id)  OVER w AS latest_order
FROM orders
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY placed_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY customer_id;

The WINDOW clause names a window once and reuses it — worth knowing, though SQL Server does not support it and you repeat the OVER (…) there instead.

The unbounded frame is mandatory for LAST_VALUE, for the reason lesson 30 gave. An alternative that avoids the frame entirely: ROW_NUMBER() ascending and descending, then filter for rn = 1 on each.

4. Share of the group total

Each row against the total of its own partition:

SQL
SELECT dept_id,
       first_name,
       salary,
       ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY dept_id), 1)
         AS pct_of_dept_payroll
FROM employees
WHERE salary IS NOT NULL
ORDER BY dept_id, pct_of_dept_payroll DESC;

One pass, no subquery, no join. The 100.0 matters for the reason lesson 18 gave — integer division would return zeros.

5. Comparing against the group

Rows that stand out from their own partition, filtered outside the window:

SQL
WITH stats AS (
  SELECT dept_id, first_name, salary,
         ROUND(AVG(salary) OVER (PARTITION BY dept_id), 2) AS dept_avg
  FROM employees
  WHERE salary IS NOT NULL
)
SELECT dept_id, first_name, salary, dept_avg,
       ROUND(salary - dept_avg, 2) AS above_by
FROM stats
WHERE salary > dept_avg
ORDER BY above_by DESC;

The correlated-subquery version of this appeared in lesson 25. This does the same work in one pass over the table instead of one pass per row.

When not to use a window function

You want fewer rows — that is GROUP BY. A window keeps them all and you then have to filter.
A simple aggregate answers it — SELECT MAX(salary) FROM employees needs no window.
You are on MySQL 5.7 or older — none of this exists there.
The filter is on a plain column — filter in WHERE first; the window then runs over fewer rows.

That last point is a real performance lever: WHERE runs before window functions, so narrowing the input first means the window does less work.

Common mistakes

ROW_NUMBER where ties should be included — silently drops legitimate rows.
No tiebreaker — "top 2" returns a different pair between runs.
Deleting rn > 1 without reading it first — the PARTITION BY is the definition of duplicate, and it is easy to get wrong.
LAST_VALUE without the unbounded frame — returns the current row.
Filtering after the window when you could filter before — more rows through the window than necessary.

Interview question

Find the top 2 highest-paid employees in each department.

ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) inside a CTE, then WHERE rn <= 2. Two follow-ups usually come: why the CTE (you cannot filter on a window function in WHERE), and what happens on a tie (which is why the choice between ROW_NUMBER, RANK and DENSE_RANK is the real question).

Module complete

Window functions replace a large share of the self joins and correlated subqueries in older SQL, and they are the difference between an intermediate and an advanced answer in most interviews.

Still to come: indexes and query plans, transactions and isolation, schema design and normalisation, and the graded interview problem set.