SQL Expected vs Actual Comparison | Dataford Interview Questions

The 6sense QA team validates platform behavior by storing expected API or UI results separately from results observed during automated test runs. Write a PostgreSQL query that compares the expected and actual result rows for every 6sense test case.

Requirements

Return every test case, including cases with no recorded result rows.
Report expected row count, actual row count, mismatched row count, missing expected rows, and unexpected actual rows.
Mark each case as PASS, FAIL, or NOT_RUN. Treat two NULL result values as equal.
Order the output by test_case_id ascending.

Schema

test_cases

Column	Type	Description
test_case_idPK	INT	Unique test case identifier
test_name	VARCHAR(150)	Name of the QA test case

expected_results

Column	Type	Description
test_case_id	INT	Referenced test case
result_keyPK	VARCHAR(80)	Assertion or response field name
expected_value	VARCHAR(255)	Value expected by the test

actual_results

Column	Type	Description
test_case_id	INT	Referenced test case
result_keyPK	VARCHAR(80)	Observed assertion or response field name
actual_value	VARCHAR(255)	Value observed during test execution

Your question is SQL Expected vs Actual Comparison. 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