Choose the First Usable Contact
Return the first nonblank trimmed email, or 'unreachable' when neither address is usable.
- String functions
- NULL handling
- Sorting
Exercise brief
Understand the request
Notification platform engineer A notification export should use the primary email, then the backup, then a clear fallback.
A notification export should use the primary email, then the backup, then a clear fallback. Return the first nonblank trimmed email, or 'unreachable' when neither address is usable.
Return
- Return record_id and contact_email.
- Order by record_id ascending.
Constraints
- Convert empty or whitespace-only values to NULL with NULLIF.
- Use COALESCE in primary, backup, literal order.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
function_cases
record_idINTEGERfirst_nameTEXTlast_nameTEXTlabelTEXTemailTEXTbackup_emailTEXTphoneTEXTraw_unitsTEXTraw_quantityTEXTraw_statusTEXTactual_valueREALtarget_valueREALcompleted_unitsREALelapsed_hoursREAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
COALESCE treats only NULL as missing; an empty string may need normalization first.
Hint 2
NULLIF(TRIM(value), '') turns blank text into NULL.
Hint 3
Place normalized primary, normalized backup, then the literal fallback in COALESCE.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT record_id, COALESCE(NULLIF(TRIM(email), ''), NULLIF(TRIM(backup_email), ''), 'unreachable') AS contact_email FROM function_cases ORDER BY record_id;Why this works
Combining NULLIF with COALESCE distinguishes usable text from empty placeholders and implements a portable priority chain.
Success check
Blank strings never win the fallback chain and every record receives one contact_email.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| record_id | contact_email |
|---|---|
| 101 | Alice.NG@Example.COM |
| 102 | bob.backup@example.com |
| 103 | unreachable |
| 104 | dave.smith@example.com |
| 105 | eve.li@example.com |
| 106 | not-an-email |
| 107 | grace@sub.example.com |
| 108 | heidi@example.com |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Date Operations & Time-Based Analytics
Practice date arithmetic, safe timestamp ranges, calendar bucketing, dense time series, rolling windows, growth, and cohort analysis.
Open the interactive workspace and practice across SQL topics.