Week 02

Window Functions I: ROW_NUMBER, RANK & Top-N per Group

Day 82.5 to 3 hrs

Rank rows without losing their details

The latest order for each customer. The three highest salaries in each department. Every product tied for the highest revenue. These questions need you to compare rows within a group and still return details from individual rows.

GROUP BY branch with MAX(ctc) gives one maximum per branch. To return the student who received it, or the second highest distinct offer, a ranking function is usually easier to work with.

The main decision is how to handle ties. "Two students" and "students with the top two CTC values" can produce different answers. We will use the same data to see why.

What is a Window Function?

A window function calculates a value using related rows without combining them into a single output row. You can keep each offer and add its position within the branch.

PARTITION BY defines the groups. ORDER BY inside OVER decides the ranking within each group. The final ORDER BY still controls the order of the output.

Keep this one table for the whole day. It is the placement cell's offer sheet, CTC in lakhs.

offers

id	student	branch	company	ctc
1	Asha	CSE	Flipkart	44
2	Rahul	CSE	Zomato	32
3	Neha	CSE	Swiggy	32
4	Vikram	CSE	Infosys	18
5	Priya	ECE	Qualcomm	28
6	Karthik	ECE	Bosch	21
7	Arjun	IT	Adobe	36
8	Divya	IT	Wipro	12

Rahul and Neha are tied at 32. That single tie teaches you most of this topic, so watch those two rows.

Now suppose we tell the database: "the window of a row is all rows of the same branch". Standing on Rahul's row, here is what the window contains.

row	student	branch	ctc	inside Rahul's window?
1	Asha	CSE	44	yes
2	Rahul	CSE	32	yes, and this is the current row
3	Neha	CSE	32	yes
4	Vikram	CSE	18	yes
5	Priya	ECE	28	no, different branch
6	Karthik	ECE	21	no, different branch
7	Arjun	IT	36	no, different branch
8	Divya	IT	12	no, different branch

Rahul's rank is decided only among the four CSE rows. When the database moves to Priya's row, the window becomes the two ECE rows and her numbering starts again from 1.

That is the mental picture. Now the syntax.

Step 1: no window at all

Start with the input rows, just the CSE rows in order.

SQL

SELECT student, ctc
FROM offers
WHERE branch = 'CSE'
ORDER BY ctc DESC, student ASC;

student	ctc
Asha	44
Neha	32
Rahul	32
Vikram	18

WHERE keeps only CSE rows and ORDER BY decides the print order. There is no rank column yet, so you cannot filter on a position, because no position exists.

Step 2: add one numbering column

ROW_NUMBER() is a window function that gives 1, 2, 3, 4 to the rows of a window, in the order you specify. It exists because a plain SELECT has no idea what "the 2nd row" means.

SQL

SELECT student, ctc,
       ROW_NUMBER() OVER (ORDER BY ctc DESC, student ASC) AS rn
FROM offers
WHERE branch = 'CSE'
ORDER BY rn;

student	ctc	rn
Asha	44	1
Neha	32	2
Rahul	32	3
Vikram	18	4

Four rows went in, four rows came out, one column got added. OVER (...) says "this is a window function, and here is the window". The ORDER BY inside OVER is not the print order, it is the ranking order.

Step 3: cut the table into groups

PARTITION BY makes the window per branch instead of the whole table, so numbering restarts for each group. Leave it out, as in Step 2, and the window is the entire result.

Add the two cousins of ROW_NUMBER at the same time. RANK() and DENSE_RANK() also number rows, but they treat ties differently.

SQL

SELECT student, ctc,
       ROW_NUMBER() OVER (PARTITION BY branch ORDER BY ctc DESC, student ASC) AS rn,
       RANK()       OVER (PARTITION BY branch ORDER BY ctc DESC) AS rk,
       DENSE_RANK() OVER (PARTITION BY branch ORDER BY ctc DESC) AS dr
FROM offers
WHERE branch = 'CSE'
ORDER BY ctc DESC, student;

student	ctc	rn	rk	dr
Asha	44	1	1	1
Neha	32	2	2	2
Rahul	32	3	2	2
Vikram	18	4	4	3

ROW_NUMBER refuses to repeat a number, so it broke the 32 tie with the student ASC you supplied: Neha 2, Rahul 3. RANK gave both 32 rows the value 2 and then jumped to 4, because positions 2 and 3 were already consumed. DENSE_RANK gave both the value 2 and carried on at 3.

One line to remember for the interview. "Top 3 students" means people, so ROW_NUMBER. "Top 3 CTC values" means values, so DENSE_RANK. "Everyone at the top, ties included" means RANK or DENSE_RANK with the filter at 1.

FROM + JOIN
WHERE
GROUP BY
HAVING
window functions
ORDER BY
LIMIT

Windows run late, after WHERE and HAVING are already done. Two of today's traps come straight out of that line.

IMP
Dialect note: These examples use SQLite window functions. Check the engine and version before adapting the syntax elsewhere.

The Pattern

Every top-N-per-group question is the same four steps.

Get one clean row per thing you want to rank. If you are ranking a count or an average, aggregate first inside a CTE. A CTE is a named temporary result, written WITH name AS (...), that the outer query reads like a table.
Add the rank column: ROW_NUMBER for exactly one winner, RANK or DENSE_RANK when tied rows must all survive.
Filter WHERE rn = 1 or WHERE rn <= 3 in the outer query. You cannot filter at the level where the rank is created.
Write the final ORDER BY the statement asks for. It is separate from the window's own ORDER BY.

Here is the pattern for "the highest offer in each branch".

SQL

WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY branch ORDER BY ctc DESC, student ASC) AS rn
  FROM offers o
)
SELECT branch, student, company, ctc
FROM ranked
WHERE rn = 1
ORDER BY branch;

branch	student	company	ctc
CSE	Asha	Flipkart	44
ECE	Priya	Qualcomm	28
IT	Arjun	Adobe	36

Line by line:

WITH ranked AS ( starts a temporary result called ranked.
SELECT o.* keeps every original column of offers.
ROW_NUMBER() OVER (PARTITION BY branch ORDER BY ctc DESC, student ASC) adds one column: inside each branch the biggest CTC gets 1, and on a tie the alphabetically smaller student gets the smaller number.
FROM offers o is the input, all 8 rows, nothing filtered.
SELECT branch, student, company, ctc FROM ranked reads that result and picks only the four columns asked for, leaving rn out.
WHERE rn = 1 keeps the top row of each branch, at the outer level, which is the whole point.
ORDER BY branch sets the print order.

Dry run of that query, step by step, counting how many rows survive.

step	what happens	rows
FROM offers	all offers loaded	8
WHERE	no filter at this level	8
GROUP BY / HAVING	none	8
window	rn attached per branch, 1 to 4 for CSE, 1 to 2 for ECE and IT	8
SELECT (outer)	the CTE result is read and `rn = 1` is applied	3
ORDER BY branch	CSE, ECE, IT	3

The row count drops only at the outer level. The CTE retains all 8 rows, so the outer query can select any required rank.

Check the required columns, output order and formatting against the sample. The practice judge compares the SQLite result:

Text

CSE|Asha|Flipkart|44
ECE|Priya|Qualcomm|28
IT|Arjun|Adobe|36

A different row order, a different column count, or a stray rn column, and it is a wrong answer even though your logic was correct.

Check what you learned

Rahul and Neha both have 32 LPA, the second highest in CSE. The statement says "list every student whose CTC is one of the top 2 CTC values in their branch". Which function?

Query Templates

SQL

-- 1. Exactly one winner per group (best offer, latest row, first row).
WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY group_col
                            ORDER BY sort_col DESC, id DESC) AS rn
  FROM my_table o
)
SELECT * FROM ranked WHERE rn = 1 ORDER BY group_col;

-- 2. Top N per group where tied values must all survive.
WITH ranked AS (
  SELECT branch, student, ctc,
         DENSE_RANK() OVER (PARTITION BY branch ORDER BY ctc DESC) AS dr
  FROM offers
)
SELECT branch, student, ctc FROM ranked
WHERE dr <= 3 ORDER BY branch, ctc DESC, student;

-- 3. Rank something that must be aggregated first.
WITH stats AS (
  SELECT branch, AVG(ctc) AS a FROM offers GROUP BY branch
)
SELECT branch, PRINTF('%.2f', a) AS avg_ctc,
       RANK() OVER (ORDER BY a DESC) AS rk
FROM stats ORDER BY rk;

-- 4. Nth highest overall, N = 2 here.
SELECT DISTINCT ctc FROM (
  SELECT ctc, DENSE_RANK() OVER (ORDER BY ctc DESC) AS dr FROM offers
) WHERE dr = 2;

Template 3 prints this. Worth seeing once, because the ranked value is an average, not a raw column:

branch	avg_ctc	rk
CSE	31.50	1
ECE	24.50	2
IT	24.00	3

The CTE produced 3 aggregated rows, and only then did RANK number them.

Variations

Variation 1 - Nth highest CTC, three ways

It helps to know an alternative when window functions are unavailable or the interviewer asks for another approach. The distinct CTCs here are 44, 36, 32, 28, 21, 18, 12, so the second highest is 36. All three queries return 36.

SQL

-- (a) DENSE_RANK, handles duplicate CTCs correctly.
SELECT DISTINCT ctc FROM (
  SELECT ctc, DENSE_RANK() OVER (ORDER BY ctc DESC) AS dr FROM offers
) WHERE dr = 2;

-- (b) LIMIT with OFFSET on distinct values, no window function at all.
SELECT DISTINCT ctc FROM offers ORDER BY ctc DESC LIMIT 1 OFFSET 1;

-- (c) Correlated count, works even on MySQL 5.7.
SELECT DISTINCT o.ctc FROM offers o
WHERE (SELECT COUNT(DISTINCT o2.ctc) FROM offers o2 WHERE o2.ctc > o.ctc) = 1;

LIMIT n OFFSET k means "skip k rows, then take n rows", so OFFSET 1 skips the highest and takes the next. DISTINCT is doing real work here: without it, two students on 44 would eat both positions and you would print 44 as the second highest.

Version (c) reads aloud as "how many distinct CTCs are strictly bigger than mine? Exactly one, so I am the second highest." Change = 1 to < 3 and you have the top 3 values with no window function anywhere. That is how this question is solved when the interviewer bans windows.

Variation 2 - Second highest per branch

Same shape, different filter: keep rank 2 instead of rank 1. Here the function you choose changes the answer, so read the wording twice.

SQL

WITH r AS (
  SELECT o.*, DENSE_RANK() OVER (PARTITION BY branch ORDER BY ctc DESC) AS dr
  FROM offers o
)
SELECT branch, student, ctc FROM r WHERE dr = 2 ORDER BY branch, student;

branch	student	ctc
CSE	Neha	32
CSE	Rahul	32
ECE	Karthik	21
IT	Divya	12

Both CSE rows come, because 32 is the second highest value and two students hold it. Swap DENSE_RANK for ROW_NUMBER() OVER (PARTITION BY branch ORDER BY ctc DESC, student ASC) with rn = 2 and Rahul disappears, leaving only Neha. Neither is wrong in general. Only one matches the statement in front of you.

Variation 3 - Top 3 per branch, and the ROW_NUMBER trap

SQL

WITH ranked AS (
  SELECT o.*, DENSE_RANK() OVER (PARTITION BY branch ORDER BY ctc DESC) AS dr
  FROM offers o
)
SELECT branch, student, ctc FROM ranked
WHERE dr <= 3 ORDER BY branch, ctc DESC, student;

Here is the right output, 8 rows:

branch	student	ctc
CSE	Asha	44
CSE	Neha	32
CSE	Rahul	32
CSE	Vikram	18
ECE	Priya	28
ECE	Karthik	21
IT	Arjun	36
IT	Divya	12

And here is what the judge sees when you use ROW_NUMBER() ... WHERE rn <= 3 instead, 7 rows:

branch	student	ctc
CSE	Asha	44
CSE	Neha	32
CSE	Rahul	32
ECE	Priya	28
ECE	Karthik	21
IT	Arjun	36
IT	Divya	12

In CSE the dense ranks are 1, 2, 2, 3, so all four rows are within 3. With ROW_NUMBER they are 1, 2, 3, 4, the tie ate positions 2 and 3, and Vikram at 4 is cut. One tie in the hidden data, one row of difference, and the diff fails. The choice matters as soon as the data contains a tie.

Variation 4 - De-duplicate, keep the latest offer per student

Students update their offer during the season, so the log has more than one row per student.

offer_log

id	student	company	ctc	offered_on
11	Asha	Infosys	9	2026-08-02
12	Asha	Flipkart	44	2026-09-05
13	Rahul	TCS	7	2026-08-11
14	Rahul	Zomato	32	2026-09-01
15	Neha	Swiggy	32	2026-08-28

SQL

WITH latest AS (
  SELECT l.*,
         ROW_NUMBER() OVER (PARTITION BY student
                            ORDER BY offered_on DESC, id DESC) AS rn
  FROM offer_log l
)
SELECT student, company, ctc, offered_on FROM latest WHERE rn = 1 ORDER BY student;

student	company	ctc	offered_on
Asha	Flipkart	44	2026-09-05
Neha	Swiggy	32	2026-08-28
Rahul	Zomato	32	2026-09-01

Asha's Infosys row and Rahul's TCS row are gone, because a newer row exists for each. The , id DESC is not decoration. If two rows share a date, that is the only thing making your output reproducible.

The same query with customer_id and order_date is the "latest order per customer" OA question. Learn the shape once and it covers latest transaction per account, current status per ticket, newest price per product.

Variation 5 - Why you cannot put the window in WHERE

SQL

-- WRONG: the ranking has not happened yet when WHERE runs.
SELECT student, ROW_NUMBER() OVER (ORDER BY ctc DESC) AS rn
FROM offers WHERE rn = 1;

The judge prints no rows, only an error like misuse of aliased window function rn. Other databases say no such column: rn. The reason is the execution order above: WHERE runs before window functions, so the column does not exist yet. HAVING fails the same way, and so does an aggregate over a window, as in SUM(ROW_NUMBER() OVER ()).

The fix is always the same. Give the window its own level with a CTE, and filter one level above.

CTE computes the rank
outer SELECT reads it
filter rn = 1
final ORDER BY

Check what you learned

The statement wants the top 2 students per branch. Why can GROUP BY not do it?

Common Mistakes

Filtering before ranking. Put the window function in a CTE, then filter its result in the outer query.
Choosing the wrong tie rule. Use ROW_NUMBER for a fixed number of rows, DENSE_RANK for distinct values, and RANK when the required ranks have gaps after ties. Check the wording and sample.
Unstable row selection. For ROW_NUMBER, add a unique tie-break column when the main sort value can repeat. Do not add that column to RANK or DENSE_RANK if equal values should stay tied.
Missing output order. The window's ORDER BY controls the calculation. A final ORDER BY controls the displayed rows.
Extra columns. Keep the helper rank out of the final result unless the question asks for it.
Ranking the wrong rows. For highest average CTC, calculate one average per branch first. Ranking individual offers answers a different question.
Integer division. Use 100.0 * value / total for percentages. Format with PRINTF('%.2f', x) only when the output needs two fixed decimal places.

Interview Notes

Q: What is the difference between GROUP BY and PARTITION BY? GROUP BY collapses rows, so one output row stands for a whole group and the individual rows are gone. PARTITION BY removes nothing, it only defines which rows the calculation looks at, so the row count stays the same. Follow-up they ask: can you use both in one query? Yes, and the window then runs on top of the grouped rows.

Q: ROW_NUMBER, RANK and DENSE_RANK, how do they differ? ROW_NUMBER always gives distinct numbers 1 to n, so ties break arbitrarily unless I add a tie-break column. RANK gives tied rows the same number and then skips, so after two rows at 2 the next is 4. DENSE_RANK repeats without skipping, so the next is 3. Follow-up: which one for "top 3 salaries"? DENSE_RANK, since that phrasing counts distinct values.

Q: Find the second highest salary. Three ways: DENSE_RANK filtered on 2, or SELECT DISTINCT salary ORDER BY salary DESC LIMIT 1 OFFSET 1, or a correlated count of strictly greater distinct salaries equal to 1. All three return no row when there is no second value, and I would wrap it in a scalar subquery if the question wants NULL instead of an empty result.

Q: Why does WHERE rn = 1 fail? Because of the logical order: FROM, WHERE, GROUP BY, HAVING, window functions, SELECT, ORDER BY. At WHERE time the window has not run, so the column does not exist. I put the window in a CTE and filter one level up.

Q: Top 3 per department without window functions? A correlated subquery. For each row I count the distinct higher salaries in the same department and keep the row if that count is less than 3. That is the answer for MySQL 5.7 and older, where windows do not exist.

Q: Can a window function go inside an aggregate? Not at the same query level. To sum computed row numbers, calculate them in a CTE and aggregate outside. To rank grouped totals, aggregate first and then rank.

Q: Does DISTINCT remove the tied rank rows? DISTINCT compares the whole selected row. Selecting only salary removes repeated salaries. Adding ROW_NUMBER keeps rows separate because their numbers differ; adding RANK can still leave identical rows that DISTINCT removes.

Q: What happens if I leave PARTITION BY out? The window becomes the whole result set and the numbering runs once from top to bottom. That is exactly what I want for "Nth highest overall".

Q: Two employees tie for the top salary in a department and the question wants one row per department. What do you do? I ask for the tie-break rule, because the data cannot decide it. If there is no rule, I pick a deterministic one such as the smaller employee id, put it inside the OVER clause, and say the assumption out loud.

Quick Test

Check what you learned

CSE CTCs are 44, 32, 32, 18. What does RANK() give Vikram's 18 row?

Week 02

Window Functions I: ROW_NUMBER, RANK & Top-N per Group

Day 82.5 to 3 hrs

Rank rows without losing their details

GROUP BY branch with MAX(ctc) gives one maximum per branch. To return the student who received it, or the second highest distinct offer, a ranking function is usually easier to work with.

The main decision is how to handle ties. "Two students" and "students with the top two CTC values" can produce different answers. We will use the same data to see why.

What is a Window Function?

A window function calculates a value using related rows without combining them into a single output row. You can keep each offer and add its position within the branch.

PARTITION BY defines the groups. ORDER BY inside OVER decides the ranking within each group. The final ORDER BY still controls the order of the output.

Keep this one table for the whole day. It is the placement cell's offer sheet, CTC in lakhs.

offers

id	student	branch	company	ctc
1	Asha	CSE	Flipkart	44
2	Rahul	CSE	Zomato	32
3	Neha	CSE	Swiggy	32
4	Vikram	CSE	Infosys	18
5	Priya	ECE	Qualcomm	28
6	Karthik	ECE	Bosch	21
7	Arjun	IT	Adobe	36
8	Divya	IT	Wipro	12

Rahul and Neha are tied at 32. That single tie teaches you most of this topic, so watch those two rows.

Now suppose we tell the database: "the window of a row is all rows of the same branch". Standing on Rahul's row, here is what the window contains.

row	student	branch	ctc	inside Rahul's window?
1	Asha	CSE	44	yes
2	Rahul	CSE	32	yes, and this is the current row
3	Neha	CSE	32	yes
4	Vikram	CSE	18	yes
5	Priya	ECE	28	no, different branch
6	Karthik	ECE	21	no, different branch
7	Arjun	IT	36	no, different branch
8	Divya	IT	12	no, different branch

Rahul's rank is decided only among the four CSE rows. When the database moves to Priya's row, the window becomes the two ECE rows and her numbering starts again from 1.

That is the mental picture. Now the syntax.

Step 1: no window at all

Start with the input rows, just the CSE rows in order.

SQL

SELECT student, ctc
FROM offers
WHERE branch = 'CSE'
ORDER BY ctc DESC, student ASC;

student	ctc
Asha	44
Neha	32
Rahul	32
Vikram	18

WHERE keeps only CSE rows and ORDER BY decides the print order. There is no rank column yet, so you cannot filter on a position, because no position exists.

Step 2: add one numbering column

ROW_NUMBER() is a window function that gives 1, 2, 3, 4 to the rows of a window, in the order you specify. It exists because a plain SELECT has no idea what "the 2nd row" means.

SQL

SELECT student, ctc,
       ROW_NUMBER() OVER (ORDER BY ctc DESC, student ASC) AS rn
FROM offers
WHERE branch = 'CSE'
ORDER BY rn;

student	ctc	rn
Asha	44	1
Neha	32	2
Rahul	32	3
Vikram	18	4

Step 3: cut the table into groups

PARTITION BY makes the window per branch instead of the whole table, so numbering restarts for each group. Leave it out, as in Step 2, and the window is the entire result.

Add the two cousins of ROW_NUMBER at the same time. RANK() and DENSE_RANK() also number rows, but they treat ties differently.

SQL

SELECT student, ctc,
       ROW_NUMBER() OVER (PARTITION BY branch ORDER BY ctc DESC, student ASC) AS rn,
       RANK()       OVER (PARTITION BY branch ORDER BY ctc DESC) AS rk,
       DENSE_RANK() OVER (PARTITION BY branch ORDER BY ctc DESC) AS dr
FROM offers
WHERE branch = 'CSE'
ORDER BY ctc DESC, student;

student	ctc	rn	rk	dr
Asha	44	1	1	1
Neha	32	2	2	2
Rahul	32	3	2	2
Vikram	18	4	4	3

FROM + JOIN
WHERE
GROUP BY
HAVING
window functions
ORDER BY
LIMIT

Windows run late, after WHERE and HAVING are already done. Two of today's traps come straight out of that line.

IMP
Dialect note: These examples use SQLite window functions. Check the engine and version before adapting the syntax elsewhere.

The Pattern

Every top-N-per-group question is the same four steps.

Get one clean row per thing you want to rank. If you are ranking a count or an average, aggregate first inside a CTE. A CTE is a named temporary result, written WITH name AS (...), that the outer query reads like a table.
Add the rank column: ROW_NUMBER for exactly one winner, RANK or DENSE_RANK when tied rows must all survive.
Filter WHERE rn = 1 or WHERE rn <= 3 in the outer query. You cannot filter at the level where the rank is created.
Write the final ORDER BY the statement asks for. It is separate from the window's own ORDER BY.

Here is the pattern for "the highest offer in each branch".

SQL

WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY branch ORDER BY ctc DESC, student ASC) AS rn
  FROM offers o
)
SELECT branch, student, company, ctc
FROM ranked
WHERE rn = 1
ORDER BY branch;

branch	student	company	ctc
CSE	Asha	Flipkart	44
ECE	Priya	Qualcomm	28
IT	Arjun	Adobe	36

Line by line:

WITH ranked AS ( starts a temporary result called ranked.
SELECT o.* keeps every original column of offers.
ROW_NUMBER() OVER (PARTITION BY branch ORDER BY ctc DESC, student ASC) adds one column: inside each branch the biggest CTC gets 1, and on a tie the alphabetically smaller student gets the smaller number.
FROM offers o is the input, all 8 rows, nothing filtered.
SELECT branch, student, company, ctc FROM ranked reads that result and picks only the four columns asked for, leaving rn out.
WHERE rn = 1 keeps the top row of each branch, at the outer level, which is the whole point.
ORDER BY branch sets the print order.

Dry run of that query, step by step, counting how many rows survive.

step	what happens	rows
FROM offers	all offers loaded	8
WHERE	no filter at this level	8
GROUP BY / HAVING	none	8
window	rn attached per branch, 1 to 4 for CSE, 1 to 2 for ECE and IT	8
SELECT (outer)	the CTE result is read and `rn = 1` is applied	3
ORDER BY branch	CSE, ECE, IT	3

The row count drops only at the outer level. The CTE retains all 8 rows, so the outer query can select any required rank.

Check the required columns, output order and formatting against the sample. The practice judge compares the SQLite result:

Text

CSE|Asha|Flipkart|44
ECE|Priya|Qualcomm|28
IT|Arjun|Adobe|36

A different row order, a different column count, or a stray rn column, and it is a wrong answer even though your logic was correct.

Check what you learned

Rahul and Neha both have 32 LPA, the second highest in CSE. The statement says "list every student whose CTC is one of the top 2 CTC values in their branch". Which function?

Query Templates

SQL

-- 1. Exactly one winner per group (best offer, latest row, first row).
WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY group_col
                            ORDER BY sort_col DESC, id DESC) AS rn
  FROM my_table o
)
SELECT * FROM ranked WHERE rn = 1 ORDER BY group_col;

-- 2. Top N per group where tied values must all survive.
WITH ranked AS (
  SELECT branch, student, ctc,
         DENSE_RANK() OVER (PARTITION BY branch ORDER BY ctc DESC) AS dr
  FROM offers
)
SELECT branch, student, ctc FROM ranked
WHERE dr <= 3 ORDER BY branch, ctc DESC, student;

-- 3. Rank something that must be aggregated first.
WITH stats AS (
  SELECT branch, AVG(ctc) AS a FROM offers GROUP BY branch
)
SELECT branch, PRINTF('%.2f', a) AS avg_ctc,
       RANK() OVER (ORDER BY a DESC) AS rk
FROM stats ORDER BY rk;

-- 4. Nth highest overall, N = 2 here.
SELECT DISTINCT ctc FROM (
  SELECT ctc, DENSE_RANK() OVER (ORDER BY ctc DESC) AS dr FROM offers
) WHERE dr = 2;

Template 3 prints this. Worth seeing once, because the ranked value is an average, not a raw column:

branch	avg_ctc	rk
CSE	31.50	1
ECE	24.50	2
IT	24.00	3

The CTE produced 3 aggregated rows, and only then did RANK number them.

Variations

Variation 1 - Nth highest CTC, three ways

SQL

-- (a) DENSE_RANK, handles duplicate CTCs correctly.
SELECT DISTINCT ctc FROM (
  SELECT ctc, DENSE_RANK() OVER (ORDER BY ctc DESC) AS dr FROM offers
) WHERE dr = 2;

-- (b) LIMIT with OFFSET on distinct values, no window function at all.
SELECT DISTINCT ctc FROM offers ORDER BY ctc DESC LIMIT 1 OFFSET 1;

-- (c) Correlated count, works even on MySQL 5.7.
SELECT DISTINCT o.ctc FROM offers o
WHERE (SELECT COUNT(DISTINCT o2.ctc) FROM offers o2 WHERE o2.ctc > o.ctc) = 1;

Variation 2 - Second highest per branch

Same shape, different filter: keep rank 2 instead of rank 1. Here the function you choose changes the answer, so read the wording twice.

SQL

WITH r AS (
  SELECT o.*, DENSE_RANK() OVER (PARTITION BY branch ORDER BY ctc DESC) AS dr
  FROM offers o
)
SELECT branch, student, ctc FROM r WHERE dr = 2 ORDER BY branch, student;

branch	student	ctc
CSE	Neha	32
CSE	Rahul	32
ECE	Karthik	21
IT	Divya	12

Variation 3 - Top 3 per branch, and the ROW_NUMBER trap

SQL

WITH ranked AS (
  SELECT o.*, DENSE_RANK() OVER (PARTITION BY branch ORDER BY ctc DESC) AS dr
  FROM offers o
)
SELECT branch, student, ctc FROM ranked
WHERE dr <= 3 ORDER BY branch, ctc DESC, student;

Here is the right output, 8 rows:

branch	student	ctc
CSE	Asha	44
CSE	Neha	32
CSE	Rahul	32
CSE	Vikram	18
ECE	Priya	28
ECE	Karthik	21
IT	Arjun	36
IT	Divya	12

And here is what the judge sees when you use ROW_NUMBER() ... WHERE rn <= 3 instead, 7 rows:

branch	student	ctc
CSE	Asha	44
CSE	Neha	32
CSE	Rahul	32
ECE	Priya	28
ECE	Karthik	21
IT	Arjun	36
IT	Divya	12

Variation 4 - De-duplicate, keep the latest offer per student

Students update their offer during the season, so the log has more than one row per student.

offer_log

id	student	company	ctc	offered_on
11	Asha	Infosys	9	2026-08-02
12	Asha	Flipkart	44	2026-09-05
13	Rahul	TCS	7	2026-08-11
14	Rahul	Zomato	32	2026-09-01
15	Neha	Swiggy	32	2026-08-28

SQL

WITH latest AS (
  SELECT l.*,
         ROW_NUMBER() OVER (PARTITION BY student
                            ORDER BY offered_on DESC, id DESC) AS rn
  FROM offer_log l
)
SELECT student, company, ctc, offered_on FROM latest WHERE rn = 1 ORDER BY student;

student	company	ctc	offered_on
Asha	Flipkart	44	2026-09-05
Neha	Swiggy	32	2026-08-28
Rahul	Zomato	32	2026-09-01

Variation 5 - Why you cannot put the window in WHERE

SQL

-- WRONG: the ranking has not happened yet when WHERE runs.
SELECT student, ROW_NUMBER() OVER (ORDER BY ctc DESC) AS rn
FROM offers WHERE rn = 1;

The fix is always the same. Give the window its own level with a CTE, and filter one level above.

CTE computes the rank
outer SELECT reads it
filter rn = 1
final ORDER BY

Check what you learned

The statement wants the top 2 students per branch. Why can GROUP BY not do it?

Common Mistakes

Filtering before ranking. Put the window function in a CTE, then filter its result in the outer query.
Choosing the wrong tie rule. Use ROW_NUMBER for a fixed number of rows, DENSE_RANK for distinct values, and RANK when the required ranks have gaps after ties. Check the wording and sample.
Unstable row selection. For ROW_NUMBER, add a unique tie-break column when the main sort value can repeat. Do not add that column to RANK or DENSE_RANK if equal values should stay tied.
Missing output order. The window's ORDER BY controls the calculation. A final ORDER BY controls the displayed rows.
Extra columns. Keep the helper rank out of the final result unless the question asks for it.
Ranking the wrong rows. For highest average CTC, calculate one average per branch first. Ranking individual offers answers a different question.
Integer division. Use 100.0 * value / total for percentages. Format with PRINTF('%.2f', x) only when the output needs two fixed decimal places.

Interview Notes

Q: What happens if I leave PARTITION BY out? The window becomes the whole result set and the numbering runs once from top to bottom. That is exactly what I want for "Nth highest overall".

Quick Test

Check what you learned

CSE CTCs are 44, 32, 32, 18. What does RANK() give Vikram's 18 row?