Why window functions are needed in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use window functions instead of GROUP BY?

Which of the following best explains why window functions are needed instead of just using GROUP BY?

A GROUP BY can only be used with numeric columns, but window functions work with all data types.

B Window functions automatically create indexes to speed up queries, unlike GROUP BY.

C Window functions allow calculations across rows without collapsing the result into grouped rows, preserving individual row details.

D Window functions replace the need for JOINs in combining tables.

💡 Hint

Think about whether the original rows remain visible after aggregation.

GROUP BY groups rows and returns one row per group, losing individual row details. Window functions compute values across rows but keep each row visible.

❓ query_result

intermediate

Output of ROW_NUMBER() window function

Given a table sales with columns id, region, and amount, what is the output of this query?

SELECT id, region, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank FROM sales;

SQL

CREATE TABLE sales (id INT, region VARCHAR(10), amount INT);
INSERT INTO sales VALUES (1, 'East', 100), (2, 'East', 200), (3, 'West', 150), (4, 'West', 50);

A Rows numbered starting at 1 within each region, ordered by amount descending.

B Rows numbered globally from 1 to total rows, ignoring region.

C Rows numbered starting at 1 within each region, ordered by amount ascending.

D Rows numbered randomly without any order.

💡 Hint

Look at the PARTITION BY and ORDER BY inside the OVER clause.

ROW_NUMBER() assigns a unique number starting at 1 for each partition (region), ordered by amount descending.

📝 Syntax

advanced

Identify the syntax error in window function usage

Which option contains a syntax error in using a window function?

SELECT id, SUM(amount) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM sales;

A SELECT id, SUM(amount) OVER (PARTITION BY region ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM sales;

B SELECT id, SUM(amount) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM sales;

C SELECT id, SUM(amount) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM sales;

D SELECT id, SUM(amount) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW) FROM sales;

💡 Hint

Check the order of frame boundaries in the ROWS clause.

The frame must start with a preceding boundary and end with a following or current row boundary. 'UNBOUNDED FOLLOWING' cannot come before 'CURRENT ROW'.

❓ optimization

advanced

Why prefer window functions over self-joins for running totals?

Which reason best explains why window functions are preferred over self-joins to calculate running totals?

A Window functions are more efficient and easier to write than self-joins for cumulative calculations.

B Self-joins cannot be used with ORDER BY clauses.

C Window functions do not require indexes, while self-joins do.

D Self-joins always produce incorrect results for running totals.

💡 Hint

Think about query performance and code simplicity.

Window functions compute running totals efficiently in a single pass, while self-joins require multiple joins and are slower.

🔧 Debug

expert

Diagnose the error in this window function query

What error will this query produce?

SELECT id, region, amount, RANK() OVER (PARTITION BY region ORDER BY amount) AS rank FROM sales WHERE rank <= 2;

A Runtime error due to missing GROUP BY clause.

B Syntax error because window functions cannot be used in WHERE clause.

C No error, query runs and filters rows with rank less than or equal to 2.

D Error because RANK() requires a GROUP BY clause.

💡 Hint

Consider when window functions are evaluated in SQL query processing.

Window functions are computed after WHERE clause filtering, so they cannot be used directly in WHERE. Use a subquery or HAVING instead.

Practice

1. Why are window functions needed in SQL?
SELECT with window functions can do what that regular aggregation cannot?

easy

A. They speed up query execution by skipping indexes.

B. They allow calculations across rows without hiding individual rows.

C. They automatically create new tables for results.

D. They replace the need for JOIN operations.

Solution

1. Step 1: Understand aggregation vs window functions

   Regular aggregation groups rows and returns fewer rows, hiding details.

2. Step 2: Identify window function behavior

   Window functions calculate values across rows but keep all original rows visible.

3. Final Answer:

   They allow calculations across rows without hiding individual rows. -> Option B

4. Quick Check:

   Window functions keep rows visible = A [OK]

Hint: Window functions keep all rows, aggregations reduce rows [OK]

Common Mistakes:

• Thinking window functions create new tables
• Confusing window functions with JOINs
• Believing window functions speed up queries

2. Which of the following is the correct syntax to use a window function in SQL?

easy

A. SELECT name, RANK() OVER (ORDER BY score) FROM players;

B. SELECT name, RANK(score) FROM players ORDER BY score;

C. SELECT name, RANK() FROM players GROUP BY score;

D. SELECT name, RANK OVER (ORDER BY score) FROM players;

Solution

1. Step 1: Recall window function syntax

   Window functions use function() OVER (PARTITION BY ... ORDER BY ...).

2. Step 2: Check each option

   SELECT name, RANK() OVER (ORDER BY score) FROM players; correctly uses RANK() OVER (ORDER BY score). Others miss parentheses or OVER keyword.

3. Final Answer:

   SELECT name, RANK() OVER (ORDER BY score) FROM players; -> Option A

4. Quick Check:

   Window function syntax = function() OVER (...) [OK]

Hint: Window functions always use OVER() clause [OK]

Common Mistakes:

• Omitting parentheses after function name
• Forgetting the OVER keyword
• Using GROUP BY instead of window functions

3. Given the table sales with columns id, region, and amount, what will this query return?

SELECT id, region, amount, SUM(amount) OVER (PARTITION BY region ORDER BY id) AS running_total FROM sales ORDER BY id;

medium

A. A running total of sales amount per region, ordered by id, showing all rows.

B. Total sales amount per region, one row per region only.

C. Sum of all sales amounts ignoring region, one total value.

D. Syntax error due to missing GROUP BY clause.

Solution

1. Step 1: Understand SUM() OVER with PARTITION and ORDER

   SUM() OVER (PARTITION BY region ORDER BY id) calculates a running total per region ordered by id.

2. Step 2: Check output rows

   Window functions keep all rows, so all sales rows appear with running totals.

3. Final Answer:

   A running total of sales amount per region, ordered by id, showing all rows. -> Option A

4. Quick Check:

   Running total per group = A [OK]

Hint: SUM() OVER with PARTITION BY gives running totals per group [OK]

Common Mistakes:

• Thinking it returns one row per group
• Confusing window function with GROUP BY aggregation
• Expecting syntax error without GROUP BY

4. Identify the error in this SQL query using a window function:

SELECT employee_id, department, ROW_NUMBER() ORDER BY salary FROM employees;

medium

A. ORDER BY cannot be used inside ROW_NUMBER().

B. employee_id must be inside ROW_NUMBER() function.

C. ROW_NUMBER() requires GROUP BY clause.

D. Missing OVER() clause after ROW_NUMBER() function.

Solution

1. Step 1: Check window function syntax

   ROW_NUMBER() must be followed by OVER() with optional ORDER BY inside it.

2. Step 2: Identify missing OVER()

   The query uses ROW_NUMBER() ORDER BY salary without OVER(), causing syntax error.

3. Final Answer:

   Missing OVER() clause after ROW_NUMBER() function. -> Option D

4. Quick Check:

   Window functions need OVER() [OK]

Hint: Always add OVER() after window functions [OK]

Common Mistakes:

• Placing ORDER BY outside OVER()
• Thinking GROUP BY is needed for ROW_NUMBER()
• Misunderstanding function arguments

5. You want to find each employee's salary rank within their department, but also show all employees with their salaries and ranks. Which SQL approach best achieves this?

hard

A. Use a subquery to calculate ranks and join back to employees table.

B. Use GROUP BY department, salary with RANK() to get ranks per department.

C. Use RANK() OVER (PARTITION BY department ORDER BY salary DESC) to rank salaries per department while keeping all rows.

D. Use ORDER BY salary DESC with LIMIT to get top salaries per department.

Solution

1. Step 1: Understand the requirement

   We want ranks per department and all employees listed with their ranks.

2. Step 2: Evaluate options

   Use RANK() OVER (PARTITION BY department ORDER BY salary DESC) to rank salaries per department while keeping all rows. uses RANK() OVER (PARTITION BY department ORDER BY salary DESC) which ranks salaries per department and keeps all rows visible.

3. Final Answer:

   Use RANK() OVER (PARTITION BY department ORDER BY salary DESC) to rank salaries per department while keeping all rows. -> Option C

4. Quick Check:

   Window function with PARTITION keeps all rows and ranks [OK]

Hint: Use RANK() OVER PARTITION BY to rank within groups [OK]

Common Mistakes:

• Using GROUP BY loses individual rows
• Using subqueries unnecessarily complicates query
• LIMIT filters rows, not ranks all