You have a table payments with columns payment_id, customer_id, and amount. How can you calculate a running total of amount for each customer_id without using window functions?
hard📝 Application Q8 of 15
SQL - Advanced Query Patterns
You have a table payments with columns payment_id, customer_id, and amount. How can you calculate a running total of amount for each customer_id without using window functions?
ASELECT payment_id, customer_id, amount, SUM(amount) FROM payments GROUP BY customer_id ORDER BY payment_id;
BSELECT payment_id, customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY payment_id) AS running_total FROM payments;
CSELECT p1.payment_id, p1.customer_id, p1.amount, (SELECT SUM(p2.amount) FROM payments p2 WHERE p2.customer_id = p1.customer_id AND p2.payment_id <= p1.payment_id) AS running_total FROM payments p1 ORDER BY p1.customer_id, p1.payment_id;
DSELECT payment_id, customer_id, amount, (SELECT SUM(amount) FROM payments WHERE payment_id >= p1.payment_id) AS running_total FROM payments p1 ORDER BY customer_id, payment_id;
Step-by-Step Solution
Solution:
Step 1: Use correlated subquery with partition
The subquery sums amounts for the same customer where payment_id is less than or equal to the current row's payment_id.
Step 2: Order by customer and payment_id
Ordering ensures running totals are computed per customer in payment order.
Final Answer:
SELECT p1.payment_id, p1.customer_id, p1.amount, (SELECT SUM(p2.amount) FROM payments p2 WHERE p2.customer_id = p1.customer_id AND p2.payment_id <= p1.payment_id) AS running_total FROM payments p1 ORDER BY p1.customer_id, p1.payment_id; correctly calculates running totals per customer without window functions.
Quick Check:
Partition by customer_id and cumulative sum with correlated subquery [OK]
Quick Trick:Correlated subquery with customer_id and payment_id filters [OK]
Common Mistakes:
Using window functions when disallowed
Not filtering by customer_id in subquery
Using >= instead of <= in subquery
Missing ORDER BY for correct sequence
Master "Advanced Query Patterns" in SQL
9 interactive learning modes - each teaches the same concept differently
