Account Activation Dates
Onboarding at Northline Bank counts an account as activated on the day it first does something, not the day the paperwork cleared. The team wants each customer's activation date so it can measure how long the gap between opening and using an account really is.
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, name and first_txn, one row per customer with at least one movement, first_txn being the earliest txn_date on their ledger rows. Earliest activation first; two customers activating on the same day come back in alphabetical order of name.
Example output — shape only, on invented customers and dates:
| name | first_txn |
|---|---|
| Marisol Vega | 2019-03-04 |
| Oscar Delacroix | 2019-03-11 |
| Nadia Ferreira | 2019-03-19 |
| Tobias Renner | 2019-04-02 |
| Rune Halvorsen | 2019-04-15 |
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