SQL Longest Passing Streak | Dataford Interview Questions

Write a SQL query to identify the longest streak of passing test runs for each test case.

Runs are ordered chronologically by run_at, with run_id breaking timestamp ties. A run is passing only when status = 'PASS'; failed, skipped, and NULL statuses break a streak.

Output

One row per test case, including cases with no runs or no passing runs.
Columns: test_case_id, test_case_name, longest_passing_streak, streak_start_run_id, and streak_end_run_id.
Order by test_case_id. If streaks tie, report the earliest streak.

Schema

test_cases

Column	Type	Description
test_case_idPK	VARCHAR(20)	Unique test case identifier
test_case_name	VARCHAR(100)	Human-readable test case name

test_runs

Column	Type	Description
run_idPK	INT	Unique test run identifier
test_case_id	VARCHAR(20)	Test case executed by the run
run_at	TIMESTAMP	Timestamp when the run completed
status	VARCHAR(20)	Run outcome, such as PASS, FAIL, or SKIPPED

Your question is SQL Longest Passing Streak. 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