Apply First-Match Incident Priority
Assign P1 to failures or critical severity, P2 to customer-facing warnings, P3 to remaining warnings, and Monitor otherwise.
- CASE expressions
- Sorting
Exercise brief
Understand the request
Incident management lead Overlapping alert rules must resolve to one deterministic incident priority.
Overlapping alert rules must resolve to one deterministic incident priority. Assign P1 to failures or critical severity, P2 to customer-facing warnings, P3 to remaining warnings, and Monitor otherwise.
Return
- Return event_id, status_code, severity, is_customer_facing, and incident_priority.
- Order by event_id.
Constraints
- Place the broad P1 rule before the warning rules.
- Keep the customer-facing warning branch before the remaining-warning branch.
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
CASE stops at the first true WHEN.
Hint 2
Start with status_code = FAIL OR severity = critical.
Hint 3
Put customer-facing WARN before the generic WARN branch.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, status_code, severity, is_customer_facing, CASE WHEN status_code = 'FAIL' OR severity = 'critical' THEN 'P1' WHEN status_code = 'WARN' AND is_customer_facing = 1 THEN 'P2' WHEN status_code = 'WARN' THEN 'P3' ELSE 'Monitor' END AS incident_priority FROM pipeline_events ORDER BY event_id;Why this works
Overlapping decision rules are encoded by branch order. A dataset row that satisfies both P1 and P2 makes incorrect precedence observable instead of coincidentally passing.
Success check
The critical warning is P1 rather than P2, proving that the first matching branch wins.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | status_code | severity | is_customer_facing | incident_priority |
|---|---|---|---|---|
| 101 | OK | low | 1 | Monitor |
| 102 | RUNNING | medium | 1 | Monitor |
| 103 | WARN | critical | 1 | P1 |
| 104 | FAIL | critical | 1 | P1 |
| 208 | WARN | high | 0 | P3 |
| 209 | FAIL | high | 0 | P1 |
| 210 | OK | medium | 0 | Monitor |
| 303 | NULL | medium | 1 | Monitor |
| 304 | NULL | 1 | Monitor | |
| 305 | FAIL | medium | 1 | P1 |
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.