Model a Four-State SLA Outcome
Return Not Measured, Invalid Target, Breached, or Met in that precedence order.
- CASE expressions
- NULL handling
- Sorting
Exercise brief
Understand the request
Service-level governance analyst A two-state pass/fail label would hide missing measurements and invalid targets.
A two-state pass/fail label would hide missing measurements and invalid targets. Return Not Measured, Invalid Target, Breached, or Met in that precedence order.
Return
- Return event_id, actual_ms, target_ms, and sla_state.
- Order by event_id.
Constraints
- Handle NULL measurement inputs before target validation.
- Treat actual_ms equal to target_ms as Met.
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
SQL predicates involving NULL evaluate to unknown, so handle missing values explicitly.
Hint 2
Invalid targets need their own branch before the breach comparison.
Hint 3
Once exceptions are removed, ELSE can safely mean Met.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, actual_ms, target_ms, CASE WHEN actual_ms IS NULL OR target_ms IS NULL THEN 'Not Measured' WHEN target_ms <= 0 THEN 'Invalid Target' WHEN actual_ms > target_ms THEN 'Breached' ELSE 'Met' END AS sla_state FROM pipeline_events ORDER BY event_id;Why this works
Production data contracts often require more than true and false. CASE makes missing, invalid, breached, and met states explicit instead of collapsing unknown data into a misleading success.
Success check
Every data-quality state remains distinct from a true SLA result.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | actual_ms | target_ms | sla_state |
|---|---|---|---|
| 101 | 90 | 100 | Met |
| 102 | 100 | 100 | Met |
| 103 | 199 | 100 | Breached |
| 104 | 210 | 100 | Breached |
| 208 | 200 | 200 | Met |
| 209 | 401 | 200 | Breached |
| 210 | 200 | 200 | Met |
| 303 | NULL | 300 | Not Measured |
| 304 | 600 | 300 | Breached |
| 305 | 301 | 300 | Breached |
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.