SQL Defects Per Test Run | Dataford Interview Questions

Write an SQL query to join a test results table with a defects table and show the number of defects per test run.

Use the provided test_results and defects tables. Include test runs that have no matching defects.

Output

One row per test run, with columns test_run_id and defect_count.
Include each test run represented in test_results, including zero-defect runs.
Order by test_run_id ascending.

test_results

Column	Type	Description
result_idPK	INT	Unique identifier for a test result
test_run_id	INT	Identifier of the test run
test_name	VARCHAR(100)	Name of the executed test
status	VARCHAR(20)	Outcome of the test result

defects

Column	Type	Description
defect_idPK	INT	Unique identifier for a defect
test_result_id	INT	Identifier of the test result associated with the defect
defect_code	VARCHAR(30)	Reference code for the defect
severity	VARCHAR(20)	Defect severity classification

Your question is SQL Defects Per Test Run. Start with the requirements and the two 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.