Why advanced window functions matter in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Calculate running total with window functions

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

SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date) AS running_total FROM sales ORDER BY sale_date;

SQL

CREATE TABLE sales (sale_date DATE, amount INT);
INSERT INTO sales VALUES
('2024-01-01', 100),
('2024-01-02', 200),
('2024-01-03', 150);

A [{"sale_date": "2024-01-01", "amount": 100, "running_total": 100}, {"sale_date": "2024-01-02", "amount": 200, "running_total": 300}, {"sale_date": "2024-01-03", "amount": 150, "running_total": 450}]

B [{"sale_date": "2024-01-01", "amount": 100, "running_total": 100}, {"sale_date": "2024-01-02", "amount": 200, "running_total": 200}, {"sale_date": "2024-01-03", "amount": 150, "running_total": 150}]

C [{"sale_date": "2024-01-01", "amount": 100, "running_total": 450}, {"sale_date": "2024-01-02", "amount": 200, "running_total": 350}, {"sale_date": "2024-01-03", "amount": 150, "running_total": 150}]

D [{"sale_date": "2024-01-01", "amount": 100, "running_total": null}, {"sale_date": "2024-01-02", "amount": 200, "running_total": null}, {"sale_date": "2024-01-03", "amount": 150, "running_total": null}]

💡 Hint

Think about how the SUM() window function accumulates values ordered by date.

The SUM() OVER (ORDER BY sale_date) calculates a running total by adding amounts in order of sale_date. So the totals accumulate as 100, then 100+200=300, then 300+150=450.

❓ query_result

intermediate

Find the rank of employees by salary within departments

Consider an employees table with columns department and salary. What is the output of this query?

SELECT department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank FROM employees ORDER BY department, salary_rank;

SQL

CREATE TABLE employees (department TEXT, salary INT);
INSERT INTO employees VALUES
('Sales', 5000),
('Sales', 7000),
('Sales', 7000),
('HR', 4000),
('HR', 4500);

A [{"department": "HR", "salary": 4500, "salary_rank": 2}, {"department": "HR", "salary": 4000, "salary_rank": 1}, {"department": "Sales", "salary": 7000, "salary_rank": 1}, {"department": "Sales", "salary": 7000, "salary_rank": 1}, {"department": "Sales", "salary": 5000, "salary_rank": 2}]

B [{"department": "HR", "salary": 4500, "salary_rank": 1}, {"department": "HR", "salary": 4000, "salary_rank": 1}, {"department": "Sales", "salary": 7000, "salary_rank": 1}, {"department": "Sales", "salary": 7000, "salary_rank": 2}, {"department": "Sales", "salary": 5000, "salary_rank": 3}]

C [{"department": "HR", "salary": 4500, "salary_rank": 1}, {"department": "HR", "salary": 4000, "salary_rank": 2}, {"department": "Sales", "salary": 7000, "salary_rank": 1}, {"department": "Sales", "salary": 7000, "salary_rank": 1}, {"department": "Sales", "salary": 5000, "salary_rank": 3}]

D [{"department": "HR", "salary": 4500, "salary_rank": 1}, {"department": "HR", "salary": 4000, "salary_rank": 2}, {"department": "Sales", "salary": 7000, "salary_rank": 2}, {"department": "Sales", "salary": 7000, "salary_rank": 2}, {"department": "Sales", "salary": 5000, "salary_rank": 3}]

💡 Hint

RANK() assigns the same rank to ties and skips ranks after ties.

Within each department, salaries are ranked descending. Ties get the same rank, and the next rank skips accordingly. So two 7000 salaries in Sales get rank 1, next salary 5000 gets rank 3.

📝 Syntax

advanced

Identify the syntax error in window function usage

Which option contains a syntax error in the use of window functions?

A SELECT name, AVG(score) OVER (PARTITION BY team ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM players;

B SELECT name, MAX(score) OVER (PARTITION BY team ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM players;

C SELECT name, SUM(score) OVER (ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM players;

D SELECT name, COUNT(*) OVER (PARTITION BY team ORDER BY date ROWS UNBOUNDED PRECEDING) FROM players;

💡 Hint

Check the framing clause syntax carefully.

Option D is missing the keyword BETWEEN in the frame clause. Correct syntax requires BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

❓ optimization

advanced

Optimize query using window functions instead of subqueries

Which query is more efficient for calculating each employee's salary difference from the department average?

SQL

CREATE TABLE employees (id INT, department TEXT, salary INT);
INSERT INTO employees VALUES (1, 'HR', 4000), (2, 'HR', 4500), (3, 'Sales', 7000), (4, 'Sales', 5000);

A SELECT id, department, salary, salary - (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department) AS diff FROM employees e1;

B SELECT id, department, salary, salary - AVG(salary) OVER (PARTITION BY department) AS diff FROM employees;

C SELECT id, department, salary, salary - SUM(salary) OVER (PARTITION BY department) / COUNT(*) OVER (PARTITION BY department) AS diff FROM employees;

D SELECT id, department, salary, salary - MAX(salary) OVER (PARTITION BY department) AS diff FROM employees;

💡 Hint

Window functions can avoid repeated subqueries for each row.

Option B uses AVG() as a window function partitioned by department, calculating the average once per partition efficiently. Option B runs a subquery per row, which is less efficient.

🧠 Conceptual

expert

Why use advanced window functions over GROUP BY?

Which statement best explains why advanced window functions matter compared to simple GROUP BY aggregation?

A Window functions allow calculations across rows related to the current row without collapsing rows, enabling detailed analysis within groups.

B Window functions always run faster than GROUP BY because they use indexes automatically.

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

D Window functions replace the need for JOINs in all cases, simplifying queries.

💡 Hint

Think about how window functions keep rows while adding calculations.

Window functions compute aggregates or rankings over partitions without reducing the number of rows, unlike GROUP BY which groups and collapses rows. This allows more detailed and flexible analysis.

Practice

1. What is the main advantage of using advanced window functions in SQL compared to simple aggregate functions?

easy

A. They only work on single rows without considering others.

B. They automatically create indexes for faster queries.

C. They allow calculations across rows without collapsing the result into groups.

D. They replace the need for JOIN operations.

Solution

1. Step 1: Understand aggregate functions behavior

   Aggregate functions like SUM or AVG group rows and return one result per group, collapsing data.

2. Step 2: Understand window functions behavior

   Window functions perform calculations across rows but keep each row visible, without grouping them.

3. Final Answer:

   They allow calculations across rows without collapsing the result into groups. -> Option C

4. Quick Check:

   Window functions = calculations without grouping [OK]

Hint: Window functions keep rows, aggregates collapse them [OK]

Common Mistakes:

• Thinking window functions group rows like aggregates
• Confusing window functions with JOINs
• Assuming window functions only work on single rows

2. Which of the following is the correct syntax to calculate a running total of sales using a window function?

easy

A. SELECT SUM(sales) OVER (ORDER BY date) AS running_total FROM sales_data;

B. SELECT SUM(sales) GROUP BY date ORDER BY running_total FROM sales_data;

C. SELECT RUNNING_TOTAL(sales) FROM sales_data ORDER BY date;

D. SELECT sales + PREVIOUS(sales) FROM sales_data;

Solution

1. Step 1: Identify correct window function syntax

   SUM() OVER (ORDER BY column) computes running total ordered by that column.

2. Step 2: Check other options for syntax errors

   The other options use invalid SQL syntax or functions that don't exist.

3. Final Answer:

   SELECT SUM(sales) OVER (ORDER BY date) AS running_total FROM sales_data; -> Option A

4. Quick Check:

   SUM() OVER (ORDER BY ...) = running total [OK]

Hint: Use SUM() OVER (ORDER BY ...) for running totals [OK]

Common Mistakes:

• Using GROUP BY instead of OVER()
• Trying non-existent functions like RUNNING_TOTAL()
• Adding values manually without window syntax

3. Given the table employees with columns department, salary, and name, what will the following query return?

SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;

medium

A. Calculates total salary per department.

B. Ranks employees by salary across all departments combined.

C. Returns employees sorted by name with no ranking.

D. Ranks employees by salary within each department, highest salary gets rank 1.

Solution

1. Step 1: Understand PARTITION BY usage

   PARTITION BY department groups rows by department for the window function.

2. Step 2: Understand RANK() OVER (ORDER BY salary DESC)

   Ranks salaries in descending order within each department, highest salary gets rank 1.

3. Final Answer:

   Ranks employees by salary within each department, highest salary gets rank 1. -> Option D

4. Quick Check:

   RANK() + PARTITION BY = rank within groups [OK]

Hint: PARTITION BY groups, ORDER BY sets rank order [OK]

Common Mistakes:

• Thinking RANK() ignores PARTITION BY
• Confusing RANK() with total aggregation
• Assuming sorting by name affects rank

4. Identify the error in this SQL query that tries to calculate a cumulative sum of sales per region:

SELECT region, sales, SUM(sales) OVER (PARTITION region ORDER BY date) AS cumulative_sales FROM sales_data;

medium

A. Missing parentheses around PARTITION BY clause.

B. Incorrect keyword; should be PARTITION BY instead of PARTITION.

C. ORDER BY cannot be used inside window functions.

D. SUM() cannot be used as a window function.

Solution

1. Step 1: Check window function syntax

   The correct syntax requires PARTITION BY, not just PARTITION.

2. Step 2: Validate other parts

   ORDER BY inside OVER() is valid for running totals; SUM() is valid as window function.

3. Final Answer:

   Incorrect keyword; should be PARTITION BY instead of PARTITION. -> Option B

4. Quick Check:

   Use PARTITION BY, not PARTITION alone [OK]

Hint: Use PARTITION BY, not just PARTITION [OK]

Common Mistakes:

• Omitting BY after PARTITION
• Thinking ORDER BY is invalid in window functions
• Believing SUM() can't be a window function

5. You want to find the difference in salary between each employee and the highest salary in their department using window functions. Which query correctly achieves this?

hard

A. SELECT name, department, salary, MAX(salary) OVER (PARTITION BY department) - salary AS diff FROM employees;

B. SELECT name, department, salary, salary - MAX(salary) OVER (PARTITION BY department) AS diff FROM employees;

C. SELECT name, department, salary, salary - MAX(salary) FROM employees GROUP BY department;

D. SELECT name, department, salary, salary - MAX(salary) FROM employees;

Solution

1. Step 1: Use MAX() as window function partitioned by department

   MAX(salary) OVER (PARTITION BY department) gives highest salary per department for each row.

2. Step 2: Calculate difference as highest salary minus employee salary

   Subtract employee salary from max salary to get positive difference.

3. Step 3: Check options for correct calculation and syntax

   SELECT name, department, salary, MAX(salary) OVER (PARTITION BY department) - salary AS diff FROM employees; correctly subtracts salary from max salary per department; SELECT name, department, salary, salary - MAX(salary) OVER (PARTITION BY department) AS diff FROM employees; reverses subtraction; The remaining options misuse GROUP BY or lack window function.

4. Final Answer:

   SELECT name, department, salary, MAX(salary) OVER (PARTITION BY department) - salary AS diff FROM employees; -> Option A

5. Quick Check:

   MAX() OVER PARTITION BY + subtraction = difference [OK]

Hint: Subtract salary from MAX() OVER partition for difference [OK]

Common Mistakes:

• Reversing subtraction order
• Using GROUP BY instead of window functions
• Omitting PARTITION BY in MAX()