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

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
  • Filtering
  • Sorting

Exercise brief

Understand the request

Data quality operations lead A quarantine queue prioritizes records by the number of missing or blank critical attributes.

A quarantine queue prioritizes records by the number of missing or blank critical attributes. Count defects across customer_key, external_id, email, status, and phone for every affected record.

Return

  • Return record_id and quality_issue_count.
  • Order by quality_issue_count descending, then record_id.

Constraints

  • Treat NULL as missing for every field and trimmed-empty text as missing for email or status.
  • Return only rows with at least one issue.

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
  • statusTEXT
  • phoneTEXT

Hints, when you need them

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

Hint 1

Represent each rule as a 1-or-0 CASE flag.

Hint 2

Blank-aware text rules need both IS NULL and a trimmed-empty comparison.

Hint 3

Add the five flags and filter with their underlying predicates.

Verified SQL answer

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

Reveal solution and explanation
SELECT record_id, CASE WHEN customer_key IS NULL THEN 1 ELSE 0 END + CASE WHEN external_id IS NULL THEN 1 ELSE 0 END + CASE WHEN email IS NULL OR TRIM(email) = '' THEN 1 ELSE 0 END + CASE WHEN status IS NULL OR TRIM(status) = '' THEN 1 ELSE 0 END + CASE WHEN phone IS NULL THEN 1 ELSE 0 END AS quality_issue_count FROM quality_records WHERE customer_key IS NULL OR external_id IS NULL OR email IS NULL OR TRIM(email) = '' OR status IS NULL OR TRIM(status) = '' OR phone IS NULL ORDER BY quality_issue_count DESC, record_id;

Why this works

Row-level rule flags make a quality score explainable: each defect contributes exactly one unit and can later be expanded into named rule columns.

Success check

The five-field blank record ranks first and all partially incomplete records are retained.

Expected result

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

record_idquality_issue_count
10075
10062
10122
10021

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.