What are window functions? Give real-world examples.

**Window functions in SQL perform calculations across a set of rows related to the current row**, without collapsing the result into a single row (unlike GROUP BY).

👉 In simple terms: They let you analyze data row-by-row while still seeing the bigger picture.

🧠 Key Concept

A window = a group of rows defined using:

OVER (PARTITION BY column ORDER BY column)

• PARTITION BY → splits data into groups
• ORDER BY → defines order within each group

🔹 Common Window Functions

• ROW_NUMBER()
• RANK() / DENSE_RANK()
• SUM() / AVG() (as window functions)
• LAG() / LEAD()

🌍 Real-World Examples

✅ 1. Ranking Students (Leaderboard)

👉 Scenario: Rank students based on marks

SELECT name, marks,
RANK() OVER (ORDER BY marks DESC) AS rank
FROM students;

✔ Use Case:

• College rankings
• Competition leaderboards

✅ 2. Running Total (Sales Dashboard)

👉 Scenario: Calculate **cumulative sales**

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

✔ Use Case:

• Business dashboards
• Revenue growth tracking

✅ 3. Salary Comparison Within Department

👉 Scenario: Compare employee salary within the same department

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

✔ Use Case:

• HR analytics
• Performance **benchmarking**

✅ 4. Previous Row Comparison (Trend Analysis)

👉 Scenario: Compare today’s sales with yesterday

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

✔ Use Case:

• Stock market trends
• Daily performance tracking

✅ 5. Top N per Category

👉 Scenario: Get top 3 products in each category

SELECT *
FROM (
  SELECT product, category, sales,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM products
) t
WHERE rn <= 3;

✔ Use Case:

• E-commerce (top products per category)
• **Marketing insights**