SQL Most Recent Defect Per Test | Dataford Interview Questions

Write an SQL query to identify the most recent defect for each test case.

Return all test cases, including those without a linked defect. The most recent defect is determined by the latest reported_at timestamp; if timestamps tie, choose the defect with the greatest defect_id.

Output

One row per test case, ordered by test_case_id.
Columns: test_case_id, test_case_name, defect_id, defect_title, reported_at, and severity.
Test cases without defects must have NULL values for defect columns.

Schema

test_cases

Column	Type	Description
test_case_idPK	INT	Unique test case identifier
test_case_name	VARCHAR(150)	Test case name
test_suite	VARCHAR(100)	Test suite containing the case

defects

Column	Type	Description
defect_idPK	INT	Unique defect identifier
defect_title	VARCHAR(200)	Short defect description
reported_at	TIMESTAMP	Timestamp when the defect was reported
severity	VARCHAR(20)	Defect severity

test_case_defects

Column	Type	Description
test_case_idPK	INT	Referenced test case
defect_idPK	INT	Referenced defect
link_status	VARCHAR(20)	Status of the test case to defect relationship

Your question is SQL Most Recent Defect Per Test. 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