Map Pipeline Status Codes with Simple CASE
Use simple CASE to map OK, WARN, FAIL, and RUNNING while preserving an Unmapped fallback.
- CASE expressions
- Sorting
Exercise brief
Understand the request
Data platform analyst An operations feed exposes compact status codes that need readable labels.
An operations feed exposes compact status codes that need readable labels. Use simple CASE to map OK, WARN, FAIL, and RUNNING while preserving an Unmapped fallback.
Return
- Return event_id, status_code, and status_label.
- Order by event_id.
Constraints
- Use CASE status_code WHEN value rather than searched CASE.
- Map every unrecognized value, blank, and NULL to Unmapped with ELSE.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
pipeline_events
event_idINTEGERpipeline_nameTEXTstatus_codeTEXTseverityTEXTactual_msINTEGERtarget_msINTEGERretry_countINTEGERprocessed_rowsINTEGERfailed_rowsINTEGERowner_teamTEXTis_customer_facingINTEGERsource_systemTEXTmaintenance_modeINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Simple CASE is best when every branch compares one expression for equality.
Hint 2
Start with CASE status_code and add one WHEN literal per known code.
Hint 3
Finish with ELSE Unmapped so nonmatches do not silently become NULL.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, status_code, CASE status_code WHEN 'OK' THEN 'Succeeded' WHEN 'WARN' THEN 'Needs Attention' WHEN 'FAIL' THEN 'Failed' WHEN 'RUNNING' THEN 'In Progress' ELSE 'Unmapped' END AS status_label FROM pipeline_events ORDER BY event_id;Why this works
Simple CASE is a compact code-to-label lookup. Equality with NULL is never true, so the explicit ELSE safely absorbs missing and unexpected codes.
Success check
Every event has the requested label and the NULL, blank, and UNKNOWN codes use the fallback.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | status_code | status_label |
|---|---|---|
| 101 | OK | Succeeded |
| 102 | RUNNING | In Progress |
| 103 | WARN | Needs Attention |
| 104 | FAIL | Failed |
| 208 | WARN | Needs Attention |
| 209 | FAIL | Failed |
| 210 | OK | Succeeded |
| 303 | NULL | Unmapped |
| 304 | Unmapped | |
| 305 | FAIL | Failed |
Previewing 10 of 14 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
Basic SQL Functions
Practice production-oriented string cleanup, numeric transformations, NULL handling, tolerant conversion, and delimiter parsing.
Database Changes
Practice safe SQL data changes, schema evolution, retry-safe upserts, transactions, rollback, views, and indexes in isolated databases.
Open the interactive workspace and practice across SQL topics.