TRAINING | DAY 10

Window Functions: ROW_NUMBER, RANK, DENSE_RANK, LEAD & LAG

Numbering and ranking rows inside partitions, top-N per group, the granularity rule, and looking at the previous or next row.

The Crux in 60 Seconds

Windows add a column, rows stay

A window function with OVER (PARTITION BY ... ORDER BY ...) works inside groups of rows without collapsing them, unlike GROUP BY.

Three numbering functions, one difference

ROW_NUMBER is always unique; RANK gives ties the same rank and skips (1, 2, 2, 4); DENSE_RANK gives ties the same rank without skipping (1, 2, 2, 3).

Filter in an outer query

The number is created in SELECT, so WHERE cannot see it. Wrap the query in a subquery or CTE and filter rn <= 2 outside.

Aggregate first, then rank

Window functions do not aggregate. For top products per category, sum sales by category and product first. LEAD and LAG fetch the next or previous row.

Foundational Concepts & Comparisons

GROUP BY

Aggregates: one row per group, so row count drops.
Gives max, min, average, but not the second-highest.

PARTITION BY

Creates a window per group but keeps every row; adds a column in SELECT.

ROW_NUMBER vs RANK

ROW_NUMBER: unique running number even for equal salaries.
RANK: same value, same rank.

RANK vs DENSE_RANK

RANK skips after ties: 1, 2, 2, 4.
DENSE_RANK does not: 1, 2, 2, 3.

LEAD vs LAG

LEAD looks at the next row in the ORDER BY sequence; LAG the previous row.

With vs without PARTITION BY

Without it the full table is one window. With it, numbering restarts for each partition.

Raw rows vs aggregated rows

Ranking order rows ranks orders, not products. Aggregate to product level first.

Subquery vs CTE for the filter

Either works: generate the number in the inner part, filter in the outer query.

Architectural Mechanics & Workflows

Day 9 assignment review

Q4 (top salary per department), student approach confirmed: subquery with MAX(salary) grouped by department id, then join back to employee on department id and compare the salary; two derived tables joined on department and salary also works.
Q6 (hardest): total sales per product id, only for the highest-selling product in each category. "Highest-selling" means the highest sum of units; a product can have several orders.
Students are now comfortable with subqueries and CTEs. Without window functions the set of solvable questions is limited, so these come first; more questions follow on Thursday.

Why window functions

With a subquery, the highest salary per department (100: 15,000; 200: 12,000) comes from GROUP BY department_id plus MAX, joined back. But what about the top two employees per department, or the top two products per category?

UNION or an overall ORDER BY and top two gives the top two of the whole table, not per department.
Subqueries, CTEs, aggregation or filtering alone cannot do it: GROUP BY yields max, min or average, not the second-highest.
You need to create windows within the table (partition it) and compute inside them.
Without window functions, queries become complex; with them they are easier to read and more performant. Fundamental to SQL and heavily used in work and interviews.

ROW_NUMBER and the OVER clause

SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employee

Function nameROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG

→

OVERdefines the window

→

PARTITION BYone window per value (optional)

→

ORDER BYbasis for the number

The function name varies; the OVER structure stays the same.
ORDER BY inside OVER is not sorting the output; it tells the function how to number rows. Salary descending: highest paid gets 1, lowest the last number. Ascending: lowest gets 1.
No PARTITION BY: the full table is one window, numbered across all rows.
Ties: order may just follow how data is stored. Add a second ORDER BY column (employee name or id) as a tie-breaker; it sorts by salary first, then by that column.

PARTITION BY and filtering the result

PARTITION BY department_id creates a window per department; numbering restarts at 1 in each (100 gets 1, 2, 3, 4; 200 restarts 1, 2, 3; same for 300 and 700).
It is like grouping by department, but the number of rows stays the same: there is no GROUP BY or WHERE, only an added column in SELECT.
Partition is optional, but ROW_NUMBER needs ORDER BY, or the database does not know the basis for numbering. Order by salary, age, date of birth or any value.

Student question	Answer
Are equal salaries sorted by employee id?	Not necessarily; output may follow stored order. Add a column to ORDER BY for a particular tie-break.
Are the function and OVER always used together?	Yes, that is the full syntax; only the window specification inside OVER is optional.

Top two employees per department

Generate ROW_NUMBER, then filter 1 or 2. But the number is created in SELECT, which runs after WHERE, so WHERE rn <= 2 in the same query is an invalid column. HAVING does not help either: there is no grouping.

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employee)
SELECT * FROM ranked WHERE rn <= 2

A subquery in FROM works equally: generate the number inside, filter rn <= 2 in the outer query. Other window functions follow the same syntax: function name, OVER, PARTITION BY and ORDER BY as needed. Some have specific requirements, but for these functions the syntax is alike.

RANK

ROW_NUMBER gives a unique value whether or not salaries tie. RANK gives equal values the same rank.
Example: two employees earn 12,000, so both get rank 2, and the next person gets rank 4 (skipping 3), since that person is fourth by salary. The next rank always reflects how many rows came before.
With PARTITION BY the same behaviour applies separately in each partition.

Partition by several columnsEach unique combination forms a window, as grouping by two columns forms a group per combination. Partition by department id and department name, or add salary: rows with the same department and salary fall in one window. ROW_NUMBER numbers 1, 2 inside that window; RANK gives identical values the same rank. Partitions form first; ORDER BY then orders within each. A student asked about employees under a manager with equal salary: manager id would be part of the partition, but the exact query depends on the question.

DENSE_RANK and choosing between the three

Salary order	ROW_NUMBER	RANK	DENSE_RANK
Highest salary	1	1	1
12,000 (first of two)	2	2	2
12,000 (second of two)	3	2	2
Next salary	4	4 (3 skipped)	3 (no skip)

With PARTITION BY each works within the partition; without it, across the full table.
Common interview topic: given data, work out all three. Make sure you can tell them apart.

Which one should I use?

It depends on the requirement; there is no hard rule. For the top two employees per department, RANK <= 2 may return three rows if two tie at rank 2.
If the question says to break ties by name or age, add that column to ORDER BY (for example salary, then age ascending to pick the younger first). The combination is no longer tied, so ranks differ.
Student question: use DENSE_RANK for top five products so none are missed? RANK skips: two tied at rank 4 make the next rank 6; DENSE_RANK makes it 5. Five ranks could then include six products. Whether the business wants exactly five rows or everyone in the top five ranks, ties included, is a requirement: ask for clarification rather than assuming.

Granularity: aggregate first, then rank

Task: top five selling products in each category by sales. A window function does not sum or aggregate; it takes the rows it receives, partitions them, orders them and assigns ranks. The orders table is at order level, so a product can have several rows.

Ranking raw rows (partition by category, order by sales) ranks individual order rows, not products. Filtering to ranks 1 and 2 returns some order rows, not the top products by total.
First compute total sales per category and product, then rank those totals within each category.

Category	Product totals	Top two
1	100: 600, 200: 150, 300 and 400 have their own totals	100 and 200
2	600: 500, 700: 400, 800: 100	600 and 700

The principleBefore using a window function, decide the granularity you need. Employee ids are unique, so top employee by salary needs no aggregation; product orders do. Same idea as the highest-paid problem: group by department to find the max, then join back.

WITH category_product_sales AS (
  SELECT category, product_id, SUM(sales) AS total_sales
  FROM orders GROUP BY category, product_id),
ranked AS (
  SELECT *, RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rnk
  FROM category_product_sales)
SELECT * FROM ranked WHERE rnk <= 5

Aggregate in the first CTE; rank in the second (the rank is generated in SELECT, so it needs another layer to filter). Result: five products per category, 15 records for three categories.
You can group and rank in one query (GROUP BY category and product id, ordering by SUM(sales)). If that confuses you, use two CTEs: aggregate first, then rank. Combine later when comfortable.
A later CTE can use an earlier one, or join several; treat each as a virtual table that can be reused, joined or built on.
Patterns to know: top employees per department, top products per category.

LEAD and LAG

LEAD(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY salary DESC)   -- next row
LAG(salary, 1, 0)  OVER (PARTITION BY department_id ORDER BY salary DESC)   -- previous row

Part	Behaviour
Column	Value fetched from the other row (employee id, salary, any column). It need not be the ORDER BY column.
Offset (2nd argument)	1 means the next (LAG: previous) row, 2 means two rows away, and so on. Default is 1.
Default (3rd, optional)	Returned when there is no such row (for example 500, 999, or a column such as the current salary). Without it the result is NULL. Also used when the offset runs past the rows.
Edge rows	LEAD: last row NULL. LAG: first row NULL. With PARTITION BY this happens in each partition.

The function sets the direction (LEAD forward, LAG backward); ORDER BY sets the sequence. Reversing ORDER BY can make LEAD and LAG give equivalent results.

Year-over-year and month-over-month

YoY: aggregate sales by year (2019: 100,000; 2020: 150,000), then LAG brings the previous year's sales onto the same row. Difference divided by the previous value gives growth.
MoM: aggregate by year and month, order by year then month. Without PARTITION BY, January's previous row is the prior December. With PARTITION BY year, each year's first month has no previous value. Choose by use case.
The database does not know months or years; it sorts by ORDER BY and returns the previous row. You must decide the granularity and aggregate first.

FIRST_VALUE and LAST_VALUEReturn the first or last value in the partition per the ordering (ordering by employee id gives the first salary in that order). The LAST_VALUE result may depend on how the window is defined; the instructor will check and discuss on Thursday.

Which function for which need

Need	Function
Unique running number	ROW_NUMBER
Ties share a rank, numbers skipped	RANK
Ties share a rank, no skipping	DENSE_RANK
Value from the next or previous row	LEAD or LAG
First or last value in the partition	FIRST_VALUE, LAST_VALUE

Best Practices & Golden Rules

Decide granularity firstWindow functions do not aggregate. Aggregate to the level you want to rank.

Filter one layer upGenerate the number in a subquery or CTE; filter in the outer query.

ROW_NUMBER needs ORDER BYAdd a tie-breaker column when you need a predictable order.

Pick the function from the requirementAsk whether ties should return extra rows.

Choose PARTITION BY on purposeFor month comparisons it decides whether January sees the prior December.

Practise on your own dataInvent questions about subcategories or regions and solve them with these functions.

Numbers & Metrics to Remember

1, 2, 2, 4

RANK sequence with a tie

1, 2, 2, 3

DENSE_RANK sequence with a tie

rows for top five products in three categories

arguments of LEAD: column, offset, default

Mini Glossary

Window function: calculates across a set of rows without collapsing them.

OVER clause: defines the window (partition and order) for the function.

PARTITION BY: splits rows into windows; numbering restarts in each.

ROW_NUMBER: unique running number following the ORDER BY.

RANK: equal values share a rank; following numbers skip.

DENSE_RANK: equal values share a rank; no numbers skipped.

LEAD / LAG: value from the next / previous row in the ordering.

Granularity: the level of detail a row represents (order, product, category).

Quick Self-Test

1Why can GROUP BY not give the top two salaries per department?
2What do PARTITION BY and ORDER BY each do inside OVER, and what if PARTITION BY is omitted?
3Scenario: WHERE rn <= 2 on a ROW_NUMBER column raises an invalid column error. Why, and how do you fix it?
4Salaries 15, 12, 12, 10: give ROW_NUMBER, RANK and DENSE_RANK for each row.
5Scenario: top two employees per department with RANK <= 2 returns three rows in one department. Why, and what are the options?
6Why must sales be summed by category and product before ranking products?
7Troubleshooting: LAG returns NULL on the first row of each department. Why, and how can you supply a different value?
8For month-over-month growth, what changes if you add PARTITION BY year?