SQL Interview Cheat Sheet - WINDOW FUNCTION CHEAT SHEET

 Most important topic for interviews today.

---

ROW_NUMBER()

Assigns unique numbers.

SELECT *,
ROW_NUMBER() OVER
(
ORDER BY Salary DESC
) rn
FROM Employee;

Result

Name	Salary	RN
A	90000	1
B	90000	2
C	80000	3

---

RANK()

Same rank for ties, skips numbers.

SELECT *,
RANK() OVER
(
ORDER BY Salary DESC
) rk
FROM Employee;

Result

Salary	Rank
90000	1
90000	1
80000	3

---

DENSE_RANK()

No skipped ranks.

SELECT *,
DENSE_RANK() OVER
(
ORDER BY Salary DESC
) rk
FROM Employee;

Result

Salary	Rank
90000	1
90000	1
80000	2

---

PARTITION BY

Creates groups.

SELECT *,
RANK() OVER
(
PARTITION BY DeptID
ORDER BY Salary DESC
) rk
FROM Employee;

Used for:

• Top N per department
• Highest salary per department
• Latest order per customer

---

Running Total

SELECT OrderDate,
       Sales,
       SUM(Sales) OVER
       (
       ORDER BY OrderDate
       ) RunningTotal
FROM Sales;

---

LAG()

Previous row.

SELECT Month,
       Sales,
       LAG(Sales) OVER
       (
       ORDER BY Month
       ) PreviousMonth
FROM Sales;

Used for:

• Month-over-month growth
• Previous salary
• Previous order

---

LEAD()

Next row.

SELECT Month,
       Sales,
       LEAD(Sales) OVER
       (
       ORDER BY Month
       ) NextMonth
FROM Sales;

---

First Value

SELECT *,
FIRST_VALUE(Salary) OVER
(
PARTITION BY DeptID
ORDER BY Salary DESC
)
FROM Employee;

Highest salary in department.

---

Top 3 Employees Per Department

Very common interview question.

SELECT *
FROM
(
    SELECT *,
           DENSE_RANK() OVER
           (
           PARTITION BY DeptID
           ORDER BY Salary DESC
           ) rk
    FROM Employee
)x
WHERE rk <= 3;

---