Coding questions · Stripe
Find the Latest Balance for a Bank Account
Query the latest dated bank-account balance using both bank and account identifiers while handling records across months and years.
Write a PostgreSQL query that returns the latest reported balance for one bank account.
Tables
bank_balances(bank_name text, account_name text, start_date date, end_date date, balance numeric(18,2))
requested_account(bank_name text, account_name text) contains exactly one row.
Rules
- Match both bank name and account name exactly. Account names are text identifiers and may contain leading zeros.
- For this exercise, latest means greatest
end_date; each bank/account pair has at most one row with a given end date. - Every field is non-null, and
start_date <= end_date. - Records may span different months and years. Dates are typed dates, not strings to compare lexicographically.
Output
Return one column named balance for the latest matching record. If the requested account has no record, return no rows. The end-date definition and missing-account behavior are explicit exercise conventions.
Example
bank_balances:
| bank_name | account_name | start_date | end_date | balance |
|---|---|---|---|---|
| Harbor | 001234 | 2021-12-01 | 2021-12-31 | 150.00 |
| Harbor | 001234 | 2019-05-01 | 2019-05-31 | 200.00 |
| Bay | 001234 | 2022-01-01 | 2022-01-31 | 300.00 |
requested_account: ('Harbor', '001234')
Expected output:
| balance |
|---|
| 150.00 |
Latest Reported Balance for a Bank Account
Table bank_balances
| column | type | constraints |
|---|---|---|
| bank_name | TEXT | NOT NULL |
| account_name | TEXT | NOT NULL |
| start_date | DATE | NOT NULL |
| end_date | DATE | NOT NULL |
| balance | NUMERIC(18,2) | NOT NULL |
Sample data:
| account_name | balance | bank_name | end_date | start_date |
|---|---|---|---|---|
| 001234 | 200 | Harbor | 2019-05-31 | 2019-05-01 |
| 001234 | 175.25 | Harbor | 2021-02-28 | 2021-02-01 |
| 001234 | 150 | Harbor | 2021-12-31 | 2021-12-01 |
| 001234 | 1180.4 | Harbor | 2022-01-31 | 2021-11-15 |
| 001234 | 300 | Bay | 2022-03-31 | 2022-03-01 |
| 1234 | 999 | Harbor | 2022-06-30 | 2022-06-01 |
| 001235 | 50 | Harbor | 2023-01-31 | 2023-01-01 |
| 004321 | 2750.6 | Bay | 2020-07-31 | 2020-07-01 |
Table requested_account
| column | type | constraints |
|---|---|---|
| bank_name | TEXT | NOT NULL |
| account_name | TEXT | NOT NULL |
Sample data:
| account_name | bank_name |
|---|---|
| 001234 | Harbor |
Expected output
| balance |
|---|
| 1180.4 |
Hints
- The requested account is identified by both bank_name and account_name; the same account_name can appear at another bank.
- Treat account_name as text: '001234' and '1234' are different accounts.
- Latest is defined by end_date, which is a date column, not by start_date.