Window vs Aggregate Functions in SQL
💡 Concept Name
Window functions compute values across a set of rows related to the current row, while aggregate functions group rows into single summary values.
📘 Quick Intro
Aggregate functions like SUM(), AVG(), and COUNT() summarize data by collapsing groups into a single row. Window functions, using the OVER() clause, let you perform row-wise operations like running totals or ranks without collapsing data.
🧠 Analogy / Short Story
Imagine you're in a classroom. Aggregate functions are like the teacher announcing the average grade of the entire class — one result for everyone. Window functions are like showing each student their own average, class rank, or how they compare to peers — individualized insights while still seeing all classmates around.
🔧 Technical Explanation
- Aggregate Functions: Return one row per group. Examples:
SUM(),COUNT(),AVG(). - Window Functions: Use
OVER()to retain original rows while adding computed values likeRANK(),ROW_NUMBER(),SUM() OVER(). - Aggregate functions require
GROUP BY; window functions do not. - Window functions allow calculations across a "window frame" — subsets defined using
PARTITION BYandORDER BY.
🎯 Purpose & Use Case
- ✅ Use aggregate functions for summarizing groups of data (e.g., total sales by region).
- ✅ Use window functions for ranking, moving averages, or percentiles without collapsing data.
- ✅ Window functions are ideal for dashboards or per-row comparisons.
- ✅ Aggregate functions are best for grouped summaries and report totals.
💻 Real Code Example
-- Aggregate Function: Total sales per department
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;
-- Window Function: Total salary of department for each employee
SELECT Name, Department, Salary,
SUM(Salary) OVER (PARTITION BY Department) AS DeptTotal
FROM Employees;
-- Window Function: Rank employees by salary within departments
SELECT Name, Department, Salary,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank
FROM Employees;
❓ Interview Q&A
Q1: What is the main difference between window and aggregate functions?
A: Aggregate functions return one value per group; window functions return a value for each row while still using group context.
Q2: What clause is required with window functions?
A: The OVER() clause is mandatory with window functions.
Q3: Can aggregate functions be used with OVER()?
A: Yes, functions like SUM() or AVG() can be window functions if combined with OVER().
Q4: Does GROUP BY collapse rows?
A: Yes, GROUP BY reduces multiple rows into one row per group.
Q5: Can you filter rows after window functions?
A: Yes, use a subquery or CTE to filter based on window function outputs.
Q6: What does PARTITION BY do in window functions?
A: It divides the result set into partitions (like groups) over which the function operates.
Q7: When would you use ROW_NUMBER() vs RANK()?
A: Use ROW_NUMBER() for unique ranking; RANK() allows ties.
Q8: Can I use both aggregate and window functions in one query?
A: Yes, but you'll need subqueries or CTEs to avoid conflicts in row grouping.
Q9: Is OVER() needed for AVG() in aggregate queries?
A: No, it's needed only if you're using AVG() as a window function, not as a group-level aggregate.
Q10: Are window functions slower than aggregate functions?
A: Not necessarily. They can be optimized by indexes and partitioning strategies.
📝 MCQs
Q1. Which clause is required for a window function?
- GROUP BY
- WHERE
- ORDER BY
- OVER()
Q2. What does an aggregate function return?
- One row per row
- One row per group
- Windowed row
- Always NULL
Q3. Which function is a window function?
- SUM()
- AVG()
- ROW_NUMBER()
- MAX()
Q4. Which function requires GROUP BY?
- RANK()
- ROW_NUMBER()
- COUNT()
- LEAD()
Q5. What does PARTITION BY do?
- Sorts data
- Removes duplicates
- Divides data into subsets
- Joins tables
Q6. Can window functions return multiple rows?
- No
- Yes, they retain original rows
- Only with joins
- Only in views
Q7. Which gives total per employee's department?
- GROUP BY
- COUNT()
- SUM(Salary)
- SUM() OVER(PARTITION BY Department)
Q8. Is GROUP BY required in window functions?
- Yes
- Only with COUNT()
- No
- Depends on query
Q9. What function provides a unique row index?
- RANK()
- DENSE_RANK()
- ROW_NUMBER()
- NTILE()
Q10. Do window functions collapse data?
- Yes
- Sometimes
- No
- Only when partitioned
💡 Bonus Insight
You can combine window and aggregate logic using CTEs to get layered analytics — for example, calculate department total (aggregate) and then show each employee's percentage contribution (window). This is powerful for dashboards and analytics-driven applications.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!