Quarantine Missing Business Keys
Return records with at least one missing business-key field and count their missing_key_fields.
- CASE expressions
- String functions
- NULL handling
- Filtering
- Sorting
Exercise brief
Understand the request
Warehouse quality steward Rows cannot participate safely in identity matching when a customer key, external ID, or usable email is missing.
Rows cannot participate safely in identity matching when a customer key, external ID, or usable email is missing. Return records with at least one missing business-key field and count their missing_key_fields.
Return
- Return record_id, customer_key, external_id, email, and missing_key_fields.
- Order by missing_key_fields descending, then record_id.
Constraints
- Treat NULL and whitespace-only email values as missing.
- Count customer_key, external_id, and email independently with CASE.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
quality_records
record_idINTEGERcustomer_keyTEXTexternal_idTEXTemailTEXT
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Missing text can be represented by NULL or by only spaces.
Hint 2
TRIM(email) = '' catches the blank representation without matching NULL.
Hint 3
Add three CASE flags and reuse the same predicates in WHERE.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT record_id, customer_key, external_id, email, 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 AS missing_key_fields FROM quality_records WHERE customer_key IS NULL OR external_id IS NULL OR email IS NULL OR TRIM(email) = '' ORDER BY missing_key_fields DESC, record_id;Why this works
Separating missing-key detection from duplicate grouping prevents NULL and blank placeholders from becoming false duplicate identities.
Success check
The NULL email and the mostly blank record are both caught with the correct issue counts.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| record_id | customer_key | external_id | missing_key_fields | |
|---|---|---|---|---|
| 1007 | NULL | NULL | 3 | |
| 1006 | C300 | EVT-003 | NULL | 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.