SQL Topic collection
Finding Duplicates & Data Quality SQL Topic exercises
Detect identity collisions, profile NULL-aware conflicts, and compare deterministic survivors with production-safe SQL.
- Exercises
- 12
- Difficulty
- Beginner to Advanced
What this collection tests
Skills and query patterns
Business-key uniqueness, canonicalization, completeness, NULL-aware conflicts, ROW_NUMBER survivorship, FIRST_VALUE comparisons, and referential integrity
Curated exercises
Choose an exercise
Work in the live editor where supported. Advanced guided labs state their engine boundary before you open the workspace.
- Exercise 1Easy
Find Repeated Customer Keys
Return each repeated customer_key and its duplicate_count, ordered by duplicate_count descending and customer_key ascending.
- Aggregation
- HAVING
- Filtering
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 2Easy
Detect Duplicate Composite Source IDs
Find repeated (source_system, external_id) combinations.
- Aggregation
- HAVING
- NULL handling
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 3Easy
Quarantine Missing Business Keys
Return records with at least one missing business-key field and count their missing_key_fields.
- CASE expressions
- String functions
- NULL handling
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 4Medium
Find Duplicates After Email Normalization
Group on LOWER(TRIM(email)) and return repeated normalized emails.
- Aggregation
- HAVING
- String functions
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 5Medium
Return Every Row in a Normalized Duplicate Group
Use a correlated EXISTS check to return every record with another record sharing its normalized email.
- Subqueries
- String functions
- Filtering
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 6Medium
Count Quality Defects Per Record
Count defects across customer_key, external_id, email, status, and phone for every affected record.
- CASE expressions
- String functions
- NULL handling
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 7Medium
Measure Completeness Within Duplicate Groups
Calculate completeness_pct across email, status, and phone for each duplicate customer group, ordered by completeness_pct ascending and customer_key.
- Aggregation
- HAVING
- CASE expressions
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 8Medium
Profile Null-Aware Duplicate Conflicts
Return duplicate customer groups whose region, status, or phone states disagree, counting NULL as one distinct phone state.
- Aggregation
- HAVING
- CASE expressions
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 9Hard
Select a Deterministic Survivor
Rank duplicate groups by updated_at, ingested_at, and record_id descending, then return each survivor ordered by customer_key.
- Window functions
- Subqueries
- Aggregation
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 10Hard
Compare Non-Survivors with the Approved Record
Use the approved precedence to return each non-survivor, its survivor_record_id, and NULL-safe region_changed, status_changed, and phone_changed flags.
- Window functions
- Subqueries
- Aggregation
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 11Medium
Find Orphan Account References Safely
Use NOT EXISTS to return records whose non-NULL account_id has no matching quality_accounts row.
- Joins
- Subqueries
- Filtering
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise → - Exercise 12Hard
Build a Data Quality Control Scorecard
Return a single-row scorecard with total_rows, missing_key_rows, normalized_duplicate_rows, conflicting_customer_groups, orphan_account_rows, and clean_row_pct.
- Subqueries
- Aggregation
- HAVING
SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided
Open exercise →
Attempt
Write a query or design from the brief before opening any hints.
Validate
Run it against the included dataset and inspect the output.
Explain
Compare the verified answer and explain each choice aloud.
Learn before practicing Finding Duplicates & Data Quality
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Open the interactive workspace and practice across SQL topics.