Why window functions are needed in SQL - Visual Breakdown

Concept Flow - Why window functions are needed

Start with a table of data

↓

Need to calculate aggregates per row

↓

Without window functions: use GROUP BY

↓

Problem: GROUP BY loses row details

↓

Result: detailed rows + aggregate info

Window functions let us calculate totals or ranks for each row without losing the original row details, unlike GROUP BY which groups rows and hides individual data.

Execution Sample

SQL

SELECT employee, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM employees;

This query shows each employee's salary and the average salary of their department, keeping all employee rows.

Execution Table

Step	Action	Input Data	Window Function Calculation	Output Row
1	Read first employee row	{employee: 'Alice', department: 'Sales', salary: 5000}	Calculate AVG(salary) for Sales: (5000+6000)/2=5500	{Alice, Sales, 5000, 5500}
2	Read second employee row	{employee: 'Bob', department: 'Sales', salary: 6000}	Calculate AVG(salary) for Sales: 5500	{Bob, Sales, 6000, 5500}
3	Read third employee row	{employee: 'Charlie', department: 'HR', salary: 4000}	Calculate AVG(salary) for HR: 4000	{Charlie, HR, 4000, 4000}
4	All rows processed	N/A	N/A	Query complete with all rows and averages

💡 All rows processed, window function calculated average salary per department without grouping rows

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
Current Row	None	{Alice, Sales, 5000}	{Bob, Sales, 6000}	{Charlie, HR, 4000}	All rows processed
AVG(salary) OVER (PARTITION BY department)	None	5500 (Sales)	5500 (Sales)	4000 (HR)	Computed for each row

Key Moments - 2 Insights

Why can't we just use GROUP BY to get average salary per department?

GROUP BY combines rows into one per group, so you lose individual employee details. Window functions keep each row and add the aggregate info.

How does the window function know which rows to include in the average?

The PARTITION BY clause tells it to calculate the average separately for each department, so it only uses rows from the same department.

Visual Quiz - 3 Questions

Look at the execution table, what is the average salary for Sales at step 2?

A 5000

B 5500

C 6000

D 4000

💡 Hint

Check the 'Window Function Calculation' column at step 2 in the execution_table

At which step does the window function calculate the average salary for HR?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look for the row with department 'HR' in the 'Input Data' column in execution_table

If we removed PARTITION BY, what would happen to the average salary calculation?

A Average would be zero

B Query would fail with syntax error

C Average would be calculated over all employees, not per department

D Each row would show NULL for average

💡 Hint

PARTITION BY controls grouping for window functions, see concept_flow for grouping effect

Concept Snapshot

Window functions calculate aggregates like averages or ranks for each row
without grouping rows together.
Use OVER() with optional PARTITION BY to define groups.
This keeps all original rows and adds aggregate info.
Unlike GROUP BY, no rows are lost or combined.

Full Transcript

Window functions are needed because sometimes we want to calculate totals or averages for groups of rows but still keep each individual row visible. Normally, GROUP BY groups rows and returns one row per group, hiding individual details. Window functions solve this by calculating aggregates over a 'window' of rows related to each row, defined by PARTITION BY. This way, each row keeps its data and also shows the aggregate value for its group. For example, we can show each employee's salary and the average salary of their department in the same result. The execution steps show reading each row, calculating the average salary for that row's department, and outputting the row with the added average. This helps us understand why window functions are powerful and needed in SQL queries.