✅ SQL Window Functions 🪟📊

Window functions perform calculations across rows related to the current row without collapsing them like GROUP BY does.

1️⃣ ROW_NUMBER()
Gives a unique number to each row in a partition.

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

📌 Use case: Rank employees by salary within each department.

2️⃣ RANK() vs DENSE_RANK()
⦁ RANK() → Skips numbers on ties (1, 2, 2, 4)
⦁ DENSE_RANK() → No gaps (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() and LEAD()
Access previous/next row values.

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

📌 Use case: Compare current row to previous/next (e.g., salary or stock change).

4️⃣ NTILE(n)
Divides rows into n buckets.

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

📌 Use case: Quartiles/percentile-style grouping.

5️⃣ SUM(), AVG(), COUNT() with OVER()
Running totals, partition-wise aggregates, moving stats.

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

🧠 Interview Q&A

Q1: Difference between GROUP BY and OVER()?
⦁ GROUP BY → Collapses rows into groups; one row per group.
⦁ OVER() → Keeps all rows; adds an extra column with the aggregate.

Q2: When would you use LAG()?
To compare current row values with previous ones (e.g., day‑to‑day revenue change, previous month’s balance).

Q3: What happens if no PARTITION BY is used?
The function runs over the entire result set as a single partition.

Q4: Can you sort inside OVER()?
Yes, ORDER BY inside OVER() defines the calculation order (needed for ranking, LAG/LEAD, running totals).

💬 Double Tap ❤️ for more!