Compose CASE Expressions into a Risk Score
Add five points for failure, severity points of 3/2/0, two points for three-plus retries, and one point for missing ownership.
- CASE expressions
- NULL handling
- Sorting
Exercise brief
Understand the request
Reliability scoring analyst A triage queue needs an additive score whose components remain visible in SQL.
A triage queue needs an additive score whose components remain visible in SQL. Add five points for failure, severity points of 3/2/0, two points for three-plus retries, and one point for missing ownership.
Return
- Return event_id and risk_score.
- Order by risk_score descending, then event_id.
Constraints
- Build the score by adding multiple CASE expressions.
- Critical severity is 3, high is 2, and every other severity is 0.
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
Treat each policy component as a numeric CASE.
Hint 2
The severity component needs two WHEN branches and ELSE 0.
Hint 3
Wrap the CASE expressions in parentheses and add them together.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, (CASE WHEN status_code = 'FAIL' THEN 5 ELSE 0 END + CASE WHEN severity = 'critical' THEN 3 WHEN severity = 'high' THEN 2 ELSE 0 END + CASE WHEN retry_count >= 3 THEN 2 ELSE 0 END + CASE WHEN owner_team IS NULL THEN 1 ELSE 0 END) AS risk_score FROM pipeline_events ORDER BY risk_score DESC, event_id;Why this works
CASE returns a scalar value, so independent conditional components can be composed with arithmetic. Explicit numeric defaults prevent NULL from nullifying the entire score.
Success check
The score components add correctly and tied scores use deterministic event_id order.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | risk_score |
|---|---|
| 104 | 10 |
| 209 | 10 |
| 305 | 5 |
| 407 | 4 |
| 103 | 3 |
| 208 | 3 |
| 306 | 1 |
| 101 | 0 |
| 102 | 0 |
| 210 | 0 |
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.