Window Functions & Ranking

1. How do you find the Nth highest salary in a table?

Sort the salaries from highest to lowest, skip the top N-1 rows, and take the next one. Two common approaches are DENSE_RANK() and LIMIT/OFFSET.

Understanding the idea: Imagine a leaderboard of salaries from biggest to smallest. The 1st highest is the top. The 2nd highest is next. The Nth highest is N positions down.

Employees table:

employee_id	name	salary
1	Ali	90000
2	Bob	85000
3	Cam	85000
4	Dan	70000

Approach 1 — using DENSE_RANK():

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

DENSE_RANK() gives every distinct salary a rank with no gaps. Ali is rank 1, Bob and Cam both rank 2, Dan rank 3. So rank 2 returns 85000.

If you used RANK() instead, Bob and Cam would both be rank 2 but Dan would be rank 4, because ties skip numbers. That can give the wrong answer when there are ties.

Approach 2 — using LIMIT and OFFSET:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

OFFSET 1 skips the highest row. LIMIT 1 takes the next one. For the 2nd highest, OFFSET is N-1, so OFFSET 1 means “skip 1 row”.

How the ranks work for each N:

N	What to skip	Result
1	0 rows (OFFSET 0)	90000
2	1 row (OFFSET 1)	85000
3	2 rows (OFFSET 2)	70000

Key takeaway: Use DENSE_RANK() when salaries can have ties. Use LIMIT/OFFSET for a quick and simple answer. Some databases don’t allow an expression like OFFSET N-1, so substitute the actual number.

2. What are Window Functions (e.g., RANK, DENSE_RANK)?

Window functions perform calculations across a set of related rows while keeping every individual row in the result. Normal aggregate functions collapse many rows into one; window functions do not.

Understanding the difference: A normal SUM() with GROUP BY returns one row per group. The individual rows are gone.

A window function returns the same number of rows as the input, with the calculation added as a new column.

Example — ranking employees within each department: Employees table:

name	department_id	salary
Ali	1	90000
Bob	1	85000
Cam	2	80000
Dan	2	80000

SELECT name, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM employees;

Result:

name	department_id	salary	rnk	dense_rnk
Ali	1	90000	1	1
Bob	1	85000	2	2
Cam	2	80000	1	1
Dan	2	80000	1	1

RANK() leaves gaps when values tie. DENSE_RANK() never leaves gaps.

Key parts of a window function:

• PARTITION BY splits the rows into groups (here, by department).
• ORDER BY defines the order inside each group.
• The function (like RANK() or SUM()) does the calculation over the window.

Other uses: Window functions can also make running totals and moving averages.

SELECT date, sales,
       SUM(sales) OVER (ORDER BY date) AS running_total
FROM daily_sales;

Key takeaway: Window functions replace complex self-joins and keep row detail that GROUP BY loses. RANK() and DENSE_RANK() are the two you’ll most often be asked about in interviews.

3. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

All three assign a number to each row based on an ordering. The difference is how they handle ties (equal values).

The sample data:

name	score
Ali	90
Bob	90
Cam	85

ROW_NUMBER() — gives every row a unique number, even ties.

name	score	row_number
Ali	90	1
Bob	90	2
Cam	85	3

RANK() — ties share a rank, and the next rank skips numbers.

name	score	rank
Ali	90	1
Bob	90	1
Cam	85	3

The next rank after the tie is 3, not 2.

DENSE_RANK() — ties share a rank, but the next rank does not skip.

name	score	dense_rank
Ali	90	1
Bob	90	1
Cam	85	2

The next rank is 2.

Example:

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
       RANK() OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM scores;

Key differences table:

	ROW_NUMBER()	RANK()	DENSE_RANK()
Unique number for every row	Yes	No	No
Gaps after ties	No	Yes	No
Ties share the same number	No	Yes	Yes

Key takeaway: Use ROW_NUMBER() when every row needs a unique number. Use RANK() when gaps are fine. Use DENSE_RANK() when ranks must be consecutive, like finding the Nth highest value.

4. Explain the use of LAG() and LEAD() window functions.

LAG() lets you access data from a previous row. LEAD() lets you access data from the next row. They let you compare a row with its neighbors without a self-join.

The sample data — daily sales:

date	sales
2026-01-01	100
2026-01-02	150
2026-01-03	120

LAG() — get the previous day’s sales:

SELECT date, sales,
       LAG(sales) OVER (ORDER BY date) AS prev_day_sales
FROM daily_sales;

Result:

date	sales	prev_day_sales
2026-01-01	100	(none)
2026-01-02	150	100
2026-01-03	120	150

LEAD() — get the next day’s sales:

SELECT date, sales,
       LEAD(sales) OVER (ORDER BY date) AS next_day_sales
FROM daily_sales;

Result:

date	sales	next_day_sales
2026-01-01	100	150
2026-01-02	150	120
2026-01-03	120	(none)

Practical use — day-over-day change:

SELECT date, sales,
       sales - LAG(sales) OVER (ORDER BY date) AS change_from_yesterday
FROM daily_sales;

Optional offset: LAG(sales, 2) would look back two rows instead of one.

Key takeaway: LAG() looks back, LEAD() looks forward. Both compare rows without the complexity of a self-join.