✅ SQL Window Functions 🪟📊

Window functions perform calculations across rows related to the current row without collapsing data, unlike GROUP BY.

1️⃣ ROW_NUMBER()
Assigns a unique number to each row within 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() & LEAD()
Access previous or 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 vs previous/next values
(e.g., salary change, stock price movement).

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

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

📌 Use case: Quartiles & percentile-based analysis.


5️⃣ Aggregates with OVER()
Running totals & partition-wise calculations.

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

🧠 Interview Q&A

Q1️⃣ GROUP BY vs OVER()?
• GROUP BY → Collapses rows (one row per group)
• OVER() → Keeps all rows and adds calculated columns

Q2️⃣ When to use LAG()?
To compare current row with previous data
(e.g., daily revenue change, previous month balance).

Q3️⃣ No PARTITION BY used?
The function runs over the entire result set.

Q4️⃣ Can we use ORDER BY inside OVER()?
✅ Yes. Required for ranking, LAG/LEAD, running totals.

💬 Double tap ❤️ & share for more SQL tips! 🚀