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
- Sorting
Exercise brief
Understand the request
Customer data platform analyst An ingestion control must identify non-NULL customer keys that no longer satisfy the one-row-per-customer contract.
An ingestion control must identify non-NULL customer keys that no longer satisfy the one-row-per-customer contract. Return each repeated customer_key and its duplicate_count, ordered by duplicate_count descending and customer_key ascending.
Return
- Return customer_key and duplicate_count.
- Order by duplicate_count descending, then customer_key ascending.
Constraints
- Exclude NULL customer keys from duplicate grouping.
- Use GROUP BY with HAVING COUNT(*) > 1.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
quality_records
customer_keyTEXT
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A duplicate check should first define the business key and its NULL policy.
Hint 2
Group only non-NULL customer_key values, then filter groups after aggregation.
Hint 3
Use HAVING COUNT(*) > 1 and a deterministic two-column ORDER BY.
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 FROM quality_records WHERE customer_key IS NOT NULL GROUP BY customer_key HAVING COUNT(*) > 1 ORDER BY duplicate_count DESC, customer_key;Why this works
GROUP BY establishes the uniqueness grain, while HAVING filters aggregated groups. Excluding NULL makes the rule explicit instead of accidentally treating missing identifiers as one customer.
Success check
Only the three repeated non-NULL customer keys are reported at key grain.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| customer_key | duplicate_count |
|---|---|
| C100 | 3 |
| C200 | 2 |
| C500 | 2 |
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.