Window function vs GROUP BY mental model in SQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Difference in output rows: GROUP BY vs Window Function

Consider a table sales with columns region, salesperson, and amount. Which statement best describes the difference in output rows when using GROUP BY region versus using SUM(amount) OVER (PARTITION BY region)?

A GROUP BY returns one row per region; window function returns one row per original row with aggregated values repeated.

B GROUP BY returns one row per salesperson; window function returns one row per region only.

C GROUP BY returns all rows unchanged; window function removes duplicates.

D GROUP BY and window function always return the same number of rows.

💡 Hint

Think about how GROUP BY collapses rows versus how window functions keep all rows.

GROUP BY groups rows and returns one row per group, aggregating values. Window functions compute aggregates but keep all original rows, repeating the aggregate value for each row in the partition.

❓ query_result

intermediate

Output rows count with GROUP BY vs window function

Given the table orders with 10 rows, what is the number of rows returned by these queries?

1) SELECT region, SUM(amount) FROM orders GROUP BY region;
2) SELECT region, amount, SUM(amount) OVER (PARTITION BY region) FROM orders;

A 1) 10 rows; 2) 10 rows

B 1) 10 rows; 2) Number of distinct regions

C 1) 1 row; 2) 10 rows

D 1) Number of distinct regions; 2) 10 rows

💡 Hint

GROUP BY reduces rows to groups; window functions keep all rows.

GROUP BY returns one row per group (region). Window function returns all original rows with an added column showing the sum per region.

📝 Syntax

advanced

Correct use of window function syntax

Which SQL query correctly uses a window function to calculate the running total of amount ordered by order_date?

SQL

SELECT order_id, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total FROM orders;

A SELECT order_id, amount, SUM(amount) PARTITION BY order_date FROM orders;

B SELECT order_id, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total FROM orders;

C SELECT order_id, amount, SUM(amount) GROUP BY order_date FROM orders;

D SELECT order_id, amount, SUM(amount) OVER (PARTITION order_date) FROM orders;

💡 Hint

Window functions require OVER() with correct clauses like ORDER BY or PARTITION BY.

Option B correctly uses SUM() as a window function with ORDER BY inside OVER(). Others have syntax errors or misuse clauses.

❓ optimization

advanced

Performance impact of GROUP BY vs window function

Which statement about performance is generally true when comparing GROUP BY and window functions on large datasets?

A GROUP BY usually reduces data early, often improving performance; window functions process all rows, which can be slower.

B Window functions always run faster because they do not group data.

C GROUP BY and window functions have identical performance on large datasets.

D Window functions reduce the number of rows, improving performance over GROUP BY.

💡 Hint

Think about how grouping reduces rows versus window functions keeping all rows.

GROUP BY reduces rows early, which can speed up queries. Window functions keep all rows and compute aggregates over partitions, which can be more costly.

🔧 Debug

expert

Identify error in window function usage

What error will this query produce?

SELECT region, salesperson, SUM(amount) OVER () FROM sales GROUP BY region;

A No error; query runs and returns grouped sums

B ERROR: window function cannot be used with GROUP BY

C ERROR: column "salesperson" must appear in the GROUP BY clause or be used in an aggregate function

D ERROR: missing PARTITION BY clause in window function

💡 Hint

Check which columns are selected and how GROUP BY works with non-aggregated columns.

The query selects salesperson but does not group by it or aggregate it, causing an error. Window functions can be used with GROUP BY but columns must be valid.

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