🚀 Window Functions ⭐

Window functions are one of the most powerful SQL features used in data analytics, reporting, and advanced SQL interviews.

👉 They allow you to perform calculations across rows without collapsing them like GROUP BY.

In simple words:
GROUP BY → reduces rows

Window Functions → keep rows but add calculated values

🧠 Basic Syntax of Window Functions

SELECT column, window_function() 
OVER ( 
  PARTITION BY column 
  ORDER BY column 
) 
FROM table;

Components:
- OVER() → defines the window
- PARTITION BY → splits data into groups
- ORDER BY → defines calculation order

🔹 1️⃣ ROW_NUMBER()

Assigns a unique sequential number to rows.

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

Result:
name | salary | rank
Rahul | 90000 | 1
Priya | 85000 | 2
Amit | 85000 | 3

👉 Even if salaries are same, numbers stay unique.

🔹 2️⃣ RANK()

Assigns rank but skips numbers when ties occur.

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

Result:
name | salary | rank
Rahul | 90000 | 1
Priya | 85000 | 2
Amit | 85000 | 2
Neha | 80000 | 4

Notice rank 3 is skipped.

🔹 3️⃣ DENSE_RANK()

Similar to RANK but does not skip numbers.

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

Result:
name | salary | rank
Rahul | 90000 | 1
Priya | 85000 | 2
Amit | 85000 | 2
Neha | 80000 | 3

🔹 4️⃣ PARTITION BY

Used to divide rows into groups before calculation.

Example: Rank employees within each department

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

👉 Each department gets its own ranking.

🔹 5️⃣ LAG()

Used to access previous row values.

Example: Compare sales with previous day.

SELECT date, sales, LAG(sales) OVER(ORDER BY date) AS previous_sales 
FROM sales;

🔹 6️⃣ LEAD()

Used to access next row values.

SELECT date, sales, LEAD(sales) OVER(ORDER BY date) AS next_sales 
FROM sales;

⭐ Real Data Analyst Examples

Top 3 highest salaries

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

Running total of sales

SELECT date, sales, SUM(sales) OVER(ORDER BY date) AS running_total 
FROM sales;

Rank products by category

SELECT product_name, category, price, 
RANK() OVER(PARTITION BY category ORDER BY price DESC) AS rank 
FROM products;

🎯 Common Interview Questions
✔ Difference between ROW_NUMBER, RANK, DENSE_RANK
✔ Find Nth highest salary
✔ Running totals using window functions
✔ Compare current row with previous row
✔ Rank employees by department

🚀 Mini Practice Tasks
Task 1: Assign row numbers to employees by salary.
Task 2: Rank employees by salary.
Task 3: Find top 3 highest salaries using window functions.
Task 4: Calculate running total of sales.

💼 What You Must Master
✅ ROW_NUMBER()
✅ RANK()
✅ DENSE_RANK()
✅ PARTITION BY
✅ LAG() / LEAD()
✅ Running totals

These functions are used heavily in real analytics queries and SQL interviews.

Double Tap ♥️ For More