Average Defects Over Time | Dataford Interview Questions

Apex Systems QA Hub needs a monthly quality metric showing the average number of defects found per tester, broken down by environment, release, and module. Write a PostgreSQL query that calculates this metric from test runs and linked defects.

Requirements

Group results by calendar month, environment, release, and module.
Count defects for each tester within every group, including test runs with zero defects.
Average those tester-level defect totals and return results in chronological order.

Schema

testers

Column	Type	Description
tester_idPK	INT	Unique tester identifier
tester_name	VARCHAR(100)	Tester display name

test_runs

Column	Type	Description
run_idPK	INT	Unique test run identifier
tester_id	INT	Tester who executed the run
environment	VARCHAR(40)	Execution environment
release_name	VARCHAR(30)	Application release under test
module	VARCHAR(60)	Application module tested
executed_at	TIMESTAMP	Timestamp when the run was executed

defects

Column	Type	Description
defect_idPK	INT	Unique defect identifier
run_id	INT	Test run that found the defect
severity	VARCHAR(20)	Defect severity
status	VARCHAR(20)	Current defect status

Your question is Average Defects Over Time. Start with the requirements and the three 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