Combine Simple CASE and Searched CASE Points
Map severity to 8/5/3/1/0 points with simple CASE, then add two points when retries are at least three.
- CASE expressions
- Sorting
Exercise brief
Understand the request
Remediation program manager A prioritization score combines a fixed severity lookup with a retry escalation rule.
A prioritization score combines a fixed severity lookup with a retry escalation rule. Map severity to 8/5/3/1/0 points with simple CASE, then add two points when retries are at least three.
Return
- Return event_id, severity, retry_count, and remediation_points.
- Order by remediation_points descending, then event_id.
Constraints
- Use simple CASE for severity equality mapping.
- Use a separate searched CASE for the retry threshold.
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
Start with CASE severity WHEN critical THEN 8 and the other fixed mappings.
Hint 2
Add a second CASE WHEN retry_count >= 3 THEN 2 ELSE 0 END.
Hint 3
Use ELSE 0 in both components so missing inputs do not nullify the sum.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, severity, retry_count, CASE severity WHEN 'critical' THEN 8 WHEN 'high' THEN 5 WHEN 'medium' THEN 3 WHEN 'low' THEN 1 ELSE 0 END + CASE WHEN retry_count >= 3 THEN 2 ELSE 0 END AS remediation_points FROM pipeline_events ORDER BY remediation_points DESC, event_id;Why this works
Simple and searched CASE solve different subproblems and can be composed in one scalar expression. Common numeric branch types keep the result portable across engines.
Success check
Both CASE forms contribute to one deterministic numeric expression.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | severity | retry_count | remediation_points |
|---|---|---|---|
| 104 | critical | 3 | 10 |
| 103 | critical | 2 | 8 |
| 209 | high | 4 | 7 |
| 407 | high | 3 | 7 |
| 208 | high | 2 | 5 |
| 102 | medium | 1 | 3 |
| 210 | medium | 0 | 3 |
| 303 | medium | NULL | 3 |
| 305 | medium | 1 | 3 |
| 101 | low | 0 | 1 |
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.