All problems

Is This Customer Up or Down This Cycle?

easySQLGroupByAggregation

Northline Bank's support line fields the same question all day: "has my balance gone up or down this month?" The support team wants a single summary table they can look at while the customer is still on the phone, showing each customer and the net movement across the whole cycle.

Signs are already handled in the data. Money coming in is stored as a positive amount and money going out as a negative one, so a withdrawal of 120 dollars is recorded as -120. That means the net movement needs no case-by-case reasoning about the type label — the arithmetic works out on its own.

transactions — one row per transaction. customer_id identifies the account holder. amount is in dollars, negative for money leaving. type is deposit, withdrawal or transfer.

id customer_id amount type txn_date
1 1 500 deposit 2023-07-01
2 1 -120 withdrawal 2023-07-03
3 2 1000 deposit 2023-07-02
4 2 -300 withdrawal 2023-07-05
5 3 250 deposit 2023-07-04
6 3 -600 withdrawal 2023-07-06
7 4 800 deposit 2023-07-07
8 4 -450 withdrawal 2023-07-08
9 5 2000 deposit 2023-07-09
10 5 -1500 withdrawal 2023-07-10
11 1 -200 transfer 2023-07-11
12 3 400 deposit 2023-07-12

The table already exists in the database — there is nothing to create or load.

Task: Write a query returning two columns, customer_id and net_change, with one row for each customer who has at least one transaction in the table. A customer with no transactions produces no row. Rows come back sorted by customer_id, smallest first.

Example output — shape only, on invented customers; somebody who took out more than they paid in comes back negative.

customer_id net_change
6 -420
7 1240
8 95

Sign in to solve this problem

Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.

Sign in

Discussion

Sign in to join the discussion — reading is open to everyone.

Loading comments…