The Monthly Volume Chart
The operations dashboard at Northline Bank plots how many movements the ledger handles each month, so staffing can be planned against real load. Dates are recorded down to the day, and the chart needs them collapsed to the month they fall in.
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 two columns, month and txns, one row per month in which at least one movement occurred. month must be formatted YYYY-MM, and txns is how many movements fell in that month. Return the rows in chronological order, earliest month first.
Example output — shape only, on invented months. Note the YYYY-MM text: a two-digit month, no day part:
| month | txns |
|---|---|
| 2019-03 | 41 |
| 2019-04 | 26 |
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