Clean and Reconcile Bank Exports | Dataford Interview Questions

Given a raw export of transactional data with mismatched formatting, use formulas to clean, align, and reconcile the dataset against a bank settlement report at Rippling.

Write a PostgreSQL query using raw_transactions and bank_settlements. Normalize references, safely parse dates and amounts, match records, and classify reconciliation differences.

Output

One row per matched or unmatched record pair, with transaction ID, settlement ID, normalized reference, cleaned transaction date, cleaned amount, settlement date, settlement amount, and reconciliation status.
Include unmatched records from either source.
Order by transaction ID, followed by unmatched settlements in numeric settlement-ID order.

Schema

raw_transactions

Column	Type	Description
raw_transaction_idPK	INT	Identifier from the raw transaction export
raw_date	VARCHAR(20)	Transaction date in inconsistent text formats
raw_amount	VARCHAR(30)	Transaction amount containing currency symbols or inconsistent formatting
raw_reference	VARCHAR(40)	Raw transaction reference

bank_settlements

Column	Type	Description
settlement_idPK	VARCHAR(20)	Bank settlement identifier
settlement_date	DATE	Date reported by the bank
settlement_amount	NUMERIC(14,2)	Settled monetary amount
reference	VARCHAR(40)	Reference from the bank settlement report

Your question is Clean and Reconcile Bank Exports. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem