Coding questions · Stripe
Reconcile Monthly Bank-Account Balances Across Two Datasets
Compare bank-account balances for a target calendar month, report match status, and distinguish missing comparison records from zero balances.
Write a PostgreSQL query that compares monthly bank-account balances from two imported datasets and marks each account in the first dataset as matching or not matching the second.
Tables
Both primary_balances and comparison_balances have columns:
bank_name text, account_name text, start_date date, end_date date, balance numeric(18,2)
report_month(month_start date) contains exactly one row whose value is the first day of the target month.
Exercise Conventions
- A record belongs to the calendar month containing its
end_date. - In each input table, there is at most one record per bank/account/end-date month. All fields are non-null, and
start_date <= end_date. - Report each primary record in the target month. Accounts appearing only in the comparison table are outside this report.
- Match records by bank name, account name, and target month, even if the two end dates within that month differ.
- A primary record is
Matchonly when a comparison record exists and the two numeric balances are equal. Otherwise it isNot Match. - Do not sum records across years or treat a missing comparison record as a zero balance.
These schema and missing-record conventions make the monthly reconciliation task precise.
Output
Return bank_name, account_name, end_date, balance, and match_status. The end date and balance come from the primary record. Sort by bank name and then account name using COLLATE "C" for deterministic text order.
Example
Target month: 2021-12-01.
primary_balances:
| bank_name | account_name | start_date | end_date | balance |
|---|---|---|---|---|
| Bay | 02 | 2021-12-01 | 2021-12-31 | 300.00 |
| Harbor | 01 | 2021-12-01 | 2021-12-31 | 150.00 |
| Harbor | 01 | 2020-12-01 | 2020-12-31 | 999.00 |
comparison_balances:
| bank_name | account_name | start_date | end_date | balance |
|---|---|---|---|---|
| Bay | 02 | 2021-12-01 | 2021-12-31 | 250.00 |
| Harbor | 01 | 2021-12-01 | 2021-12-30 | 150.00 |
Expected output:
| bank_name | account_name | end_date | balance | match_status |
|---|---|---|---|---|
| Bay | 02 | 2021-12-31 | 300.00 | Not Match |
| Harbor | 01 | 2021-12-31 | 150.00 | Match |
Monthly Bank Balance Reconciliation
Table primary_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 |
|---|---|---|---|---|
| 02 | 300 | Bay | 2021-12-31 | 2021-12-01 |
| 01 | 150 | Harbor | 2021-12-31 | 2021-12-01 |
| 01 | 999 | Harbor | 2020-12-31 | 2020-12-01 |
| 02 | 0 | Harbor | 2021-12-31 | 2021-12-01 |
| 02 | 75 | bay | 2021-12-01 | 2021-12-01 |
| 01 | 120.5 | Bay | 2021-12-31 | 2021-12-01 |
| 03 | 80 | Harbor | 2021-12-31 | 2021-12-01 |
| 10 | 45.25 | 1st Credit | 2021-12-31 | 2021-12-01 |
| 9 | 60 | 1st Credit | 2021-12-31 | 2021-12-01 |
| 01 | 500 | Summit | 2021-11-30 | 2021-11-01 |
| 01 | 20 | Cove | 2022-01-01 | 2022-01-01 |
Table comparison_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 |
|---|---|---|---|---|
| 02 | 250 | Bay | 2021-12-31 | 2021-12-01 |
| 01 | 150 | Harbor | 2021-12-30 | 2021-12-01 |
| 01 | 999 | Harbor | 2020-12-31 | 2020-12-01 |
| 02 | 75 | bay | 2021-12-31 | 2021-12-01 |
| 01 | 120.5 | Bay | 2021-11-30 | 2021-11-01 |
| 03 | 50 | Harbor | 2021-12-31 | 2021-12-01 |
| 03 | 30 | Harbor | 2020-12-31 | 2020-12-01 |
| 10 | 45.25 | 1st Credit | 2021-12-31 | 2021-12-01 |
| 01 | 500 | Summit | 2021-12-31 | 2021-12-01 |
| 01 | 20 | Cove | 2022-01-31 | 2022-01-01 |
Table report_month
| column | type | constraints |
|---|---|---|
| month_start | DATE | PRIMARY KEY |
Sample data:
| month_start |
|---|
| 2021-12-01 |
Expected output
| bank_name | account_name | end_date | balance | match_status |
|---|---|---|---|---|
| 1st Credit | 10 | 2021-12-31 | 45.25 | Match |
| 1st Credit | 9 | 2021-12-31 | 60 | Not Match |
| Bay | 01 | 2021-12-31 | 120.5 | Not Match |
| Bay | 02 | 2021-12-31 | 300 | Not Match |
| Harbor | 01 | 2021-12-31 | 150 | Match |
| Harbor | 02 | 2021-12-31 | 0 | Not Match |
| Harbor | 03 | 2021-12-31 | 80 | Not Match |
| bay | 02 | 2021-12-01 | 75 | Match |
Hints
- Use the single report_month row to decide which calendar month each end_date must fall in.
- Every target-month primary record must appear in the output, whether or not a comparison record exists for it.
- Two records describe the same account-month when bank_name, account_name and the month of end_date agree; the exact end dates may differ.
- A missing comparison record is not the same as a comparison balance of zero.