Window Functions (OVER)
Overview
This is arguably the most important advanced SQL concept for Technical Interviews (OA rounds). Aggregate functions (like SUM) crush rows together, permanently losing the individual row data. A Window Function performs aggregate math (SUM, AVG, RANK), but preserves the individual rows. It opens a 'Window' to look at surrounding rows, does the math, and attaches the result directly to your current row.
Syntax
-- Syntax: function() OVER ( ... )
SELECT
emp_name,
department,
salary,
-- 1. Standard Aggregate (Crushes rows - ILLEGAL here without GROUP BY)
-- SUM(salary)
-- 2. Window Function (Legal! Calculates sum without crushing rows)
-- OVER() with no arguments calculates the sum of the ENTIRE table
SUM(salary) OVER () AS total_company_payroll
FROM employees;Common Pitfalls
- Trying to use a Window Function in the
WHEREclause (e.g.,WHERE ROW_NUMBER() OVER(...) = 1). This is fundamentally impossible. Window functions execute at the very end of the SQL pipeline, strictly during theSELECTphase, long afterWHEREhas finished filtering. To filter by a window function, you MUST wrap it in a CTE first. - Forgetting the
OVER()clause.AVG(salary)is a standard aggregate.AVG(salary) OVER()is a Window Function. TheOVERkeyword is what instructs the engine to preserve the row geometry.
Interview Questions
GROUP BY or HAVING clause?Because of SQL's strict logical execution order. GROUP BY and HAVING evaluate before the SELECT clause. Window Functions are evaluated inside the SELECT clause, meaning they literally do not exist yet when the grouping phases are running.
Real-World Example
Comparing every individual employee's salary to the company-wide average, without losing the employee's name.
SELECT
emp_name,
salary,
-- Get the global average
AVG(salary) OVER () AS global_avg,
-- Calculate the exact mathematical difference inline!
salary - AVG(salary) OVER () AS diff_from_avg
FROM employees;Check Your Knowledge
Test your understanding of Window Functions (OVER) with these quick questions.