Customer Data Cleanup After a Migration
SQL · Intermediate · about 80 minutes · 6 required tasks
The scenario
Your company has just migrated its customer data to a new system. Before anyone trusts it, you are asked to audit what came across, clean it up and make sure history was not lost. You will check for customers that went missing, find and remove duplicate records, filter out malformed numeric values, and finally recover each customer's current and previous address from a history table. Each task is a self-contained practice dataset; together they follow the order a real clean-up runs in.
What you will build
- A list of customers missing after the migration and a duplicate-email report
- De-duplicated customer and order records, and a set of valid numeric transaction amounts
- A current-versus-previous address view built from an address history table
Skills practiced
- Anti-joins
- Duplicate detection with GROUP BY and HAVING
- ROW_NUMBER() deduplication
- CAST and REGEXP validation
- LAG() over history tables
Stages
Audit the migration
Find customers that did not make it across and emails that appear more than once.
2 required tasks
Remove duplicates and bad values
Keep one row per real customer or order and filter out malformed amounts.
3 required tasks, 1 optional stretch task
Preserve address history
Recover each customer's current and previous address from a history table.
1 required task
Every task opens in the regular auto-graded challenge editor. Tasks you have already solved count automatically once you sign in.