🔥 Now, let’s move to the next topic:

✅ Window Functions

🧠 1. What are Window Functions?

Window functions perform calculations without grouping rows

👉 Difference:
• GROUP BY → reduces rows
• Window Function → keeps all rows + adds extra column

📊 Example Table

name → Amit, Ravi, Neha
department → IT, IT, HR
salary → 60000, 70000, 40000

⚡ 2. Basic Syntax
SELECT column,
FUNCTION() OVER (PARTITION BY column ORDER BY column)
FROM table;

🔥 3. ROW_NUMBER()

Assigns unique rank to each row

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

✔ Rank employees within each department

🥇 4. RANK() vs DENSE_RANK()

👉 RANK() (skips numbers)

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

👉 DENSE_RANK() (no skipping)

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

📊 Visual Difference

If salaries are 100, 90, 90, 80:
• RANK() gives: 1, 2, 2, 4
• DENSE_RANK() gives: 1, 2, 2, 3

⚡ 5. PARTITION BY (Very Important)

👉 Splits data into groups (like GROUP BY but without collapsing rows)

SELECT department, name, salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

✔ Shows avg salary per department for each row

🎯 6. Practice Tasks
1. Rank employees by salary
2. Rank employees within each department
3. Find highest salary per department
4. Add average salary column per department
5. Find second highest salary using window function

✅ Practice Tasks Solution

✅ 1. Rank employees by salary

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

✅ 2. Rank employees within each department

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

✅ 3. Find highest salary per department

SELECT name, department, salary
FROM (
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
) t
WHERE rn = 1;

✅ 4. Add average salary column per department

SELECT name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

✅ 5. Find second highest salary using window function

SELECT name, salary
FROM (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk = 2;

⚡ Mini Challenge 🔥
👉 Get top 2 highest paid employees in each department

⚡ Mini Challenge Solution 🔥

SELECT name, department, salary
FROM (
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
) t
WHERE rn <= 2;

🔥 Pro Tip:
• Use ROW_NUMBER() → unique ranking
• Use DENSE_RANK() → handle ties

Double Tap ❤️ For More