Who Made Our Single Biggest Move?
Compliance at Northline Bank reviews the single biggest movement of money each week by hand, and the analyst has asked for it to be pulled out of the ledger automatically. "Biggest" means the largest amount of money that moved, and a withdrawal of 1500 moved more money than a deposit of 800 did.
That is the trap in this table. Amounts are stored signed — money in is positive, money out is negative — so read straight off the column the withdrawals look like the smallest numbers in the ledger. A ledger sorted naively puts the biggest withdrawal at the bottom rather than near the top, which is precisely the row compliance most wants to see.
transactions — one row per money movement. amount is signed: positive for money in, negative for money out. type is one of 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 exactly one row with two columns, customer_id and abs_amount — the customer behind the single largest movement of money, and the size of that movement as a positive number, with the sign discarded. Movements of every type are eligible. If two movements were tied at the top only one row would still come back, and which of them it is would be unspecified.
Example output — shape only, on an invented customer and movement.
| customer_id | abs_amount |
|---|---|
| 8 | 3450 |
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