Complex Joins for Reconciliation | Dataford Interview Questions

Describe a situation where you had to perform a complex JOIN to reconcile disparate data sources.

Use the provided transaction mapping and source-system records to produce a reconciliation report. Include matched records, amount discrepancies, and records missing from either source.

Output

One row per reconciled or unmatched transaction reference.
Columns: canonical_id, source_a_ref, source_b_ref, source_a_total, source_b_total, amount_difference, and reconciliation_status.
Include matched, mismatched, and source-missing records. Sort by canonical_id ascending with unmatched canonical IDs last, then by source references.

Schema

transaction_mapping

Column	Type	Description
canonical_idPK	INT	Shared identifier used to reconcile records
source_a_ref	VARCHAR(20)	Reference used by Source A
source_b_ref	VARCHAR(20)	Reference used by Source B

source_a_records

Column	Type	Description
record_idPK	INT	Source A record identifier
source_ref	VARCHAR(20)	Reference from Source A
record_date	DATE	Date recorded by Source A
amount	NUMERIC(12,2)	Amount reported by Source A

source_b_records

Column	Type	Description
record_idPK	INT	Source B record identifier
source_ref	VARCHAR(20)	Reference from Source B
record_date	DATE	Date recorded by Source B
amount	NUMERIC(12,2)	Amount reported by Source B

Your question is Complex Joins for Reconciliation. Start with the requirements and the three 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