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.