📘 Module 30: Window Functions
This module introduces window functions in SQL that let you perform calculations across rows
while keeping individual row details. These are powerful for advanced analytics like ranking,
running totals, comparisons, etc.
🧾 Reference Tables Used in This Module:
📊 Table 1: Sales
🧑💼 Table 2: Employees
📘 Module 30: Window Functions 1
🌐 1. 💡 What are Window Functions
🔍 Window functions perform calculations across a group of rows (a "window") that are related to
the current row.
They do not collapse the rows like GROUP BY . Instead, they return values for every row.
🧩 Syntax:
FUNCTION_NAME(column)
OVER (
PARTITION BY column_name
ORDER BY column_name
ROWS BETWEEN ... -- optional
)
💡 Components:
🧭 PARTITION BY – Divides the result into groups (like departments).
🪜 ORDER BY – Sorts rows inside each group (e.g., by salary).
🧱 ROWS or RANGE – Controls which rows are included in the window (optional, advanced use).
🔢 2. 🎯 ROW_NUMBER()
🚦 Gives a unique serial number to each row within a partition.
🔣 Syntax:
ROW_NUMBER() OVER(PARTITION BY column ORDER BY column)
📌 Example:
SELECT SalesID, SalesDate, Amount,
ROW_NUMBER() OVER(PARTITION BY SalesDate ORDER BY SalesID) AS RowNum
FROM Sales;
📘 Module 30: Window Functions 2
👑 3. 🧮 Row Number Implementation (Use Case)
Use this to get top earners in each department.
📌 Example 1:
SELECT employeeid, employeename, department, salary,
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;
📌 Example 2 (Only Top Earners):
SELECT * FROM (
SELECT employeeid, employeename, department, salary,
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees
) AS subquery
WHERE row_num = 1;
📘 Module 30: Window Functions 3
🥇 4. 🎖️ RANK() vs DENSE_RANK()
🗂 Used to rank rows within a partition.
🔣 Syntax:
RANK() OVER(PARTITION BY column ORDER BY column)
DENSE_RANK() OVER(PARTITION BY column ORDER BY column)
📌 Examples:
SELECT *, RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
SELECT *, DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
📘 Module 30: Window Functions 4
🧠 Key Difference:
Function Handles Ties Skips Rank
ROW_NUMBER ❌ No ❌ No
RANK ✅ Yes ✅ Yes
DENSE_RANK ✅ Yes ❌ No
🎯 5. 🪜 NTILE(n)
📊 Divides data into equal parts or buckets (for percentile-based grouping).
🔣 Syntax:
NTILE(n) OVER(ORDER BY column)
📌 Example:
SELECT employeeid, employeename, salary,
NTILE(3) OVER(ORDER BY salary DESC) AS ntile_group
FROM employees;
📘 Module 30: Window Functions 5
⚖️ 6. 📉 AVG() (Window Average)
📐 Calculates average for a group but keeps the row.
🔣 Syntax:
AVG(column) OVER(PARTITION BY column)
📌 Examples:
SELECT *, AVG(salary) OVER(PARTITION BY department) AS avg_salary
FROM employees;
-- Rounded version
SELECT *, FLOOR(AVG(salary) OVER(PARTITION BY department)) AS avg_salary
FROM employees;
📘 Module 30: Window Functions 6
🔢 7. 🔁 COUNT() (Window Count)
📋 Counts how many rows are there in the partition.
🔣 Syntax:
COUNT(column) OVER(PARTITION BY column)
📌 Example:
SELECT *, COUNT(EmployeeID) OVER(PARTITION BY department) AS employee_count
FROM employees;
💰 8. ➕ SUM() (Window Total)
💸 Gives total sum of values in a group.
🔣 Syntax:
SUM(column) OVER(PARTITION BY column)
📌 Example:
SELECT *, SUM(Salary) OVER(PARTITION BY department) AS total_salary
FROM employees;
📘 Module 30: Window Functions 7
📈 9. 🔂 Running Total
📊 Running total is a cumulative sum row by row.
🔣 Syntax:
SUM(column) OVER(PARTITION BY column ORDER BY column)
📌 Example:
SELECT *,
SUM(Salary) OVER(PARTITION BY department ORDER BY Salary DESC) AS cumulative_salary
FROM employees
ORDER BY department, salary DESC;
🔁 10. ⬅️➡️ LAG() and LEAD()
⏮ LAG() → Gets value from previous row
⏭ LEAD() → Gets value from next row
🔣 Syntax:
LAG(column) OVER(PARTITION BY col ORDER BY col)
LEAD(column) OVER(PARTITION BY col ORDER BY col)
📌 Example:
SELECT *,
LAG(Salary) OVER(PARTITION BY department ORDER BY salary DESC) AS previous_salary,
LEAD(Salary) OVER(PARTITION BY department ORDER BY salary DESC) AS next_salary
FROM employees;
📘 Module 30: Window Functions 8
🧠 Key Points to Remember
✅ Window functions do not group data likeGROUP BY .
✅ You can use to group, but rows are still individual.
PARTITION BY
✅ Always use for meaningful results in ranking, cumulative total, etc.
ORDER BY
✅ LAG/LEAD are useful for comparing rows side by side.
✅ ROWS BETWEEN can be used for moving average, running max, min (advanced).
🧠 Additional Tips
🔹 You can use WHERE row_num = 1 trick with ROW_NUMBER to filter top entries per group
🔹 Can also be used in CTEs (Common Table Expressions)
🔹 Great for reporting, pagination, and analytical dashboards
📘 Module 30: Window Functions 9