Defect Counts Before vs After Migration | Dataford Interview Questions

Write a SQL query to compare defect counts before and after a database migration.

Use the provided migrations and defects tables. Treat defects detected before the migration date as before-migration defects, and defects detected on or after that date as after-migration defects.

Output

One row per migration, including migrations with no matching defects
Columns: migration_id, system_name, migration_date, defects_before, defects_after, and count_change
Sort by migration_date ascending, then migration_id ascending

Schema

migrations

Column	Type	Description
migration_idPK	INT	Unique migration identifier
system_name	VARCHAR(100)	System affected by the migration
migration_date	DATE	Date the migration occurred

defects

Column	Type	Description
defect_idPK	INT	Unique defect identifier
system_name	VARCHAR(100)	System where the defect was detected
detected_at	DATE	Date the defect was detected
status	VARCHAR(30)	Current defect status

Your question is Defect Counts Before vs After Migration. 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.

Problem