# SQL Window Functions — ROW_NUMBER, RANK, DENSE_RANK, LAG and Running Totals

Source: https://skills101.in/learn/dbms-sql/sql-window-functions — Skills101 free tutorial (DBMS & SQL). Updated 2026-11-09.

GROUP BY collapses rows into one row per group. **Window functions** calculate across a set of rows but **keep every row** — perfect for rankings, "top N per group" and running totals. They are now a favourite in data and backend interviews.

```sql
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(30), dept_id INT, salary INT, city VARCHAR(20), manager_id INT, joined DATE);
INSERT INTO employees VALUES
 (1, 'Asha', 10, 95000, 'Jaipur', NULL, '2019-04-01'), (2, 'Ravi', 10, 72000, 'Pune', 1, '2021-07-15'),
 (3, 'Neha', 20, 58000, 'Jaipur', 1, '2020-01-10'), (4, 'Kabir', 20, 58000, 'Delhi', 3, '2022-03-01'),
 (5, 'Om', 30, 45000, 'Pune', 1, '2023-06-20'), (6, 'Meera', 10, 88000, 'Bengaluru', 1, '2018-11-05'),
 (7, 'Arjun', NULL, 40000, 'Delhi', 3, '2024-02-12');
```

## 1. The OVER clause

`function() OVER (PARTITION BY … ORDER BY …)`

- **PARTITION BY** splits rows into groups (like GROUP BY, but rows stay).
- **ORDER BY** sets the order inside each group.

```sql
SELECT name, dept_id, salary,
       ROUND(AVG(salary) OVER (PARTITION BY dept_id), 0) AS dept_avg
FROM employees WHERE dept_id IS NOT NULL
ORDER BY dept_id, salary DESC;
```

```text
name | dept_id | salary | dept_avg
Asha | 10 | 95000 | 85000.0
Meera | 10 | 88000 | 85000.0
Ravi | 10 | 72000 | 85000.0
Neha | 20 | 58000 | 58000.0
Kabir | 20 | 58000 | 58000.0
Om | 30 | 45000 | 45000.0
```

Every employee row is kept **and** shows their department average.

## 2. ROW_NUMBER vs RANK vs DENSE_RANK

![On salaries 90000, 80000, 80000, 70000: ROW_NUMBER gives 1, 2, 3, 4; RANK gives 1, 2, 2, 4; DENSE_RANK gives 1, 2, 2, 3](https://skills101.in/learn-img/dbms-sql/window-rank.svg)

```sql
SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees ORDER BY salary DESC, name;
```

```text
name | salary | row_num | rnk | dense
Asha | 95000 | 1 | 1 | 1
Meera | 88000 | 2 | 2 | 2
Ravi | 72000 | 3 | 3 | 3
Kabir | 58000 | 4 | 4 | 4
Neha | 58000 | 5 | 4 | 4
Om | 45000 | 6 | 6 | 5
Arjun | 40000 | 7 | 7 | 6
```

- **ROW_NUMBER**: always unique. On ties the order is **arbitrary** unless you add a tie-breaker — that's why we wrote `ORDER BY salary DESC, name`. Without it, Kabir and Neha could swap between runs.
- **RANK**: ties share a rank, then **skips** (4, 4, 6).
- **DENSE_RANK**: ties share a rank, **no gaps** (4, 4, 5).

## 3. Nth highest salary (the most asked SQL question)

*Third highest distinct salary:*

```sql
SELECT DISTINCT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees
) t
WHERE r = 3;
```

```text
salary
72000
```

Use DENSE_RANK so ties don't create gaps.

## 4. Top N per group

*Highest-paid employee in each department:*

```sql
SELECT name, dept_id, salary FROM (
    SELECT name, dept_id, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, name) AS rn
    FROM employees WHERE dept_id IS NOT NULL
) t
WHERE rn = 1
ORDER BY dept_id;
```

```text
name | dept_id | salary
Asha | 10 | 95000
Kabir | 20 | 58000
Om | 30 | 45000
```

## 5. LAG and LEAD: compare with neighbours

```sql
SELECT name, joined, salary,
       LAG(salary) OVER (ORDER BY joined) AS prev_joiner_salary
FROM employees ORDER BY joined;
```

```text
name | joined | salary | prev_joiner_salary
Meera | 2018-11-05 | 88000 | NULL
Asha | 2019-04-01 | 95000 | 88000
Neha | 2020-01-10 | 58000 | 95000
Ravi | 2021-07-15 | 72000 | 58000
Kabir | 2022-03-01 | 58000 | 72000
Om | 2023-06-20 | 45000 | 58000
Arjun | 2024-02-12 | 40000 | 45000
```

`LAG` looks at the previous row, `LEAD` at the next — great for "change since last month".

## 6. Running total

```sql
SELECT name, joined, salary,
       SUM(salary) OVER (ORDER BY joined) AS running_payroll
FROM employees ORDER BY joined;
```

```text
name | joined | salary | running_payroll
Meera | 2018-11-05 | 88000 | 88000
Asha | 2019-04-01 | 95000 | 183000
Neha | 2020-01-10 | 58000 | 241000
Ravi | 2021-07-15 | 72000 | 313000
Kabir | 2022-03-01 | 58000 | 371000
Om | 2023-06-20 | 45000 | 416000
Arjun | 2024-02-12 | 40000 | 456000
```

## Common mistakes

- Using a window function in WHERE (not allowed — wrap it in a subquery or CTE, as above).
- Using RANK for "Nth highest" when ties exist — use DENSE_RANK.
- Forgetting PARTITION BY when you want "per group".

## Quick recap

- `f() OVER (PARTITION BY … ORDER BY …)` keeps all rows.
- ROW_NUMBER unique; RANK skips after ties; DENSE_RANK doesn't skip.
- Nth highest → DENSE_RANK = N; top per group → ROW_NUMBER with PARTITION BY.
- LAG/LEAD for neighbours; SUM() OVER (ORDER BY) for running totals.


Practice quiz and free certificate: https://skills101.in/learn/dbms-sql/sql-window-functions