OVER clause with PARTITION BY in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate running total per department

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

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

SQL

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

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

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

C [{"department": "Clothing", "sale_date": "2024-01-01", "amount": 200, "running_total": 200}, {"department": "Clothing", "sale_date": "2024-01-03", "amount": 100, "running_total": 300}, {"department": "Electronics", "sale_date": "2024-01-01", "amount": 100, "running_total": 250}, {"department": "Electronics", "sale_date": "2024-01-02", "amount": 150, "running_total": 250}]

D [{"department": "Clothing", "sale_date": "2024-01-01", "amount": 200, "running_total": 100}, {"department": "Clothing", "sale_date": "2024-01-03", "amount": 100, "running_total": 300}, {"department": "Electronics", "sale_date": "2024-01-01", "amount": 100, "running_total": 100}, {"department": "Electronics", "sale_date": "2024-01-02", "amount": 150, "running_total": 250}]

💡 Hint

Think about how the running total sums amounts within each department ordered by date.

The SUM() OVER (PARTITION BY department ORDER BY sale_date) calculates a running total for each department separately, adding amounts in order of sale_date.

📝 Syntax

intermediate

Identify the syntax error in window function usage

Which option contains a syntax error in using the OVER clause with PARTITION BY?

A SELECT name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;

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

C SELECT name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;

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

💡 Hint

Check the parentheses around the OVER clause.

The OVER clause must be followed by parentheses enclosing PARTITION BY and ORDER BY. Option B misses parentheses causing syntax error.

❓ optimization

advanced

Optimize query with multiple window functions

You want to calculate both the rank and the cumulative sum of sales per region ordered by date. Which query is more efficient?

A

SELECT region, sale_date, amount,
       RANK() OVER (PARTITION BY region ORDER BY sale_date) AS rank
FROM sales;

SELECT region, sale_date, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_sum
FROM sales;

B

SELECT region, sale_date, amount,
       RANK() OVER (PARTITION BY region) AS rank,
       SUM(amount) OVER (PARTITION BY region) AS running_sum
FROM sales;

C

SELECT region, sale_date, amount,
       RANK() OVER (ORDER BY sale_date) AS rank,
       SUM(amount) OVER (ORDER BY sale_date) AS running_sum
FROM sales;

D

SELECT region, sale_date, amount,
       RANK() OVER (PARTITION BY region ORDER BY sale_date) AS rank,
       SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_sum
FROM sales;

💡 Hint

Consider how many times the table is scanned and how partitioning affects results.

Option D calculates both window functions in a single query with correct partitioning and ordering, minimizing scans and producing correct results.

🔧 Debug

advanced

Find the cause of incorrect ranking results

A query uses RANK() OVER (PARTITION BY department) without ORDER BY. Why might the ranking be incorrect or unexpected?

A Because ORDER BY is missing, the ranking is arbitrary and not based on any column, causing unexpected results.

B Because PARTITION BY is missing, the ranking is calculated over the entire table, not per department.

C Because RANK() cannot be used with PARTITION BY, it causes a runtime error.

D Because the query lacks GROUP BY, the ranking cannot be computed correctly.

💡 Hint

Think about what ORDER BY does inside the OVER clause.

RANK() requires ORDER BY inside OVER to define the order for ranking. Without ORDER BY, the ranking is arbitrary and unpredictable.

🧠 Conceptual

expert

Understanding frame specification with PARTITION BY

Consider this query:

SELECT department, sale_date, amount,
       SUM(amount) OVER (PARTITION BY department ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_sum
FROM sales
ORDER BY department, sale_date;

What does the frame clause ROWS BETWEEN 1 PRECEDING AND CURRENT ROW do in this context?

A It sums the current row's amount and the next row's amount within the department partition.

B It sums all amounts from the start of the department partition up to the current row.

C It sums the current row's amount and the amount from the immediately previous sale_date within the same department.

D It sums only the current row's amount, ignoring other rows.

💡 Hint

Think about how the frame defines which rows are included relative to the current row.

The frame clause limits the window to the current row and one row before it, so the sum includes these two rows' amounts per department ordered by sale_date.