Post #2459

✅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!