Lookup-Based Data Reconciliation | Dataford Interview Questions

How do you utilize XLOOKUP and VLOOKUP for large-scale financial or operational data reconciliation?

Translate the lookup-based reconciliation approach into PostgreSQL. Compare corresponding records from two financial sources and classify matches, discrepancies, and records missing from either source.

Output

One row per transaction key found in either source.
Include the transaction key, both source dates, both amounts, amount difference, and reconciliation status.
Include matches and unmatched records, ordered by transaction key ascending.

Schema

erp_transactions

Column	Type	Description
transaction_idPK	VARCHAR(20)	Transaction identifier from the ERP source
transaction_date	DATE	Date recorded by the ERP source
amount	NUMERIC(12,2)	Transaction amount recorded by the ERP source
currency	VARCHAR(3)	ISO currency code

finance_transactions

Column	Type	Description
transaction_idPK	VARCHAR(20)	Transaction identifier from the finance source
posted_date	DATE	Date posted by the finance source
amount	NUMERIC(12,2)	Transaction amount recorded by the finance source
currency	VARCHAR(3)	ISO currency code

Your question is Lookup-Based Data Reconciliation. 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