Classify Pipeline Health with Searched CASE
Classify missing codes as Unknown, failures as Failed, warnings or three-plus retries as Degraded, running events as Active, and all others as Healthy.
- CASE expressions
- NULL handling
- Sorting
Exercise brief
Understand the request
Reliability reporting analyst A monitoring view needs one health state derived from status and retry signals.
A monitoring view needs one health state derived from status and retry signals. Classify missing codes as Unknown, failures as Failed, warnings or three-plus retries as Degraded, running events as Active, and all others as Healthy.
Return
- Return event_id, status_code, retry_count, and health_state.
- Order by event_id.
Constraints
- Use searched CASE with an explicit ELSE.
- Test missing and blank status codes before all operational branches.
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
Searched CASE supports different predicates in each WHEN.
Hint 2
Put the NULL-or-blank test first and keep the failure branch ahead of the retry branch.
Hint 3
Use ELSE Healthy only after every exceptional state has been handled.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, status_code, retry_count, CASE WHEN status_code IS NULL OR status_code = '' THEN 'Unknown' WHEN status_code = 'FAIL' THEN 'Failed' WHEN status_code = 'WARN' OR retry_count >= 3 THEN 'Degraded' WHEN status_code = 'RUNNING' THEN 'Active' ELSE 'Healthy' END AS health_state FROM pipeline_events ORDER BY event_id;Why this works
Searched CASE is the standard form for multi-column classification. An explicit missing-data branch prevents SQL three-valued logic from turning unknown inputs into an accidental healthy label.
Success check
Each event receives exactly one state and missing data cannot fall through to Healthy.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | status_code | retry_count | health_state |
|---|---|---|---|
| 101 | OK | 0 | Healthy |
| 102 | RUNNING | 1 | Active |
| 103 | WARN | 2 | Degraded |
| 104 | FAIL | 3 | Failed |
| 208 | WARN | 2 | Degraded |
| 209 | FAIL | 4 | Failed |
| 210 | OK | 0 | Healthy |
| 303 | NULL | NULL | Unknown |
| 304 | 1 | Unknown | |
| 305 | FAIL | 1 | 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.