This is one of the most asked SQL interview topics in product-based companies.

Many candidates know GROUP BY, but very few truly understand Window Functions. Once you learn them, you'll solve problems that otherwise require messy subqueries.

Why were Window Functions invented?

Suppose we have this table.

Employees

ID Name Department Salary
1 Rahul IT 60000
2 Priya HR 55000
3 Amit IT 70000
4 Neha Sales 50000
5 Rohan Finance 65000
6 Ankit IT 60000

Suppose your manager asks:

Show every employee along with their department's average salary.

Expected output:

Name Department Salary Department Avg
Rahul IT 60000 63333
Amit IT 70000 63333
Ankit IT 60000 63333

Can GROUP BY do this?

Problem with GROUP BY

If we write:

SELECT Department,
       AVG(Salary)
FROM Employees
GROUP BY Department;

we get one row per department.

Where did Rahul go? Where is Amit? Where is Ankit?

Gone.

Why?

GROUP BY collapses multiple rows into one.

Window Functions solve this. They calculate aggregated values without collapsing rows.

Every employee remains visible.

What is a Window Function?

A Window Function performs a calculation over a set of rows, called a window, while keeping each original row in the result.

GROUP BY

6 rows
  |
  v
4 rows

Window Function:

6 rows
  |
  v
Still 6 rows
+ extra calculated column

This is the biggest difference.

OVER()

Every Window Function uses:

OVER(...)

Think of OVER() as saying:

On which set of rows should this calculation be performed?

Simplest example:

SELECT
    Name,
    Salary,
    AVG(Salary) OVER()
FROM Employees;

Average appears beside every employee. No rows disappear.

PARTITION BY

This is the heart of Window Functions.

Suppose we want average salary per department while keeping every employee.

SELECT
    Name,
    Department,
    Salary,
    AVG(Salary)
    OVER(PARTITION BY Department)
FROM Employees;

SQL internally creates partitions.

IT

Rahul
Amit
Ankit
  |
  v
Average

Each department becomes its own window.

GROUP BY vs PARTITION BY

GROUP BY

SELECT Department,
       AVG(Salary)
FROM Employees
GROUP BY Department;

One row per department.

PARTITION BY

SELECT Name,
       AVG(Salary)
OVER(PARTITION BY Department)
FROM Employees;

Employees remain.

Easy Memory Trick

GROUP BY:

Group
  |
  v
Collapse rows

PARTITION BY:

Group
  |
  v
Keep rows

ROW_NUMBER()

Assigns a unique number.

SELECT
    Name,
    Salary,
    ROW_NUMBER()
    OVER(ORDER BY Salary DESC)
FROM Employees;

Every row gets a unique number. Even ties get different numbers.

RANK()

Similar to ROW_NUMBER, but ties receive the same rank.

If salaries are:

70000
65000
60000
60000
55000

Ranks become:

1
2
3
3
5

Rank 4 is skipped.

DENSE_RANK()

Same as RANK, but does not skip numbers.

1
2
3
3
4

Difference

ROW_NUMBER:

1
2
3
4
5

RANK:

1
2
3
3
5

DENSE_RANK:

1
2
3
3
4

Easy Memory Trick

Interview Question: Find the 2nd highest salary

SELECT *
FROM
(
    SELECT *,
           DENSE_RANK()
           OVER(ORDER BY Salary DESC) AS rnk
    FROM Employees
) t
WHERE rnk = 2;

This is a very common interview problem.

LAG()

Returns the previous row's value.

SELECT
    Salary,
    LAG(Salary)
    OVER(ORDER BY Salary)
FROM Employees;

The first row has no previous row, so it returns NULL.

LEAD()

Returns the next row's value.

SELECT
    Salary,
    LEAD(Salary)
    OVER(ORDER BY Salary)
FROM Employees;

The final row has no next row, so it returns NULL.

Running Total

Another favorite interview problem.

SELECT
    Name,
    Salary,
    SUM(Salary)
    OVER(
        ORDER BY ID
    ) AS RunningTotal
FROM Employees;

Each row accumulates the previous total.

Common Window Functions

Function Purpose
ROW_NUMBER() Unique row numbering
RANK() Ranking with gaps
DENSE_RANK() Ranking without gaps
AVG() OVER() Running average or partition average
SUM() OVER() Running total or partition total
COUNT() OVER() Count while keeping rows
LAG() Previous row
LEAD() Next row

Interview Questions

1. Difference between GROUP BY and PARTITION BY?

GROUP BY PARTITION BY
Collapses rows Keeps rows
Returns one row per group Returns all original rows
Used with aggregate queries Used with window functions

2. Difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

Function Duplicate Values Gaps in Ranking
ROW_NUMBER() No, every row is unique N/A
RANK() Yes Yes
DENSE_RANK() Yes No

3. What does OVER() do?

It defines the window of rows over which the window function should operate. You can customize the window using PARTITION BY and ORDER BY.

Mental Model

GROUP BY

Rows
  |
  v
Grouped
  |
  v
Rows disappear

Window Function

Rows
  |
  v
Look around neighboring rows
  |
  v
Calculate value
  |
  v
Keep every row

That's why they're called Window Functions: each row gets to look through a window of related rows to perform calculations while still remaining in the final result.

Where Are Window Functions Used?

You'll frequently use them for: