Bucket Latency at Exact SLA Boundaries
Classify each event using actual_ms relative to target_ms, treating only values strictly above two times target as Severe Breach.
- CASE expressions
- NULL handling
- Sorting
Exercise brief
Understand the request
Pipeline performance engineer An SLA report must distinguish missing measurements, invalid targets, ordinary breaches, and severe breaches.
An SLA report must distinguish missing measurements, invalid targets, ordinary breaches, and severe breaches. Classify each event using actual_ms relative to target_ms, treating only values strictly above two times target as Severe Breach.
Return
- Return event_id, actual_ms, target_ms, and latency_band.
- Order by event_id.
Constraints
- Check NULL inputs and nonpositive targets before performing threshold comparisons.
- An actual value exactly equal to two times target is Breach, not Severe Breach.
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
Order the exception branches before the numeric buckets.
Hint 2
Test the most severe threshold before the ordinary breach threshold.
Hint 3
Use > rather than >= for both breach boundaries.
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 'Unknown' WHEN target_ms <= 0 THEN 'Invalid Target' WHEN actual_ms > target_ms * 2 THEN 'Severe Breach' WHEN actual_ms > target_ms THEN 'Breach' ELSE 'Within Target' END AS latency_band FROM pipeline_events ORDER BY event_id;Why this works
Boundary-safe bucketing depends on mutually understandable rules and top-down precedence. Adversarial values around every cutoff expose off-by-one and wrong-order mistakes.
Success check
The exact-target, exact-double, just-over-target, just-under-double, and just-over-double rows all land in the correct bands.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | actual_ms | target_ms | latency_band |
|---|---|---|---|
| 101 | 90 | 100 | Within Target |
| 102 | 100 | 100 | Within Target |
| 103 | 199 | 100 | Breach |
| 104 | 210 | 100 | Severe Breach |
| 208 | 200 | 200 | Within Target |
| 209 | 401 | 200 | Severe Breach |
| 210 | 200 | 200 | Within Target |
| 303 | NULL | 300 | Unknown |
| 304 | 600 | 300 | Breach |
| 305 | 301 | 300 | Breach |
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.