Your question is First and Last Result per Case. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
test_case_id, test_case_name, environment, first_result, and last_result.test_case_id, then environment.| 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 |
| 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 |