Compare Two Tables in SQL | Dataford Interview Questions

Persistent Systems' QA validation pipeline stores expected test results in one table and processed results in another. Write a PostgreSQL query to identify discrepancies between the two datasets.

Requirements

Compare records by test_case_id using a join that preserves unmatched records from both tables.
Classify each discrepancy as MISSING_SOURCE, MISSING_TARGET, or VALUE_MISMATCH.
Compare both status and result_value, treating NULL and a non-NULL value as different.
Return only discrepancies, ordered by test_case_id.

Schema

source_test_data

Column	Type	Description
test_case_idPK	INT	Unique expected test case identifier
status	VARCHAR(20)	Expected execution status
result_value	INT	Expected numeric result

target_test_data

Column	Type	Description
test_case_idPK	INT	Unique processed test case identifier
status	VARCHAR(20)	Actual execution status
result_value	INT	Actual numeric result

Your question is Compare Two Tables in SQL. 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