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

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
  • String functions
  • Numeric functions

Exercise brief

Understand the request

Master-data product owner Survivorship planning needs a comparable completeness metric for each repeated customer key.

Survivorship planning needs a comparable completeness metric for each repeated customer key. Calculate completeness_pct across email, status, and phone for each duplicate customer group, ordered by completeness_pct ascending and customer_key.

Return

  • Return customer_key, duplicate_count, and completeness_pct rounded to two decimals.
  • Order by completeness_pct ascending, then customer_key.

Constraints

  • Count a field as complete only when it is non-NULL and not blank after trimming.
  • Use 3 × row count as the denominator.

Data you will use

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

quality_records

  • customer_keyTEXT
  • emailTEXT
  • statusTEXT
  • phoneTEXT

Hints, when you need them

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

Hint 1

The numerator counts complete cells, not complete rows.

Hint 2

There are three assessed fields for every row in a duplicate group.

Hint 3

Sum three completeness flags and divide by 3.0 * COUNT(*).

Verified SQL answer

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

Reveal solution and explanation
SELECT customer_key, COUNT(*) AS duplicate_count, ROUND(100.0 * (SUM(CASE WHEN email IS NOT NULL AND TRIM(email) <> '' THEN 1 ELSE 0 END) + SUM(CASE WHEN status IS NOT NULL AND TRIM(status) <> '' THEN 1 ELSE 0 END) + SUM(CASE WHEN phone IS NOT NULL AND TRIM(phone) <> '' THEN 1 ELSE 0 END)) / (3.0 * COUNT(*)), 2) AS completeness_pct FROM quality_records WHERE customer_key IS NOT NULL GROUP BY customer_key HAVING COUNT(*) > 1 ORDER BY completeness_pct, customer_key;

Why this works

Cell-level completeness distinguishes a group with one missing attribute from a fully populated group and uses a denominator aligned to the metric grain.

Success check

The incomplete C100 group ranks below the two fully complete groups.

Expected result

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

customer_keyduplicate_countcompleteness_pct
C100388.89
C2002100
C5002100

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.