All problems

Who Made Our Single Biggest Move?

hardSQLSorting

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

Discussion

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

Loading comments…