First and Last Result per Case | Dataford Interview Questions

Write a SQL query to find the first and last test result for each test case and environment.

Use the test_cases and test_results tables. The first result is determined by the earliest executed_at; if timestamps tie, use the smaller result_id first.

Output

One row per test case and environment with at least one result.
Columns: test_case_id, test_case_name, environment, first_result, and last_result.
Sort by test_case_id, then environment.

Schema

test_cases

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

test_results

Column	Type	Description
result_idPK	INT	Unique identifier for the test result
test_case_id	INT	Referenced test case
environment	VARCHAR(50)	Environment where the test ran
result_status	VARCHAR(30)	Outcome recorded for the test execution
executed_at	TIMESTAMP	Time when the test execution completed

Your question is First and Last Result per Case. 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