Combine Boolean Rules into a Response Action
Suppress maintenance events, Page repeated failures, Ticket other failures, warnings, or three-plus retries, and Observe everything else.
- CASE expressions
- Sorting
Exercise brief
Understand the request
On-call automation engineer Alert automation must combine maintenance, status, and retry signals without losing boolean precedence.
Alert automation must combine maintenance, status, and retry signals without losing boolean precedence. Suppress maintenance events, Page repeated failures, Ticket other failures, warnings, or three-plus retries, and Observe everything else.
Return
- Return event_id, status_code, retry_count, maintenance_mode, and response_action.
- Order by event_id.
Constraints
- Maintenance suppression has the highest precedence.
- Use parentheses or branch structure so AND and OR logic is unambiguous.
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
Resolve the maintenance exception before operational alerts.
Hint 2
The Page branch needs FAIL and retry_count at least 3.
Hint 3
The Ticket branch accepts any failure, warning, or high retry count.
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, maintenance_mode, CASE WHEN maintenance_mode = 1 THEN 'Suppress' WHEN status_code = 'FAIL' AND retry_count >= 3 THEN 'Page' WHEN status_code IN ('FAIL', 'WARN') OR retry_count >= 3 THEN 'Ticket' ELSE 'Observe' END AS response_action FROM pipeline_events ORDER BY event_id;Why this works
Readable CASE branches make mixed AND/OR policies auditable. The ordering also documents business precedence independently of SQL operator precedence.
Success check
Repeated failures Page, ordinary failures and warnings Ticket, and maintenance always Suppresses.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | status_code | retry_count | maintenance_mode | response_action |
|---|---|---|---|---|
| 101 | OK | 0 | 0 | Observe |
| 102 | RUNNING | 1 | 0 | Observe |
| 103 | WARN | 2 | 0 | Ticket |
| 104 | FAIL | 3 | 0 | Page |
| 208 | WARN | 2 | 0 | Ticket |
| 209 | FAIL | 4 | 0 | Page |
| 210 | OK | 0 | 0 | Observe |
| 303 | NULL | NULL | 0 | Observe |
| 304 | 1 | 0 | Observe | |
| 305 | FAIL | 1 | 0 | Ticket |
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.