Median Defect Resolution Time

HardSQL · PostgreSQL

Problem

Apex Systems wants to monitor how quickly defects are resolved across its QA teams. Write a PostgreSQL query that calculates resolution-time statistics for defects opened during January 2024.

Requirements

Derive each defect's first transition to Resolved from defect_status_history.
Calculate the number of resolved defects, average resolution time, median resolution time, and 90th percentile resolution time in hours.
Group results by QA team and defect severity.
Include comma-separated project and owner names, preserving defects with no assigned owner.
Exclude unresolved defects, invalid negative durations, and defects opened outside January 2024. Sort by QA team and severity.

Schema

projects

Column	Type	Description
project_idPK	INT	Unique project identifier
project_name	VARCHAR(100)	Apex Systems project name
qa_team	VARCHAR(100)	QA team responsible for the project

engineers

Column	Type	Description
engineer_idPK	INT	Unique QA engineer identifier
engineer_name	VARCHAR(100)	QA engineer's name
role	VARCHAR(50)	QA engineering role

defects

Column	Type	Description
defect_idPK	INT	Unique defect identifier
project_id	INT	References projects.project_id
assigned_engineer_id	INT	References engineers.engineer_id
severity	VARCHAR(20)	Defect severity classification
opened_at	TIMESTAMP	Timestamp when the defect was opened
current_status	VARCHAR(30)	Current lifecycle status

defect_status_history

Column	Type	Description
history_idPK	INT	Unique status history identifier
defect_id	INT	References defects.defect_id
status	VARCHAR(30)	Status recorded in the history event
changed_at	TIMESTAMP	Timestamp when the status changed

Tablesprojectsengineersdefectsdefect_status_history