Most Recent Passing Test Per Module | Dataford Interview Questions

Your question is Most Recent Passing Test Per Module. 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.

Problem

Apex Systems QA engineers review automated test execution history across product modules. Write a PostgreSQL query that reports the most recent passing test for every module, including modules that have no passing test.

Requirements

Return every module from modules, even when it has no test run or no passing test.
Consider only rows where test_runs.status = 'PASS'.
Select the latest passing run by executed_at. If multiple passing runs have the same timestamp, choose the row with the greatest test_run_id.
Return the module ID, module name, selected test run ID, and execution timestamp, ordered by module ID.

Schema

modules

Column	Type	Description
module_idPK	INTEGER	Unique identifier for a product module
module_name	VARCHAR(100)	Human-readable module name

test_runs

Column	Type	Description
test_run_idPK	INTEGER	Unique identifier for a test execution
module_id	INTEGER	Module tested by the run
status	VARCHAR(20)	Test outcome
executed_at	TIMESTAMP	Completion timestamp of the test run