Compare Defects After Data Correction | Dataford Interview Questions

Write a SQL query to compare defect counts before and after a data correction.

Assume each correction has defect snapshots classified as before or after. Return results for correction 101, including defect types with no observations in either stage.

Output

One row per defect type
Columns: defect_type, before_count, after_count, and count_change
Include zero counts where no defects exist
Order by defect_type_id ascending

Schema

data_corrections

Column	Type	Description
correction_idPK	INT	Unique correction identifier
correction_name	VARCHAR(100)	Correction label
corrected_at	TIMESTAMP	Time the correction was applied

defect_types

Column	Type	Description
defect_type_idPK	INT	Unique defect type identifier
type_name	VARCHAR(80)	Defect type name

defect_snapshots

Column	Type	Description
snapshot_idPK	INT	Unique snapshot row identifier
correction_id	INT	Correction associated with the snapshot
defect_id	INT	Identifier of the observed defect
defect_type_id	INT	Defect classification
stage	VARCHAR(20)	Observation stage, before or after

Your question is Compare Defects After Data Correction. 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