The Busiest Day
Capacity planning at Northline Bank wants the single busiest day in the ledger, so the branch can be staffed for its worst case rather than its average one. If several days are equally busy, the earliest of them is the one to report.
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 exactly one row with two columns, txn_date and txns, for the day carrying the most movements. txns is how many movements fell on that day. If two or more days carry the same number of movements, return the earliest of those days.
Example output — shape only, on an invented day. One row, never a list:
| txn_date | txns |
|---|---|
| 2019-03-08 | 9 |
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