Lesson 6 of 14 · 7 min · Window functions and CTEs
Window functions: ROW_NUMBER, RANK, DENSE_RANK and partitions
What you will learn
- What a window function is and how it differs from
GROUP BY OVER,PARTITION BYandORDER BYinside a windowROW_NUMBER,RANKandDENSE_RANK, and how each handles ties- The top-N-per-group pattern
- Why you cannot use a window function in
WHERE
In simple words
GROUP BY squashes many rows into one row per group. Sometimes you want to keep every row and still see a group-level answer next to it, such as each employee's salary and their rank inside their department. A window function does that: for every row it looks at a "window" of related rows, calculates something, and puts the answer beside that row.
Words to know
- Window function: a calculation across a set of related rows that keeps every row.
- OVER: the keyword that turns a function into a window function.
- Partition: a group of rows the window works within, like a department.
- Tie: two rows with the same value in the sorting column.
Using the employees table
Use the employees table from the joins lesson:
| name | dept | salary |
|---|---|---|
| Neha | Engineering | 90000 |
| Arun | Engineering | 70000 |
| Sana | Engineering | 65000 |
| Ravi | Sales | 50000 |
| Meena | Sales | 55000 |
| Kiran | Sales | 55000 |
A plain aggregate with OVER () and no partition looks at the whole table but keeps every row:
SELECT name, salary, AVG(salary) OVER () AS company_avg FROM employees;
Every row shows the same average (64166.67). Compare with GROUP BY, which would return a single row.
PARTITION BY and ORDER BY
PARTITION BY splits the rows into groups; the function restarts in each. ORDER BY inside OVER sets the order the function counts in.
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense
FROM employees
ORDER BY dept, salary DESC, name;
name | dept | salary | rn | rnk | dense
-------+-------------+--------+----+-----+-------
Neha | Engineering | 90000 | 1 | 1 | 1
Arun | Engineering | 70000 | 2 | 2 | 2
Sana | Engineering | 65000 | 3 | 3 | 3
Kiran | Sales | 55000 | 1 | 1 | 1
Meena | Sales | 55000 | 2 | 1 | 1
Ravi | Sales | 50000 | 3 | 3 | 2
Look at Sales, where Kiran and Meena tie at 55000:
ROW_NUMBERgives unique numbers 1, 2, 3 and breaks ties arbitrarily (here Kiran got 1; add a tie-breaker column toORDER BYsuch as, nameto make it repeatable).RANKgives tied rows the same rank and then skips: 1, 1, 3.DENSE_RANKgives tied rows the same rank and does not skip: 1, 1, 2.
Rule of thumb: need exactly one row per rank → ROW_NUMBER; "top 3 salaries including ties, with gaps" → RANK; "Nth highest salary value" → DENSE_RANK.
Top N per group
You cannot write WHERE rn = 1 in the same query, because window functions are calculated after WHERE has run. Wrap the ranking in a subquery (or CTE) and filter outside:
SELECT name, dept, salary
FROM (SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, name) AS rn
FROM employees) AS ranked
WHERE rn = 1;
returns Neha (Engineering) and Kiran (Sales). Change to rn <= 2 for the top two in each department. This is one of the most common SQL interview patterns.
The Nth highest salary overall, handling ties correctly:
SELECT DISTINCT salary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS d FROM employees) t
WHERE d = 2;
returns 70000 (90000 is first; 70000 is second).
Other aggregates as windows
Any aggregate works as a window function. Share of department payroll:
SELECT name, dept, salary,
ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY dept), 1) AS pct_of_dept
FROM employees;
Neha's salary is 90000 out of Engineering's 225000, which is 40.0%. NTILE(4) splits rows into four equal buckets (quartiles), and PERCENT_RANK() gives relative standing.
Where window functions are allowed
Window functions can appear only in SELECT and ORDER BY. Not in WHERE, GROUP BY or HAVING, because they are calculated after those steps. That is why you wrap them in a subquery or CTE to filter by rank.
Common mistakes
WHERE ROW_NUMBER() ... = 1, which is an error. Use a subquery or CTE.- Choosing
RANKwhen you need unique rows, and getting two "first" rows. - No tie-breaker in
ORDER BY, makingROW_NUMBERresults change between runs. - Forgetting
PARTITION BY, so the ranking covers the whole table instead of each group.
Try it yourself
- Create
employeesand rank employees by salary within each department with all three functions. - Return the highest paid employee in each department.
- Return the second highest distinct salary using
DENSE_RANK. - Show each customer's orders numbered from newest to oldest (
PARTITION BY customer_id ORDER BY order_date DESC). - Show each employee's salary as a percentage of their department total.
Check yourself
How is a window function different from GROUP BY?
GROUP BY collapses rows into one per group. A window function keeps every row and adds a value calculated across a related set of rows.
Salaries 100, 90, 90, 80: what are the RANK and DENSE_RANK of 80?
RANK is 4 (it skips after the tie), DENSE_RANK is 3.
Why can't you filter on ROW_NUMBER() in WHERE?
Window functions are calculated after WHERE. Compute the rank in a subquery or CTE, then filter in the outer query.
What does PARTITION BY do?
It splits the rows into groups, and the window function restarts for each group.
Interview questions
Write a query for the top 3 highest-paid employees in each department.
Rank with DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) (or ROW_NUMBER if you want exactly three) in a subquery, then keep rows where the rank is at most 3.
How would you remove duplicate rows keeping one of each?
Number rows within each duplicate set with ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and delete or ignore rows where the number is above 1.
Difference between RANK, DENSE_RANK and ROW_NUMBER?
All number rows in order. ROW_NUMBER is always unique, RANK repeats on ties and skips numbers after, DENSE_RANK repeats on ties without skipping.
Summary
- A window function adds a group-aware value to each row without collapsing rows.
PARTITION BYsets the groups,ORDER BYsets the counting order.ROW_NUMBERis unique;RANKskips after ties;DENSE_RANKdoes not skip.- To filter by rank, calculate it in a subquery or CTE first.
Going deeper
- Window functions tutorial: the gentlest introduction in the PostgreSQL manual.
- Window function reference: all built-in window functions.
- Window function syntax:
OVER, partitions and frames. - MySQL window functions: MySQL 8 support.
See how much of this you can explain
A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.