1️⃣ ROW_NUMBER()

Assigns a unique sequential number to each row within a partition.

SELECT name, dept_id,
  ROW_NUMBER() OVER (
    PARTITION BY dept_id
    ORDER BY salary DESC
  ) AS rank
FROM employees;

2️⃣ RANK() vs DENSE_RANK()

Both functions rank rows, but they handle ties differently:

• RANK() → Skips numbers on ties (1, 2, 2, 4)
• DENSE_RANK() → No gaps in ranking (1, 2, 2, 3)

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

3️⃣ LAG() & LEAD()

These functions allow access to values from previous or next rows.

SELECT name, salary,
  LAG(salary)  OVER (ORDER BY id) AS prev_salary,
  LEAD(salary) OVER (ORDER BY id) AS next_salary
FROM employees;

4️⃣ NTILE(n)

Divides rows into n approximately equal buckets.

SELECT name,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

5️⃣ Aggregate Functions with OVER()

Allows running totals and partition-based aggregates without grouping rows.

SELECT name, dept_id, salary,
  SUM(salary) OVER (PARTITION BY dept_id) AS dept_total
FROM employees;

• GROUP BY → Collapses rows (one row per group)
• OVER() → Keeps all rows and adds calculated columns

To compare the current row with previous data (e.g., daily revenue change, previous month balance).

The window function runs over the entire result set.

✅ Yes. It is required for ranking functions, LAG/LEAD, and running totals.