Route Incidents with Nested CASE
Branch first by is_customer_facing, then apply a smaller status-and-severity decision inside each branch.
- CASE expressions
- Sorting
Exercise brief
Understand the request
Incident routing architect Customer-facing and internal workloads use different routing decisions.
Customer-facing and internal workloads use different routing decisions. Branch first by is_customer_facing, then apply a smaller status-and-severity decision inside each branch.
Return
- Return event_id, is_customer_facing, status_code, severity, and routing_queue.
- Order by event_id.
Constraints
- Use an outer CASE for customer-facing versus internal events.
- Use an inner CASE in both outer 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
Use the outer CASE only to select the policy family.
Hint 2
The customer branch needs Incident versus Watch.
Hint 3
The internal branch needs Incident, Watch, and Routine.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, is_customer_facing, status_code, severity, CASE WHEN is_customer_facing = 1 THEN CASE WHEN status_code = 'FAIL' OR severity = 'critical' THEN 'Customer Incident' ELSE 'Customer Watch' END ELSE CASE WHEN status_code = 'FAIL' THEN 'Internal Incident' WHEN status_code = 'WARN' THEN 'Internal Watch' ELSE 'Routine' END END AS routing_queue FROM pipeline_events ORDER BY event_id;Why this works
Nested CASE can represent a small hierarchical decision table. It should stay shallow and readable; larger policies belong in governed rule tables rather than deeply nested expressions.
Success check
Customer and internal events with similar status values route to their correct distinct queues.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | is_customer_facing | status_code | severity | routing_queue |
|---|---|---|---|---|
| 101 | 1 | OK | low | Customer Watch |
| 102 | 1 | RUNNING | medium | Customer Watch |
| 103 | 1 | WARN | critical | Customer Incident |
| 104 | 1 | FAIL | critical | Customer Incident |
| 208 | 0 | WARN | high | Internal Watch |
| 209 | 0 | FAIL | high | Internal Incident |
| 210 | 0 | OK | medium | Routine |
| 303 | 1 | NULL | medium | Customer Watch |
| 304 | 1 | NULL | Customer Watch | |
| 305 | 1 | FAIL | medium | Customer Incident |
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.