SQL: Compare Test Runs by Date | Dataford Interview Questions

Write a SQL query to compare current and previous test run results by date.

Return comparisons for every run that has a previous run. Include tests present in either run, including tests missing from one side. Treat a test with no previous result as newly observed and a test with a NULL current status as unknown.

Output

One row per test case and current run, with current_run_date, previous_run_date, test_case_id, test_name, previous_status, current_status, previous_duration_ms, current_duration_ms, duration_delta_ms, and status_change.
Order by current_run_date, then test_case_id.

Schema

test_runs

Column	Type	Description
run_idPK	INT	Unique test run identifier
run_date	DATE	Date on which the run completed
environment	VARCHAR(20)	Execution environment

test_results

Column	Type	Description
run_idPK	INT	Reference to the test run
test_case_idPK	INT	Reference to the test case
status	VARCHAR(20)	Observed test status
duration_ms	INT	Execution duration in milliseconds

test_cases

Column	Type	Description
test_case_idPK	INT	Unique test case identifier
test_name	VARCHAR(100)	Human-readable test name
suite_name	VARCHAR(50)	Test suite containing the case

Your question is SQL: Compare Test Runs by Date. 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