Running totals with SUM OVER in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate running total of sales by date

Given a table sales with columns sale_date and amount, which query correctly calculates the running total of amount ordered by sale_date?

SQL

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

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

B SELECT sale_date, amount, SUM(amount) FROM sales GROUP BY sale_date ORDER BY sale_date;

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

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

💡 Hint

Think about how to accumulate sums in order of dates.

Option C uses SUM() OVER (ORDER BY sale_date) which calculates a running total ordered by date. Option C partitions by date, so it sums amounts per date but does not accumulate. Option C groups and sums per date but does not produce running totals. Option C sums over all rows without order, so running total is the same for all rows.

📝 Syntax

intermediate

Identify the syntax error in running total query

Which option contains a syntax error when trying to calculate a running total of amount ordered by sale_date?

A SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales;

B SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) AS running_total FROM sales;

C SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales;

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

💡 Hint

Check the syntax of the frame clause after ORDER BY.

Option B is invalid because the frame clause syntax is incomplete. It should be ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Options A and B use correct frame syntax. Option B uses default frame which is valid.

❓ optimization

advanced

Optimize running total query for large datasets

You have a large sales table and want to calculate running totals by sale_date. Which query is most efficient?

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

B SELECT sale_date, amount, (SELECT SUM(amount) FROM sales s2 WHERE s2.sale_date <= s1.sale_date) AS running_total FROM sales s1 ORDER BY sale_date;

C SELECT sale_date, amount, SUM(amount) FROM sales GROUP BY sale_date ORDER BY sale_date;

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

💡 Hint

Consider how window functions perform compared to subqueries.

Option D uses a window function which is optimized for running totals. Option D uses a correlated subquery which is slow for large data. Option D groups sums but does not produce running totals. Option D partitions by date, which is unnecessary and incorrect for running totals.

🔧 Debug

advanced

Debug incorrect running total result

A query calculates running totals but the totals reset unexpectedly. Which option explains why?

SQL

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

A The PARTITION BY clause resets the running total for each sale_date, causing unexpected resets.

B The ORDER BY clause is missing, so running totals are unordered and reset.

C The SUM() function cannot be used with OVER clause for running totals.

D The query lacks a GROUP BY clause, so running totals are incorrect.

💡 Hint

Think about what PARTITION BY does in window functions.

PARTITION BY divides rows into groups and restarts the window function calculation for each group. Here, partitioning by sale_date causes running totals to reset for each date. To get a continuous running total, omit PARTITION BY.

🧠 Conceptual

expert

Understanding frame clauses in running totals

Which frame clause correctly defines a running total that includes all rows from the start up to the current row ordered by sale_date?

A ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

B ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

C RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

D ROWS BETWEEN UNBOUNDED FOLLOWING AND CURRENT ROW

💡 Hint

Consider how frame boundaries define the window of rows included.

Option A includes all rows from the start (unbounded preceding) up to the current row, which is the definition of a running total. Option A includes current row to the end, which is a running sum backwards. Option A includes all rows, so no running effect. Option A is invalid because unbounded following cannot come before current row.