Finding Duplicates & Data Quality SQL Topic exerciseHardVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

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
  • CASE expressions
  • String functions

Exercise brief

Understand the request

Data platform reliability lead A release gate needs one auditable summary of identity completeness, normalized duplication, conflicts, referential integrity, and clean-row coverage.

A release gate needs one auditable summary of identity completeness, normalized duplication, conflicts, referential integrity, and clean-row coverage. Return a single-row scorecard with total_rows, missing_key_rows, normalized_duplicate_rows, conflicting_customer_groups, orphan_account_rows, and clean_row_pct.

Return

  • Return total_rows, missing_key_rows, normalized_duplicate_rows, conflicting_customer_groups, orphan_account_rows, and clean_row_pct.
  • Round clean_row_pct to two decimals.

Constraints

  • A clean row has complete customer_key, external_id, email, and status; is not in a normalized duplicate-email group; and has a valid non-NULL account.
  • Count conflicting customer groups, not rows.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

quality_records

  • record_idINTEGER
  • customer_keyTEXT
  • external_idTEXT
  • emailTEXT
  • regionTEXT
  • statusTEXT
  • account_idINTEGER

quality_accounts

  • account_idINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Define duplicate-email and conflicting-customer populations once in CTEs.

Hint 2

Create row-level flags for missing keys, duplicate membership, orphan references, and clean eligibility.

Hint 3

Aggregate the flags and count the conflict-group CTE in the final single-row SELECT.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH duplicate_emails AS (SELECT LOWER(TRIM(email)) AS normalized_email FROM quality_records WHERE email IS NOT NULL AND TRIM(email) <> '' GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1), conflicting_customers AS (SELECT customer_key FROM quality_records WHERE customer_key IS NOT NULL GROUP BY customer_key HAVING COUNT(*) > 1 AND (COUNT(DISTINCT region) > 1 OR COUNT(DISTINCT status) > 1)), flags AS (SELECT q.record_id, CASE WHEN q.customer_key IS NULL OR q.external_id IS NULL OR q.email IS NULL OR TRIM(q.email) = '' THEN 1 ELSE 0 END AS missing_key, CASE WHEN q.email IS NOT NULL AND TRIM(q.email) <> '' AND LOWER(TRIM(q.email)) IN (SELECT normalized_email FROM duplicate_emails) THEN 1 ELSE 0 END AS duplicate_email, CASE WHEN q.account_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM quality_accounts a WHERE a.account_id = q.account_id) THEN 1 ELSE 0 END AS orphan_account, CASE WHEN q.customer_key IS NOT NULL AND q.external_id IS NOT NULL AND q.email IS NOT NULL AND TRIM(q.email) <> '' AND q.status IS NOT NULL AND TRIM(q.status) <> '' AND LOWER(TRIM(q.email)) NOT IN (SELECT normalized_email FROM duplicate_emails) AND q.account_id IS NOT NULL AND EXISTS (SELECT 1 FROM quality_accounts a WHERE a.account_id = q.account_id) THEN 1 ELSE 0 END AS clean_row FROM quality_records q) SELECT COUNT(*) AS total_rows, SUM(missing_key) AS missing_key_rows, SUM(duplicate_email) AS normalized_duplicate_rows, (SELECT COUNT(*) FROM conflicting_customers) AS conflicting_customer_groups, SUM(orphan_account) AS orphan_account_rows, ROUND(100.0 * SUM(clean_row) / COUNT(*), 2) AS clean_row_pct FROM flags;

Why this works

The scorecard keeps each metric at its declared grain: most are row counts, while conflicts are customer-group counts. Its clean-row rule is explicit enough to become a release assertion.

Success check

All five rule populations and the one clean row are reconciled in a deterministic single-row result.

Expected result

Use this output to verify values, aliases, ordering, and row count.

total_rowsmissing_key_rowsnormalized_duplicate_rowsconflicting_customer_groupsorphan_account_rowsclean_row_pct
1227218.33

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.