SQL Repeated Failures Across Runs | Dataford Interview Questions

Write a SQL query to detect repeated failures for the same test case across multiple runs.

Assume a failure is a test run whose status is failed, and a repeated failure requires at least two failed runs for the same test case.

Output

Return one row per test case with repeated failures.
Include test_case_id, test_case_name, failed_run_count, first_failed_at, and last_failed_at.
Order by failed_run_count descending, then test_case_id ascending.

test_cases

Column	Type	Description
test_case_idPK	INT	Unique identifier for a test case
test_case_name	VARCHAR(150)	Human-readable test case name

test_runs

Column	Type	Description
execution_idPK	INT	Unique identifier for a test execution
test_case_id	INT	Test case executed by the run
status	VARCHAR(20)	Execution status such as passed, failed, or skipped
run_at	TIMESTAMP	Timestamp when the test execution occurred

Your question is SQL Repeated Failures Across Runs. 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.