Window function vs GROUP BY mental model in SQL - Visual Side-by-Side Comparison

Concept Flow - Window function vs GROUP BY mental model

Start with full table

↓

GROUP BY: collapse rows

↓

Aggregate per group

↓

Result: fewer rows

↓

Use result for analysis

Start with the full table, then choose aggregation. GROUP BY collapses rows into groups and aggregates, returning fewer rows. Window functions keep all rows but add aggregated info per row.

Execution Sample

SQL

SELECT dept, AVG(salary) FROM employees GROUP BY dept;

SELECT emp_id, dept, salary, AVG(salary) OVER (PARTITION BY dept) FROM employees;

First query groups employees by department and shows average salary per department. Second query shows each employee's salary plus the average salary of their department without collapsing rows.

Execution Table

Step	Query Type	Input Rows	Operation	Output Rows	Output Example
1	GROUP BY	10 employees	Group by dept, calculate AVG(salary)	3 rows (one per dept)	[{dept: 'HR', avg_salary: 6000}, {dept: 'IT', avg_salary: 8000}, {dept: 'Sales', avg_salary: 7000}]
2	Window Function	10 employees	Calculate AVG(salary) over dept partition, keep all rows	10 rows (same as input)	[{emp_id: 1, dept: 'HR', salary: 5500, avg_salary: 6000}, ...]
3	GROUP BY	3 groups	No further grouping	3 rows	Final output
4	Window Function	10 rows	No row collapse	10 rows	Final output with extra column
5	End	-	-	-	Execution complete

💡 GROUP BY stops after grouping and aggregation, reducing rows; Window function continues with all rows, adding aggregated info.

Variable Tracker

Variable	Start	After GROUP BY	After Window Function	Final
Input Rows	10 employees	3 groups	10 employees	3 groups (GROUP BY) / 10 employees (Window)
Output Rows	N/A	3 rows	10 rows	3 rows (GROUP BY) / 10 rows (Window)
Aggregated Value (AVG salary)	N/A	Per group	Per row in partition	Per group (GROUP BY) / Per row (Window)

Key Moments - 3 Insights

Why does GROUP BY reduce the number of rows but window functions do not?

GROUP BY groups rows and returns one row per group (see execution_table step 1), while window functions calculate aggregates but keep all original rows (step 2).

Can I use window functions without GROUP BY?

Yes, window functions work on the full set or partitions without collapsing rows, unlike GROUP BY which always reduces rows (see variable_tracker Output Rows).

Why do window functions show aggregate values repeated for each row?

Because window functions compute aggregates over partitions but keep each row intact, so the aggregate appears alongside each row in that partition (execution_table step 2 output example).

Visual Quiz - 3 Questions

Look at the execution table, how many rows does the GROUP BY query output after aggregation?

A 1 row

B 10 rows

C 3 rows

D Depends on the number of employees

💡 Hint

Check execution_table row 1 under Output Rows

According to the variable tracker, what happens to the number of rows after applying a window function?

A Rows stay the same as input

B Rows double

C Rows reduce to number of groups

D Rows become zero

💡 Hint

Look at 'Output Rows' variable in variable_tracker after Window Function

If you want to see average salary per department but keep all employee details, which should you use?

A GROUP BY

B Window function

C Neither

D Both at the same time

💡 Hint

Refer to execution_sample showing window function output with all rows

Concept Snapshot

GROUP BY groups rows and returns one row per group with aggregates.
Window functions compute aggregates over partitions but keep all rows.
GROUP BY reduces row count; window functions do not.
Use GROUP BY to summarize data.
Use window functions to add aggregate info alongside each row.

Full Transcript

This visual execution compares GROUP BY and window functions in SQL. Starting with a full table, GROUP BY groups rows by a column and aggregates, returning fewer rows. Window functions calculate aggregates over partitions but keep all original rows, adding aggregate info per row. The execution table shows GROUP BY reducing 10 employee rows to 3 department groups, while the window function keeps all 10 rows but adds average salary per department. Variable tracking confirms row counts and aggregate values differ. Key moments clarify why GROUP BY collapses rows and window functions do not, and when to use each. The quiz tests understanding of row counts and use cases. The snapshot summarizes the core difference: GROUP BY summarizes by collapsing rows; window functions add aggregate info without collapsing rows.