The Big Movements
Compliance at Northline Bank eyeballs the biggest money movements every week, and "biggest" means the most money moved, not the highest number in the column. A 1500-dollar withdrawal is a larger event than a 1000-dollar deposit, and the ledger's signs make it look like the opposite.
transactions — one row per movement of money. customer_id points at a row in customers; type is one of deposit, withdrawal or transfer; txn_date is the day it settled. The ledger is signed: money arriving in an account is stored as a positive amount, and money leaving it as a negative one, so a 600-dollar withdrawal is recorded as -600.
| 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 that returns three columns — id, type and amount — for the three largest movements by amount of money moved, ignoring direction. Largest movement first; two movements of the same size come back with the smaller id first. Exactly three rows must come back.
Example output — shape only, on invented movements. The amount column still carries the stored sign even though the sequence is by size, which is why a negative figure can sit above a positive one:
| id | type | amount |
|---|---|---|
| 47 | withdrawal | -9400 |
| 33 | deposit | 7100 |
| 41 | transfer | -6800 |
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