Q15: What are Window Functions and how do they differ from GROUP BY?
Window functions perform calculations across a set of table rows related to the current query row using the OVER() clause.
Unlike GROUP BY, which collapses grouped rows into a single summary output row, window functions compute aggregate or positional values while preserving individual row identities.
Comparing Employee Salary against Department Average
SELECT
employee_id,
department_id,
salary,
AVG(salary) OVER(PARTITION BY department_id) AS dept_avg_salary
FROM employees;💡 Every individual employee row is preserved while calculating the aggregate department average alongside it.
Key Takeaways
- Window functions compute set values without collapsing individual table rows.
- Use
PARTITION BYto divide records into calculation windows andORDER BYto set logical ordering.