Running total without window functions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate running total using self-join

Given a table sales with columns id and amount, which query correctly calculates the running total of amount ordered by id without using window functions?

SQL

SELECT a.id, a.amount, SUM(b.amount) AS running_total
FROM sales a
JOIN sales b ON b.id <= a.id
GROUP BY a.id, a.amount
ORDER BY a.id;

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

B SELECT id, amount, (SELECT SUM(amount) FROM sales WHERE id <= s.id) AS running_total FROM sales s ORDER BY id;

C SELECT a.id, a.amount, SUM(b.amount) AS running_total FROM sales a JOIN sales b ON b.id <= a.id GROUP BY a.id, a.amount ORDER BY a.id;

D SELECT id, amount, SUM(amount) FROM sales GROUP BY id ORDER BY id;

💡 Hint

Think about joining the table to itself to accumulate sums up to the current row.

Option C uses a self-join to sum all amounts with id less than or equal to the current row's id, correctly calculating the running total without window functions. Option C uses window functions, which is not allowed. Option C is a correlated subquery which works but is not the option asked for here. Option C sums amounts per id without running total logic.

❓ query_result

intermediate

Running total with correlated subquery

Which query returns the running total of amount from the transactions table ordered by transaction_id without using window functions?

SQL

SELECT t1.transaction_id, t1.amount, (SELECT SUM(t2.amount) FROM transactions t2 WHERE t2.transaction_id <= t1.transaction_id) AS running_total FROM transactions t1 ORDER BY t1.transaction_id;

A SELECT t1.transaction_id, t1.amount, (SELECT SUM(t2.amount) FROM transactions t2 WHERE t2.transaction_id <= t1.transaction_id) AS running_total FROM transactions t1 ORDER BY t1.transaction_id;

B SELECT transaction_id, amount, SUM(amount) OVER (ORDER BY transaction_id) AS running_total FROM transactions ORDER BY transaction_id;

C SELECT t1.transaction_id, t1.amount, SUM(t2.amount) FROM transactions t1 JOIN transactions t2 ON t2.transaction_id >= t1.transaction_id GROUP BY t1.transaction_id, t1.amount ORDER BY t1.transaction_id;

D SELECT transaction_id, amount, SUM(amount) FROM transactions GROUP BY transaction_id ORDER BY transaction_id;

💡 Hint

Use a subquery that sums amounts up to the current transaction id.

Option A uses a correlated subquery to sum amounts for all transactions with id less than or equal to the current row, correctly calculating the running total without window functions. Option A uses window functions which are not allowed. Option A groups by transaction_id but does not calculate running totals. Option A reverses the join condition causing incorrect sums.

📝 Syntax

advanced

Identify syntax error in running total query

Which option contains a syntax error when trying to calculate running total without window functions?

A SELECT a.id, a.amount, SUM(b.amount) AS running_total FROM sales a JOIN sales b ON b.id <= a.id GROUP BY a.id ORDER BY a.id;

B SELECT id, amount, (SELECT SUM(amount) FROM sales WHERE id <= s.id) AS running_total FROM sales s ORDER BY id;

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

D SELECT a.id, a.amount, SUM(b.amount) AS running_total FROM sales a JOIN sales b ON b.id <= a.id GROUP BY a.id, a.amount ORDER BY a.id;

💡 Hint

Check if all non-aggregated columns in SELECT are in GROUP BY.

Option A causes a syntax error because it selects a.amount but does not include it in the GROUP BY clause, which is required in standard SQL. Options A, C, and D are syntactically valid.

❓ optimization

advanced

Optimize running total query without window functions

Which query is the most efficient way to calculate running total on orders table without using window functions?

A SELECT order_id, amount, SUM(amount) FROM orders GROUP BY order_id ORDER BY order_id;

B SELECT order_id, amount, SUM(amount) OVER (ORDER BY order_id) AS running_total FROM orders ORDER BY order_id;

C SELECT o1.order_id, o1.amount, (SELECT SUM(o2.amount) FROM orders o2 WHERE o2.order_id <= o1.order_id) AS running_total FROM orders o1 ORDER BY o1.order_id;

D SELECT o1.order_id, o1.amount, SUM(o2.amount) AS running_total FROM orders o1 JOIN orders o2 ON o2.order_id <= o1.order_id GROUP BY o1.order_id, o1.amount ORDER BY o1.order_id;

💡 Hint

Consider which approach reduces repeated subqueries.

Option D uses a self-join with aggregation which is generally more efficient than correlated subqueries (Option D) because it avoids repeated subquery execution per row. Option D uses window functions which are disallowed. Option D does not calculate running totals.

🧠 Conceptual

expert

Why avoid window functions for running totals in some cases?

Which reason best explains why someone might calculate running totals without window functions?

A Because window functions are always slower than self-joins or subqueries.

B Because some database systems do not support window functions or have limited support.

C Because running totals cannot be calculated with window functions.

D Because window functions require complex syntax that is impossible to learn.

💡 Hint

Think about compatibility and database features.

Option B is correct because some older or simpler database systems do not support window functions, so alternatives like self-joins or correlated subqueries are used. Option B is false; window functions are often more efficient. Option B is false; window functions are designed for running totals. Option B is an exaggeration and incorrect.