Chapter 1 · Advanced Queries
Window Functions I: PARTITION BY, ROW_NUMBER and RANK
- Page 2 of 22
- 15 min read
GROUP BY answers "what is the average salary per department?" by collapsing each department into one row. Often you want both at once: every employee, and their department's average next to them; every product, and its rank inside its category. A window function does exactly that. It computes a value over a set of related rows (the window) and attaches the result to each row, without collapsing anything.
Windows are among the most useful tools in data work: keep only the latest record per user before building features, deduplicate scraped documents before embedding them, take the top 3 retrieved chunks per source document, pick the best run per experiment from an evaluation log. This page covers the ranking half; the next page covers running totals, LAG and frames.
What you will learn
- How a window differs from
GROUP BY, and howOVER(),PARTITION BYandORDER BYdefine it. ROW_NUMBER,RANK,DENSE_RANKandNTILE, and how they treat ties.- Top-N per group, and the latest row per entity.
- Deduplicating rows with
ROW_NUMBER.
Windows versus GROUP BY
The same question two ways. With GROUP BY you get one row per department:
SELECT department, ROUND(AVG(salary), 0) AS dept_avg
FROM employees
GROUP BY department
ORDER BY department;+-------------+----------+
| department | dept_avg |
+-------------+----------+
| Data | 121667.0 |
| Engineering | 140000.0 |
| Management | 250000.0 |
+-------------+----------+With a window, every employee stays, and the department average is added to each row:
SELECT name,
department,
salary,
ROUND(AVG(salary) OVER (PARTITION BY department), 0) AS dept_avg,
salary - ROUND(AVG(salary) OVER (PARTITION BY department), 0) AS vs_avg,
ROUND(100.0 * salary / SUM(salary) OVER (), 1) AS pct_of_payroll
FROM employees
ORDER BY department, salary DESC, employee_id;+-----------------+-------------+--------+----------+----------+----------------+
| name | department | salary | dept_avg | vs_avg | pct_of_payroll |
+-----------------+-------------+--------+----------+----------+----------------+
| Lamia Karim | Data | 160000 | 121667.0 | 38333.0 | 15.5 |
| Priya Sen | Data | 110000 | 121667.0 | -11667.0 | 10.6 |
| Shuvo Roy | Data | 95000 | 121667.0 | -26667.0 | 9.2 |
| Hasan Mahmud | Engineering | 180000 | 140000.0 | 40000.0 | 17.4 |
| Nusrat Jahan | Engineering | 120000 | 140000.0 | -20000.0 | 11.6 |
| Zahid Hasan | Engineering | 120000 | 140000.0 | -20000.0 | 11.6 |
| Ayesha Siddiqua | Management | 250000 | 250000.0 | 0.0 | 24.2 |
+-----------------+-------------+--------+----------+----------+----------------+What to notice:
OVER (PARTITION BY department)means "compute over the rows of the same department". The window is like aGROUP BYgroup that does not collapse.OVER ()with nothing inside means "over all rows":SUM(salary) OVER ()is the whole payroll, available on every row, so a percentage of total needs no subquery.- Ordinary aggregates (
SUM,AVG,COUNT,MIN,MAX) all work as window functions when you addOVER.
The shape of a window function
RANK() OVER ( PARTITION BY category_id ORDER BY price DESC )
| | |
| | +-- order inside each window: who is 1st, 2nd …
| +-- split rows into independent windows (optional; none = one window)
+-- what to compute for each rowWindow functions run late in the logical order of a query: after FROM, WHERE, GROUP BY and HAVING, together with SELECT, and before ORDER BY and LIMIT. Two consequences you will meet below: you can rank the result of a GROUP BY, and you cannot filter on a window function in WHERE.
Support: SQLite 3.25+, MySQL 8.0+, PostgreSQL 8.4+, SQL Server (ranking functions since 2005, the rest since 2012). MySQL 5.7 has none, and it is still common on old hosting.
ROW_NUMBER, RANK and DENSE_RANK
All three number the rows of a window in the ORDER BY order. They differ only when two rows tie. Nusrat and Zahid both earn 120,000:
SELECT name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY salary DESC, employee_id;+-----------------+--------+---------+-----+-----------+
| name | salary | row_num | rnk | dense_rnk |
+-----------------+--------+---------+-----+-----------+
| Ayesha Siddiqua | 250000 | 1 | 1 | 1 |
| Hasan Mahmud | 180000 | 2 | 2 | 2 |
| Lamia Karim | 160000 | 3 | 3 | 3 |
| Nusrat Jahan | 120000 | 4 | 4 | 4 |
| Zahid Hasan | 120000 | 5 | 4 | 4 |
| Priya Sen | 110000 | 6 | 6 | 5 |
| Shuvo Roy | 95000 | 7 | 7 | 6 |
+-----------------+--------+---------+-----+-----------+| Function | On a tie | Use it for |
|---|---|---|
ROW_NUMBER() | Different numbers anyway (1, 2, 3, 4, 5); you must decide who goes first with a tie-breaker | Exactly one row per group: latest record, deduplication, pagination |
RANK() | Same number, then a gap (4, 4, 6): "two people share 4th place, nobody is 5th" | Competition-style ranking, "top 3 including ties" |
DENSE_RANK() | Same number, no gap (4, 4, 5) | "The 2nd-highest distinct salary", price levels, tiers |
The employee_id tie-breaker in ROW_NUMBER matters. Without it, the database may give Nusrat 4 today and Zahid 4 tomorrow, and any result built on "row number 1" changes between runs. Always end a ROW_NUMBER ordering with a unique column.
PARTITION BY: ranking inside groups
Add PARTITION BY and the numbering restarts in each group. Products ranked by price within their category:
SELECT COALESCE(c.name, 'Uncategorised') AS category,
p.name,
p.price,
RANK() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS price_rank
FROM products AS p
LEFT JOIN categories AS c ON c.category_id = p.category_id
ORDER BY category, price_rank, p.product_id;+---------------+-----------------------------+-------+------------+
| category | name | price | price_rank |
+---------------+-----------------------------+-------+------------+
| Accessories | USB-C Hub | 2200 | 1 |
| Accessories | Laptop Stand | 1500 | 2 |
| Books | Hands-On Machine Learning | 2500 | 1 |
| Books | Python Crash Course | 1200 | 2 |
| Courses | AI Engineering Bootcamp | 15000 | 1 |
| Courses | SQL Masterclass | 3000 | 2 |
| Electronics | 27-inch Monitor | 18500 | 1 |
| Electronics | Noise-Cancelling Headphones | 7500 | 2 |
| Electronics | Mechanical Keyboard | 4500 | 3 |
| Electronics | Wireless Mouse | 900 | 4 |
| Uncategorised | Gift Card | 1000 | 1 |
+---------------+-----------------------------+-------+------------+The Gift Card has no category, so its category_id is NULL. Window partitions treat all NULLs as one group (like GROUP BY does), so it gets its own window and rank 1.
Top-N per group
"The two best-selling products in each category" is a classic interview question and an everyday task. The natural first attempt (shown here on price) fails:
SELECT name, category_id,
RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rnk
FROM products
WHERE rnk <= 2;Error: misuse of aliased window function rnkWHERE runs before any window is computed, so a window function (or its alias) cannot be used there; PostgreSQL and MySQL refuse it too, with their own messages. Compute the rank in a CTE (or subquery), then filter outside:
WITH product_revenue AS (
SELECT p.product_id, p.name, c.name AS category,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN products AS p ON p.product_id = oi.product_id
JOIN categories AS c ON c.category_id = p.category_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY p.product_id, p.name, c.name
),
ranked AS (
SELECT *,
RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
FROM product_revenue
)
SELECT category, rnk, name, revenue
FROM ranked
WHERE rnk <= 2
ORDER BY category, rnk, product_id;+-------------+-----+---------------------------+---------+
| category | rnk | name | revenue |
+-------------+-----+---------------------------+---------+
| Accessories | 1 | Laptop Stand | 4500 |
| Accessories | 2 | USB-C Hub | 4400 |
| Books | 1 | Hands-On Machine Learning | 5000 |
| Books | 2 | Python Crash Course | 3600 |
| Courses | 1 | AI Engineering Bootcamp | 15000 |
| Courses | 2 | SQL Masterclass | 6000 |
| Electronics | 1 | 27-inch Monitor | 18500 |
| Electronics | 2 | Mechanical Keyboard | 8550 |
+-------------+-----+---------------------------+---------+Use RANK when ties should all be included (two products tied for 2nd both appear) and ROW_NUMBER when you need exactly N rows per group. PostgreSQL 13+ offers FETCH FIRST n ROWS WITH TIES (SQL Server: TOP n WITH TIES) for a single top-N, but not per group, so this CTE pattern is the portable one.
The latest row per entity
The same pattern with ROW_NUMBER and a descending date gives each customer's most recent order. This is how you get the "current" state of anything stored as history: a user's latest plan, a model's latest evaluation, a document's latest version.
WITH ordered AS (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC) AS rn
FROM orders AS o
)
SELECT c.name, ordered.order_id, ordered.order_date, ordered.status
FROM ordered
JOIN customers AS c ON c.customer_id = ordered.customer_id
WHERE rn = 1
ORDER BY ordered.order_date DESC, ordered.order_id;+-----------------+----------+------------+-----------+
| name | order_id | order_date | status |
+-----------------+----------+------------+-----------+
| Farhana Akter | 14 | 2026-06-09 | delivered |
| Imran Hossain | 13 | 2026-06-02 | pending |
| Tanvir Ahmed | 12 | 2026-05-21 | shipped |
| Mitu Das | 11 | 2026-05-06 | delivered |
| Nadia Rahman | 10 | 2026-04-19 | delivered |
| Sadia Chowdhury | 7 | 2026-03-15 | delivered |
| Rafiq Islam | 5 | 2026-02-25 | cancelled |
+-----------------+----------+------------+-----------+Seven customers, seven rows; Karim, who never ordered, is absent because he has no rows in orders. The query runs unchanged on the servers:
-- PostgreSQL
WITH ordered AS (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC) AS rn
FROM orders AS o
)
SELECT customer_id, order_id, order_date
FROM ordered
WHERE rn = 1 AND customer_id <= 3
ORDER BY customer_id;+-------------+----------+------------+
| customer_id | order_id | order_date |
+-------------+----------+------------+
| 1 | 10 | 2026-04-19 |
| 2 | 12 | 2026-05-21 |
| 3 | 14 | 2026-06-09 |
+-------------+----------+------------+NTILE: equal-sized buckets
NTILE(n) splits the ordered rows of a window into n buckets of (almost) equal size and returns the bucket number. Products into four price tiers:
SELECT name,
price,
NTILE(4) OVER (ORDER BY price DESC, product_id) AS price_tier
FROM products
ORDER BY price DESC, product_id;+-----------------------------+-------+------------+
| name | price | price_tier |
+-----------------------------+-------+------------+
| 27-inch Monitor | 18500 | 1 |
| AI Engineering Bootcamp | 15000 | 1 |
| Noise-Cancelling Headphones | 7500 | 1 |
| Mechanical Keyboard | 4500 | 2 |
| SQL Masterclass | 3000 | 2 |
| Hands-On Machine Learning | 2500 | 2 |
| USB-C Hub | 2200 | 3 |
| Laptop Stand | 1500 | 3 |
| Python Crash Course | 1200 | 3 |
| Gift Card | 1000 | 4 |
| Wireless Mouse | 900 | 4 |
+-----------------------------+-------+------------+Eleven rows do not divide by four (11 = 4 × 2 + 3), so the first three buckets get one extra row each (3, 3, 3, 2); with fewer rows than buckets, the last bucket numbers are simply never used. NTILE cuts by row count, not by value: two products with the same price can land in different tiers if the cut falls between them. In ML work this is quantile binning, a way to turn a skewed number such as income or price into a few ordered categories; for strict value thresholds use CASE WHEN instead.
Deduplication with ROW_NUMBER
Imported data is full of duplicates: a form submitted twice, the same person signing up from the web and the app, a scraper that saw a page twice. Duplicates in training data make a model over-weight those examples, and duplicates that land in both the training and the test set make evaluation look better than it is. Here is a small signup import with both kinds:
CREATE TABLE signups (
signup_id INTEGER PRIMARY KEY,
email TEXT NOT NULL,
source TEXT NOT NULL,
signed_up TEXT NOT NULL
);
INSERT INTO signups (signup_id, email, source, signed_up) VALUES
(1, 'nadia@example.com', 'web', '2026-03-01'),
(2, 'tanvir@example.com', 'web', '2026-03-02'),
(3, 'Nadia@Example.com', 'app', '2026-03-05'),
(4, 'rafiq@example.com', 'web', '2026-03-02'),
(5, 'rafiq@example.com', 'web', '2026-03-02'),
(6, 'mitu@example.com', 'app', '2026-03-07');
SELECT signup_id, email, signed_up,
ROW_NUMBER() OVER (PARTITION BY LOWER(email)
ORDER BY signed_up DESC, signup_id DESC) AS rn
FROM signups
ORDER BY LOWER(email), rn;+-----------+--------------------+------------+----+
| signup_id | email | signed_up | rn |
+-----------+--------------------+------------+----+
| 6 | mitu@example.com | 2026-03-07 | 1 |
| 3 | Nadia@Example.com | 2026-03-05 | 1 |
| 1 | nadia@example.com | 2026-03-01 | 2 |
| 5 | rafiq@example.com | 2026-03-02 | 1 |
| 4 | rafiq@example.com | 2026-03-02 | 2 |
| 2 | tanvir@example.com | 2026-03-02 | 1 |
+-----------+--------------------+------------+----+Decide what makes two rows "the same" (PARTITION BY LOWER(email), so the case difference does not hide a duplicate) and which copy to keep (ORDER BY: the latest, then the highest id). Every row with rn > 1 is a duplicate. Look at them first, then delete them:
DELETE FROM signups
WHERE signup_id IN (
SELECT signup_id
FROM (
SELECT signup_id,
ROW_NUMBER() OVER (PARTITION BY LOWER(email)
ORDER BY signed_up DESC, signup_id DESC) AS rn
FROM signups
) AS t
WHERE rn > 1
);
SELECT signup_id, email, source FROM signups ORDER BY signup_id;+-----------+--------------------+--------+
| signup_id | email | source |
+-----------+--------------------+--------+
| 2 | tanvir@example.com | web |
| 3 | Nadia@Example.com | app |
| 5 | rafiq@example.com | web |
| 6 | mitu@example.com | app |
+-----------+--------------------+--------+Often you do not delete at all: you keep the raw table untouched and build a clean one with CREATE TABLE … AS SELECT … WHERE rn = 1, so the cleaning step can be rerun and audited. That is how data pipelines usually do it.
Common mistakes
- Filtering on a window function in
WHERE. It fails (shown above) because windows are computed afterWHERE. Fix: compute the window in a CTE or subquery and filter outside. (Snowflake, BigQuery and DuckDB add aQUALIFYclause for this; SQLite, MySQL and PostgreSQL do not have it.) ROW_NUMBERwithout a tie-breaker. With ties in theORDER BY, which row gets 1 is up to the database and can change between runs, so "the latest order" or "the kept duplicate" changes too. Fix: end the window'sORDER BYwith a unique column such as the primary key.- Using
RANKwhen you mean distinct values. "The third-highest salary" withRANK() = 3returns 160,000 here, but withRANKa tie above can skip 3 entirely. Fix:DENSE_RANKfor "Nth distinct value",ROW_NUMBERfor "Nth row". - Forgetting
PARTITION BY. Without it, products are ranked against all products, not within their category, and "top 2 per category" becomes "top 2 overall". Fix: partition by the group column. - Expecting the window's
ORDER BYto sort the result. It orders rows only inside the window calculation. Fix: add a finalORDER BYto the query.
Try it yourself
- Easy: Number each customer's orders in date order (1 = first order), showing customer id, order id, date and the number.
- Medium: Find the second most expensive product in each category, using
DENSE_RANK. - Hard: Rank customers by their revenue from sold orders with
DENSE_RANK, and include every customer: those with no revenue get 0 and share the last rank. Rank the result of theGROUP BYdirectly, without a CTE.
Answers
-- 1. Order sequence per customer
SELECT customer_id, order_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY order_date, order_id) AS order_number
FROM orders
ORDER BY customer_id, order_number;
-- 2. Second most expensive per category
SELECT category_id, name, price
FROM (
SELECT category_id, name, price, product_id,
DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS dr
FROM products
WHERE category_id IS NOT NULL
) AS t
WHERE dr = 2
ORDER BY category_id, product_id;
-- 3. Rank an aggregate: windows run after GROUP BY
SELECT c.name,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue,
DENSE_RANK() OVER (ORDER BY COALESCE(SUM(oi.quantity * oi.unit_price), 0) DESC) AS revenue_rank
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status IN ('delivered', 'shipped')
LEFT JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY c.customer_id, c.name
ORDER BY revenue_rank, c.customer_id;Summary
- A window function adds a value computed over related rows to each row;
GROUP BYcollapses, a window does not. OVER ()is all rows,PARTITION BYsplits into independent windows, theORDER BYinsideOVERsets the order for ranking.ROW_NUMBERalways gives distinct numbers (add a unique tie-breaker),RANKshares and skips,DENSE_RANKshares without gaps,NTILE(n)makes n buckets.- Windows are computed after
WHERE: rank in a CTE, filter outside. That gives top-N per group, the latest row per entity and deduplication.
Next: Window Functions II: LAG, LEAD, Running Totals and Frames uses the same OVER (…) to look at the previous row, build running totals and moving averages, and control exactly which rows a window includes.