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

Challenge - 5 Problems

❓ query_result

intermediate

Calculate running total of sales per employee

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

SELECT employee_id, sale_date, amount, SUM(amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total FROM Sales ORDER BY employee_id, sale_date;

SQL

SELECT employee_id, sale_date, amount, SUM(amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total FROM Sales ORDER BY employee_id, sale_date;

A [{"employee_id":1,"sale_date":"2023-01-01","amount":100,"running_total":100},{"employee_id":1,"sale_date":"2023-01-05","amount":150,"running_total":250},{"employee_id":2,"sale_date":"2023-01-02","amount":200,"running_total":200},{"employee_id":2,"sale_date":"2023-01-06","amount":100,"running_total":300}]

B [{"employee_id":1,"sale_date":"2023-01-01","amount":100,"running_total":100},{"employee_id":1,"sale_date":"2023-01-05","amount":150,"running_total":150},{"employee_id":2,"sale_date":"2023-01-02","amount":200,"running_total":200},{"employee_id":2,"sale_date":"2023-01-06","amount":100,"running_total":100}]

C [{"employee_id":1,"sale_date":"2023-01-01","amount":100,"running_total":100},{"employee_id":1,"sale_date":"2023-01-05","amount":150,"running_total":250},{"employee_id":2,"sale_date":"2023-01-02","amount":200,"running_total":100},{"employee_id":2,"sale_date":"2023-01-06","amount":100,"running_total":300}]

D [{"employee_id":1,"sale_date":"2023-01-01","amount":100,"running_total":250},{"employee_id":1,"sale_date":"2023-01-05","amount":150,"running_total":250},{"employee_id":2,"sale_date":"2023-01-02","amount":200,"running_total":300},{"employee_id":2,"sale_date":"2023-01-06","amount":100,"running_total":300}]

💡 Hint

Think about how the SUM() function accumulates values ordered by sale_date for each employee separately.

The SUM() OVER clause with PARTITION BY employee_id and ORDER BY sale_date calculates a running total of sales for each employee ordered by the sale date. The running total adds the current amount to the sum of all previous amounts for that employee.

🧠 Conceptual

intermediate

Effect of ORDER BY in OVER clause without PARTITION BY

What does the ORDER BY clause inside the OVER() do when used without PARTITION BY?
Consider this query:

SELECT id, value, ROW_NUMBER() OVER (ORDER BY value DESC) AS rank FROM Items;

A It assigns a unique rank to each row based on descending value across the entire table.

B It resets the rank for each distinct value in the value column.

C It produces the same rank for all rows because there is no partition.

D It causes a syntax error because PARTITION BY is required with ORDER BY in OVER().

💡 Hint

Think about how window functions rank rows when no partition is specified.

Without PARTITION BY, the window function treats the entire table as one group. ORDER BY inside OVER() defines the order in which the function processes rows. ROW_NUMBER() assigns a unique rank based on that order.

📝 Syntax

advanced

Identify the syntax error in this window function

Which option contains the correct syntax for using the OVER clause with ORDER BY?
Given the query:

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM Employees;

A SELECT name, salary, RANK() OVER (PARTITION salary ORDER BY DESC) FROM Employees;

B SELECT name, salary, RANK() OVER ORDER BY salary DESC FROM Employees;

C SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM Employees;

D SELECT name, salary, RANK() OVER (ORDER salary DESC) FROM Employees;

💡 Hint

Check the placement and syntax of the OVER clause and ORDER BY inside it.

The correct syntax requires parentheses after OVER and ORDER BY inside those parentheses. Option C follows this syntax exactly.

❓ optimization

advanced

Optimizing window function with ORDER BY on large dataset

You have a large Transactions table and want to calculate a running total of amount ordered by transaction_date. Which approach is best for performance?

A Use a correlated subquery to sum amounts up to each transaction_date.

B Use SUM(amount) OVER (ORDER BY transaction_date) with an index on transaction_date.

C Use GROUP BY transaction_date and then join back to the original table.

D Calculate running total in application code after fetching all rows unordered.

💡 Hint

Window functions with proper indexing can be very efficient.

Using SUM() OVER (ORDER BY ...) with an index on the ordering column allows the database to efficiently compute running totals without expensive joins or subqueries.

🔧 Debug

expert

Why does this query produce unexpected results?

Consider this query:

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

Why might the running_total not reset for each employee?

A Because ORDER BY in OVER() must match ORDER BY in the main query to reset the total.

B Because sale_date values are not unique, causing incorrect running totals.

C Because SUM() cannot be used with ORDER BY in OVER() without GROUP BY.

D Because PARTITION BY employee_id is missing, so the running total is calculated over all rows ordered by sale_date.

💡 Hint

Think about how PARTITION BY affects window function grouping.

Without PARTITION BY, the window function treats all rows as one group, so the running total accumulates over all employees combined, not resetting per employee.