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.

Practice

1. What does the SQL expression SUM(amount) OVER (ORDER BY date) calculate?

easy

A. A running total of the amount column ordered by date

B. The total sum of amount for all rows without order

C. The average amount grouped by date

D. The maximum amount value in the table

Solution

1. Step 1: Understand SUM() with OVER()

   The SUM() function adds values, and OVER() defines how to apply it across rows.

2. Step 2: Analyze ORDER BY in OVER()

   ORDER BY date means the sum accumulates in date order, creating a running total.

3. Final Answer:

   A running total of the amount column ordered by date -> Option A

4. Quick Check:

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

Hint: SUM() with ORDER BY inside OVER() means running total [OK]

Common Mistakes:

• Thinking SUM() without OVER() gives running total
• Confusing running total with group total
• Ignoring ORDER BY inside OVER()

2. Which of the following is the correct syntax to calculate a running total of sales ordered by sale_date?

easy

A. SELECT sales, SUM(sales) OVER (PARTITION BY sale_date) FROM sales_table;

B. SELECT sales, SUM(sales) OVER (ORDER BY sale_date) FROM sales_table;

C. SELECT sales, SUM(sales) FROM sales_table ORDER BY sale_date;

D. SELECT sales, SUM(sales) OVER () ORDER BY sale_date FROM sales_table;

Solution

1. Step 1: Identify correct use of SUM() OVER()

   Running total requires SUM() OVER with ORDER BY clause inside OVER().

2. Step 2: Check each option's syntax

   SELECT sales, SUM(sales) OVER (ORDER BY sale_date) FROM sales_table; uses SUM(sales) OVER (ORDER BY sale_date), which is correct syntax for running total.

3. Final Answer:

   SELECT sales, SUM(sales) OVER (ORDER BY sale_date) FROM sales_table; -> Option B

4. Quick Check:

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

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

Common Mistakes:

• Using PARTITION BY instead of ORDER BY for running total
• Omitting OVER() clause
• Placing ORDER BY outside OVER()

3. Given the table sales_data with columns id, sale_date, and amount, what is the output of this query?

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

Assume the data:
id | sale_date | amount
1 | 2024-01-01 | 100
2 | 2024-01-03 | 200
3 | 2024-01-02 | 150

medium

A. Running totals: 100, 350, 450 ordered by id

B. Running totals: 100, 150, 350 ordered by id

C. Running totals: 100, 450, 250 ordered by id

D. Running totals: 100, 300, 450 ordered by id

Solution

1. Step 1: Order rows by sale_date for running total

   Order by sale_date: id=1(2024-01-01), id=3(2024-01-02), id=2(2024-01-03).

2. Step 2: Calculate running total in sale_date order

   Running totals: 100 (id=1), 100+150=250 (id=3), 250+200=450 (id=2).

3. Step 3: Output ordered by id

   Order by id: id=1 (100), id=2 (450), id=3 (250).

4. Final Answer:

   Running totals: 100, 450, 250 ordered by id -> Option C

5. Quick Check:

   Running total sums in sale_date order, output sorted by id [OK]

Hint: Running total sums by ORDER BY inside OVER, output order separate [OK]

Common Mistakes:

• Assuming running total follows output ORDER BY
• Adding amounts in id order instead of sale_date order
• Confusing running total with simple sum

4. Identify the error in this query intended to calculate a running total of points ordered by game_date:

SELECT player_id, points, SUM(points) OVER (PARTITION BY player_id) ORDER BY game_date FROM game_scores;

medium

A. ORDER BY clause is outside the OVER() function

B. PARTITION BY cannot be used with SUM()

C. Missing GROUP BY clause

D. SUM() cannot be used with window functions

Solution

1. Step 1: Check placement of ORDER BY

   ORDER BY for running total must be inside OVER(), not outside the function.

2. Step 2: Understand correct syntax

   Correct syntax: SUM(points) OVER (PARTITION BY player_id ORDER BY game_date).

3. Final Answer:

   ORDER BY clause is outside the OVER() function -> Option A

4. Quick Check:

   ORDER BY must be inside OVER() for running totals [OK]

Hint: Put ORDER BY inside OVER() for running totals [OK]

Common Mistakes:

• Placing ORDER BY outside OVER()
• Confusing PARTITION BY with GROUP BY
• Thinking SUM() can't be window function

5. You have a sales table with columns region, sale_date, and amount. You want to calculate a running total of sales per region ordered by sale_date. Which query correctly achieves this?

hard

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

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

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

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

Solution

1. Step 1: Understand requirement for running total per region

   We need to reset running total for each region, so PARTITION BY region is required.

2. Step 2: Check ORDER BY for running total

   ORDER BY sale_date inside OVER() ensures running total accumulates by date within each region.

3. Step 3: Evaluate options

   SELECT region, sale_date, amount, SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total FROM sales; correctly uses PARTITION BY region and ORDER BY sale_date inside SUM() OVER().

4. Final Answer:

   SELECT region, sale_date, amount, SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total FROM sales; -> Option D

5. Quick Check:

   PARTITION BY region + ORDER BY sale_date = running total per region [OK]

Hint: Use PARTITION BY for groups, ORDER BY for running total order [OK]

Common Mistakes:

• Omitting PARTITION BY to separate groups
• Using GROUP BY instead of window functions
• Swapping ORDER BY columns inside OVER()