Your question is SQL Latest Passing Run Before Each Failure. 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.
Apex Systems QA engineers review failed executions in the Apex QA test dashboard and need the most recent successful run for comparison. Write a PostgreSQL query that returns the latest passing run before every failure for the same test case and environment.
test_case_id and environment, treating two NULL environments as equal.finished_at before the failure, using run_id as a tie-breaker.failure_run_id.| Column | Type | Description |
|---|---|---|
| test_case_idPK | INTEGER | Unique test case identifier |
| test_name | VARCHAR(120) | Test name shown in the Apex QA dashboard |
| team_name | VARCHAR(80) | QA team responsible for the test |
| Column | Type | Description |
|---|---|---|
| run_idPK | INTEGER | Unique test execution identifier |
| test_case_id | INTEGER | References test_cases.test_case_id |
| status | VARCHAR(20) | Execution result |
| finished_at | TIMESTAMP | Time when the run finished |
| environment | VARCHAR(20) | Execution environment, which may be NULL |