Discrepancy Query Between Sources | Dataford Interview Questions

Write a SQL query to identify discrepancies between two data sources.

Compare records using record_id. Identify records missing from either source, records duplicated within a source, and records whose aggregated amount or status differs. Treat NULL statuses as distinct from non-NULL statuses.

Output

One row per discrepant record_id
Columns: record_id, discrepancy_type, source_a_count, source_b_count, source_a_amount, source_b_amount, source_a_status, source_b_status
Include only discrepant records and order by record_id ascending.

Schema

source_a_records

Column	Type	Description
source_row_idPK	INT	Unique row identifier in source A
record_id	INT	Business record identifier
amount	NUMERIC(12,2)	Amount reported by source A
status	VARCHAR(30)	Record status reported by source A

source_b_records

Column	Type	Description
source_row_idPK	INT	Unique row identifier in source B
record_id	INT	Business record identifier
amount	NUMERIC(12,2)	Amount reported by source B
status	VARCHAR(30)	Record status reported by source B

Your question is Discrepancy Query Between Sources. 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