Detect Duplicate Composite Source IDs
Find repeated (source_system, external_id) combinations.
- Aggregation
- HAVING
- NULL handling
- Filtering
- Sorting
Exercise brief
Understand the request
Ingestion reliability engineer Event IDs are unique only within a source system, so monitoring either column alone would create false positives.
Event IDs are unique only within a source system, so monitoring either column alone would create false positives. Find repeated (source_system, external_id) combinations.
Return
- Return source_system, external_id, and duplicate_count.
- Order by duplicate_count descending, then source_system and external_id.
Constraints
- Group by both columns as one composite business key.
- Exclude rows whose external_id is NULL.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
quality_records
source_systemTEXTexternal_idTEXT
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
The declared grain contains two columns.
Hint 2
Place both source_system and external_id in GROUP BY.
Hint 3
Use HAVING COUNT(*) > 1 after excluding missing external IDs.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT source_system, external_id, COUNT(*) AS duplicate_count FROM quality_records WHERE external_id IS NOT NULL GROUP BY source_system, external_id HAVING COUNT(*) > 1 ORDER BY duplicate_count DESC, source_system, external_id;Why this works
Composite-key checks reflect the actual uniqueness contract. Grouping only external_id would confuse identifiers owned by different producers.
Success check
The two repeated source-scoped identifiers are returned without merging IDs across sources.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| source_system | external_id | duplicate_count |
|---|---|---|
| crm | EVT-001 | 3 |
| erp | EVT-006 | 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.