SQL Join With Matching and Non-Matching | Dataford Interview Questions

OneTrust QA engineers need to reconcile test cases defined for the OneTrust Privacy Management platform with their recorded test executions. Write a PostgreSQL query that returns matching and non-matching records from both sources.

Requirements

Use a FULL OUTER JOIN on test_case_id so unmatched rows from either table are retained.
Return the shared test case ID, test case name, execution ID, and execution status.
Add a match_status column containing Matched, Missing Execution, or Unregistered Execution using CASE.
Sort by the combined test case ID ascending, placing a null combined ID last.

Schema

qa_test_cases

Column	Type	Description
test_case_idPK	INT	Unique test case identifier
test_case_name	VARCHAR(150)	Name of the OneTrust QA test case
priority	VARCHAR(20)	Test case priority

qa_test_executions

Column	Type	Description
execution_idPK	INT	Unique execution identifier
test_case_id	INT	Referenced test case identifier
execution_status	VARCHAR(20)	Execution result status
executed_at	TIMESTAMP	Time the test was executed

Your question is SQL Join With Matching and Non-Matching. 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