✅ SQL Window Functions 🧠🪟

Window functions perform calculations across rows that are related to the current row — without collapsing the result like GROUP BY.

1️⃣ ROW_NUMBER() – Assigns a unique row number per partition

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

➤ Gives ranking within each department

2️⃣ RANK() & DENSE_RANK() – Ranking with gaps (RANK) or without gaps (DENSE_RANK)

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

3️⃣ LAG() & LEAD() – Access previous or next row value

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

➤ Compare salary trends row-wise

4️⃣ SUM(), AVG(), COUNT() OVER() – Running totals, moving averages, etc.

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

5️⃣ NTILE(n) – Divides rows into n equal buckets

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

💡 Why Use Window Functions:
• Perform row-wise calculations
• Avoid GROUP BY limitations
• Enable advanced analytics (ranking, trends, etc.)

🧪 Practice Task:
Write a query to find the top 2 earners in each department using ROW_NUMBER().

💬 Tap ❤️ for more!