Reconcile Monthly Bank-Account Balances Across Two Datasets

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 `Match` only when a comparison record exists and the two numeric balances are equal. Otherwise it is `Not 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 |