Build a Precedence-Safe Operations Work Queue
Create workstream, failed_flag, and assigned_team using explicit precedence and defaults.
- CASE expressions
- NULL handling
- Sorting
Exercise brief
Understand the request
Data operations director A single work queue must classify the next action, expose a failure flag, and route unowned work to triage.
A single work queue must classify the next action, expose a failure flag, and route unowned work to triage. Create workstream, failed_flag, and assigned_team using explicit precedence and defaults.
Return
- Return event_id, pipeline_name, workstream, failed_flag, and assigned_team.
- Order by event_id.
Constraints
- Workstream precedence is Data Quality, Maintenance, Incident, Needs Measurement, Needs Target, Performance, then Normal.
- Use independent CASE expressions for the flag and assignment fallback.
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
Write the workstream CASE from highest to lowest business precedence.
Hint 2
Keep failed_flag as its own 1/0 CASE.
Hint 3
Use a third CASE to replace NULL owner_team with triage.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, pipeline_name, CASE WHEN status_code IS NULL OR status_code = '' THEN 'Data Quality' WHEN maintenance_mode = 1 THEN 'Maintenance' WHEN status_code = 'FAIL' OR severity = 'critical' THEN 'Incident' WHEN actual_ms IS NULL OR target_ms IS NULL THEN 'Needs Measurement' WHEN target_ms <= 0 THEN 'Needs Target' WHEN actual_ms > target_ms THEN 'Performance' ELSE 'Normal' END AS workstream, CASE WHEN status_code = 'FAIL' THEN 1 ELSE 0 END AS failed_flag, CASE WHEN owner_team IS NULL THEN 'triage' ELSE owner_team END AS assigned_team FROM pipeline_events ORDER BY event_id;Why this works
The capstone treats CASE as a production transformation tool: precedence resolves overlaps, independent expressions preserve reusable attributes, and explicit fallbacks keep downstream contracts non-NULL.
Success check
Every adversarial row receives one explainable workstream, a numeric failure flag, and a non-NULL assigned team.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | pipeline_name | workstream | failed_flag | assigned_team |
|---|---|---|---|---|
| 101 | orders_ingest | Normal | 0 | core |
| 102 | orders_ingest | Normal | 0 | core |
| 103 | orders_ingest | Incident | 0 | core |
| 104 | orders_ingest | Incident | 1 | core |
| 208 | customer_sync | Normal | 0 | triage |
| 209 | customer_sync | Incident | 1 | triage |
| 210 | customer_sync | Normal | 0 | data |
| 303 | billing_rollup | Data Quality | 0 | finance |
| 304 | billing_rollup | Data Quality | 0 | finance |
| 305 | billing_rollup | Incident | 1 | finance |
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.