Deposit Volume by City
Regional managers at Northline Bank are measured on the deposits their city brings in, so the league table has to pool funding by branch city. The ledger knows a customer number and nothing about geography; the city lives on the customer.
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 |
customers — one row per account holder at Northline. account_type is either checking or savings; city is the branch city the account belongs to.
| id | name | account_type | city |
|---|---|---|---|
| 1 | Farah Idris | checking | Chicago |
| 2 | Grant Boyle | savings | Miami |
| 3 | Hana Suzuki | checking | Chicago |
| 4 | Ibrahim Njoku | savings | Miami |
| 5 | Jade Wu | checking | Seattle |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query that returns two columns, city and deposits, one row per city with at least one deposit, deposits being the total deposit amount from that city's customers, rounded to 2 decimal places. Largest total first; two cities on the same total come back in alphabetical order of city.
Example output — shape only, on invented cities and totals:
| city | deposits |
|---|---|
| Denver | 12400 |
| Portland | 8300 |
| Tulsa | 2750 |
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