Window function vs GROUP BY mental model in SQL - Hands-On Comparison

Understanding Window Functions vs GROUP BY in SQL

📖 Scenario: You work at a bookstore that tracks sales data. You want to analyze sales by each book and also see the total sales across all books. You will learn how to use GROUP BY to get totals per book and how to use window functions to get totals alongside each row.

🎯 Goal: Build SQL queries that first group sales by book using GROUP BY, then use a window function to show total sales per book and overall total sales in the same result.

📋 What You'll Learn

Create a table called sales with columns book (text) and copies_sold (integer) with given data

Write a GROUP BY query to get total copies sold per book

Add a variable to hold the overall total copies sold

Write a query using a window function SUM() OVER() to show total copies sold per book and overall total on each row

💡 Why This Matters

🌍 Real World

Bookstores and many businesses analyze sales data to understand product performance and overall revenue.

💼 Career

Knowing when to use GROUP BY versus window functions is key for data analysts and database developers to write efficient and insightful queries.

1

Create the sales table with data

Create a table called sales with columns book (text) and copies_sold (integer). Insert these rows exactly: ('Book A', 10), ('Book B', 5), ('Book A', 7), ('Book C', 3), ('Book B', 8).

SQL

-- Create table sales and insert data
-- Your code here

Hint

Use CREATE TABLE to define columns, then INSERT INTO with multiple rows.

2

Write a GROUP BY query to get total copies sold per book

Write a SQL query that selects book and the sum of copies_sold as total_copies from sales, grouping by book.

SQL

CREATE TABLE sales (book TEXT, copies_sold INTEGER);
INSERT INTO sales (book, copies_sold) VALUES
('Book A', 10),
('Book B', 5),
('Book A', 7),
('Book C', 3),
('Book B', 8);

-- Write GROUP BY query below
-- Your code here

Hint

Use SUM() to add copies sold and GROUP BY book to group rows by book.

3

Calculate overall total copies sold

Create a variable called overall_total that holds the total copies sold across all books by summing copies_sold from sales.

SQL

CREATE TABLE sales (book TEXT, copies_sold INTEGER);
INSERT INTO sales (book, copies_sold) VALUES
('Book A', 10),
('Book B', 5),
('Book A', 7),
('Book C', 3),
('Book B', 8);

SELECT book, SUM(copies_sold) AS total_copies
FROM sales
GROUP BY book;

-- Create overall_total variable below
-- Your code here

Hint

Use a WITH clause or a subquery to calculate the overall total.

4

Use a window function to show total copies per book and overall total

Write a SQL query that selects book, copies_sold, the sum of copies_sold over partition by book as total_per_book, and the sum of copies_sold over all rows as overall_total from sales.

SQL

CREATE TABLE sales (book TEXT, copies_sold INTEGER);
INSERT INTO sales (book, copies_sold) VALUES
('Book A', 10),
('Book B', 5),
('Book A', 7),
('Book C', 3),
('Book B', 8);

SELECT book, SUM(copies_sold) AS total_copies
FROM sales
GROUP BY book;

WITH overall AS (
  SELECT SUM(copies_sold) AS overall_total FROM sales
)
SELECT overall_total FROM overall;

-- Write window function query below
-- Your code here

Hint

Use SUM() OVER (PARTITION BY book) for total per book and SUM() OVER () for overall total.

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