You have a transactions table with columns trans_id, user_id, and amount. You want to calculate a running total of amount for each user_id ordered by trans_id, but you cannot use window functions. Which query correctly achieves this?
ASELECT t1.trans_id, t1.user_id, t1.amount, (SELECT SUM(t2.amount) FROM transactions t2 WHERE t2.user_id = t1.user_id AND t2.trans_id <= t1.trans_id) AS running_total FROM transactions t1 ORDER BY t1.user_id, t1.trans_id;
BSELECT trans_id, user_id, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY trans_id) AS running_total FROM transactions ORDER BY user_id, trans_id;
CSELECT trans_id, user_id, amount, SUM(amount) FROM transactions GROUP BY user_id, trans_id ORDER BY user_id, trans_id;
DSELECT t1.trans_id, t1.user_id, t1.amount, (SELECT SUM(amount) FROM transactions WHERE trans_id <= t1.trans_id) AS running_total FROM transactions t1 ORDER BY user_id, trans_id;
Step-by-Step Solution
Solution:
Step 1: Understand requirement for per-user running total
Running total must reset for each user_id and sum amounts up to current trans_id.
Step 2: Analyze options for correct filtering
SELECT t1.trans_id, t1.user_id, t1.amount, (SELECT SUM(t2.amount) FROM transactions t2 WHERE t2.user_id = t1.user_id AND t2.trans_id <= t1.trans_id) AS running_total FROM transactions t1 ORDER BY t1.user_id, t1.trans_id; uses a correlated subquery filtering by same user_id and trans_id less or equal current, correctly calculating running total per user.
Step 3: Identify errors in other options
SELECT trans_id, user_id, amount, SUM(amount) OVER (PARTITION BY user_id ORDER BY trans_id) AS running_total FROM transactions ORDER BY user_id, trans_id; uses window functions (not allowed). SELECT trans_id, user_id, amount, SUM(amount) FROM transactions GROUP BY user_id, trans_id ORDER BY user_id, trans_id; misuses GROUP BY without running total logic. SELECT t1.trans_id, t1.user_id, t1.amount, (SELECT SUM(amount) FROM transactions WHERE trans_id <= t1.trans_id) AS running_total FROM transactions t1 ORDER BY user_id, trans_id; sums amounts ignoring user_id, mixing all users.
Final Answer:
SELECT t1.trans_id, t1.user_id, t1.amount, (SELECT SUM(t2.amount) FROM transactions t2 WHERE t2.user_id = t1.user_id AND t2.trans_id <= t1.trans_id) AS running_total FROM transactions t1 ORDER BY t1.user_id, t1.trans_id; -> Option A
Quick Check:
Subquery filters by user and trans_id for running total [OK]
Quick Trick:Filter subquery by user_id and trans_id <= current [OK]
Common Mistakes:
Ignoring user_id in running total calculation
Using window functions despite restriction
Not ordering results properly
Master "Advanced Query Patterns" in SQL
9 interactive learning modes - each teaches the same concept differently
