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.

Practice

1. What is the main difference between a GROUP BY clause and a window function in SQL?

easy

A. Both GROUP BY and window functions always return the same number of rows.

B. GROUP BY keeps all rows and adds summary data; window functions group rows and return one row per group.

C. GROUP BY groups rows and returns one row per group; window functions keep all rows and add summary data.

D. Window functions cannot perform calculations like sums or averages.

Solution

1. Step 1: Understand GROUP BY behavior

   GROUP BY groups rows by specified columns and returns one row per group with aggregated results.

2. Step 2: Understand window function behavior

   Window functions calculate aggregates over partitions but keep all original rows visible, adding summary columns.

3. Final Answer:

   GROUP BY groups rows and returns one row per group; window functions keep all rows and add summary data. -> Option C

4. Quick Check:

   Window functions keep detail, GROUP BY groups rows [OK]

Hint: Window functions keep rows; GROUP BY collapses them [OK]

Common Mistakes:

• Thinking window functions reduce rows like GROUP BY
• Believing GROUP BY keeps all original rows
• Assuming window functions can't do aggregates

2. Which of the following SQL queries correctly uses a window function to calculate a running total of sales per employee?

easy

A. SELECT employee_id, sale_amount, SUM(sale_amount) OVER (ORDER BY sale_date) AS running_total FROM sales GROUP BY employee_id;

B. SELECT employee_id, sale_amount, SUM(sale_amount) GROUP BY employee_id ORDER BY sale_date AS running_total FROM sales;

C. SELECT employee_id, sale_amount, SUM(sale_amount) FROM sales WINDOW (PARTITION BY employee_id ORDER BY sale_date) AS running_total;

D. SELECT employee_id, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total FROM sales;

Solution

1. Step 1: Identify correct window function syntax

   SELECT employee_id, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total FROM sales; uses SUM() OVER (PARTITION BY ... ORDER BY ...), which is the correct syntax for running totals per employee.

2. Step 2: Check other options for syntax errors

   Options B, C, and D misuse GROUP BY or WINDOW clauses incorrectly, causing syntax errors or wrong logic.

3. Final Answer:

   The correct query: SELECT employee_id, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total FROM sales; -> Option D

4. Quick Check:

   Window function syntax = SELECT employee_id, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total FROM sales; [OK]

Hint: Window functions use OVER(), GROUP BY does not [OK]

Common Mistakes:

• Using GROUP BY with window function syntax
• Misplacing WINDOW clause
• Omitting OVER() for window functions

3. Given the table sales(employee_id, sale_amount) with data:
1, 100
1, 200
2, 150
2, 50
What is the output of this query?

SELECT employee_id, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id) AS total_sales FROM sales ORDER BY employee_id, sale_amount;

medium

A. 1, 100, 3001, 200, 3002, 50, 2002, 150, 200

B. 1, 100, 1001, 200, 2002, 50, 502, 150, 150

C. 1, 100, 3001, 200, 5002, 50, 2002, 150, 350

D. 1, 100, 3001, 200, 3002, 50, 1502, 150, 150

Solution

1. Step 1: Calculate total sales per employee using window function

   For employee 1: 100 + 200 = 300; for employee 2: 150 + 50 = 200.

2. Step 2: Assign total_sales to each row without grouping

   Each row for employee 1 shows 300, each for employee 2 shows 200, preserving all rows.

3. Final Answer:

   Rows show total sales per employee repeated: 1,100,300; 1,200,300; 2,50,200; 2,150,200. -> Option A

4. Quick Check:

   Window sums repeat per row, not grouped [OK]

Hint: Window sums repeat per row; GROUP BY sums once per group [OK]

Common Mistakes:

• Confusing window sum with grouped sum
• Expecting fewer rows after window sum
• Mixing order of rows in output

4. Consider this query:

SELECT employee_id, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total FROM sales GROUP BY employee_id, sale_amount, sale_date;

What is the main issue with this query?

medium

A. The query will run but return incorrect running totals.

B. The window function must be replaced with a GROUP BY aggregate.

C. The GROUP BY clause is unnecessary but allowed here.

D. It will cause a syntax error because window functions cannot be used with GROUP BY.

Solution

1. Step 1: Window functions execute after GROUP BY

   Window functions are applied to the result set produced after GROUP BY and HAVING, so the query runs without syntax error.

2. Step 2: GROUP BY distorts the running total

   GROUP BY employee_id, sale_amount, sale_date collapses multiple rows sharing the same values in these columns into one row each before the window function computes the running total, leading to incorrect sums if duplicate combinations exist.

3. Final Answer:

   The query will run but return incorrect running totals. -> Option A

4. Quick Check:

   GROUP BY collapses rows before window functions run [OK]

Hint: Window functions run after GROUP BY; grouping can collapse rows unexpectedly [OK]

Common Mistakes:

• Assuming syntax error when mixing GROUP BY and window functions
• Thinking GROUP BY has no effect on subsequent window calculations
• Overlooking row collapse from GROUP BY before window execution

5. You want to find each employee's sale amount along with the average sale amount of their department, but keep all individual sales visible. Which approach is best?

hard

A. Use GROUP BY department_id to get average sales, then join back to sales table.

B. Use a window function like AVG(sale_amount) OVER (PARTITION BY department_id) to add average per row.

C. Use GROUP BY employee_id to get average sales per employee.

D. Use a subquery with GROUP BY employee_id and join on employee_id.

Solution

1. Step 1: Understand requirement to keep all individual sales

   We want to keep each sale row visible, not collapse them by grouping.

2. Step 2: Choose window function to add department average per row

   Window function AVG() OVER (PARTITION BY department_id) adds average without grouping rows.

3. Step 3: Compare with other options

   Options A, C, D use GROUP BY which collapses rows or require joins, making query complex.

4. Final Answer:

   Use window function to add department average per row, keeping all sales visible. -> Option B

5. Quick Check:

   Window functions keep detail and add group info [OK]

Hint: Window functions add group info without hiding rows [OK]

Common Mistakes:

• Using GROUP BY and losing individual rows
• Overcomplicating with joins instead of window functions
• Grouping by employee instead of department