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
- Filtering
- Sorting
Exercise brief
Understand the request
Master-data governance lead Not every duplicate is an identical retry; a missing phone in one version and a populated phone in another is also a governed disagreement.
Not every duplicate is an identical retry; a missing phone in one version and a populated phone in another is also a governed disagreement. Return duplicate customer groups whose region, status, or phone states disagree, counting NULL as one distinct phone state.
Return
- Return customer_key, row_count, region_versions, status_versions, and phone_versions_including_null.
- Order by customer_key.
Constraints
- Use COUNT(DISTINCT ...) for non-NULL values.
- Add one phone version when COUNT(phone) is less than COUNT(*).
- Require duplicate row count and at least one conflicting attribute.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
quality_records
customer_keyTEXTregionTEXTstatusTEXTphoneTEXT
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
COUNT(DISTINCT phone) ignores NULL, so it cannot see a missing-versus-populated disagreement by itself.
Hint 2
COUNT(phone) < COUNT(*) proves that at least one phone is NULL; add one state to the non-NULL distinct count.
Hint 3
Keep duplicate groups when any region, status, or NULL-aware phone version count exceeds one.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT customer_key, COUNT(*) AS row_count, COUNT(DISTINCT region) AS region_versions, COUNT(DISTINCT status) AS status_versions, COUNT(DISTINCT phone) + CASE WHEN COUNT(phone) < COUNT(*) THEN 1 ELSE 0 END AS phone_versions_including_null 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 OR COUNT(DISTINCT phone) + CASE WHEN COUNT(phone) < COUNT(*) THEN 1 ELSE 0 END > 1) ORDER BY customer_key;Why this works
SQL DISTINCT aggregates ignore NULL. Combining the non-NULL distinct count with an explicit NULL-presence flag produces a truthful version count without inventing a sentinel value that could collide with real data.
Success check
C100 exposes both a status conflict and a NULL-versus-populated phone conflict, C200 exposes a region conflict, and identical C500 retries remain excluded.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| customer_key | row_count | region_versions | status_versions | phone_versions_including_null |
|---|---|---|---|---|
| C100 | 3 | 1 | 2 | 2 |
| C200 | 2 | 2 | 1 | 1 |
Learn the concepts behind this answer
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.