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.