Post #2752

🔥 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