Is This Customer Up or Down This Cycle?
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