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.