Guard a Conditional Failure-Rate Calculation
Return NULL for zero or missing processed_rows; otherwise calculate failed_rows as a percentage of processed_rows.
- CASE expressions
- NULL handling
- Sorting
Exercise brief
Understand the request
Pipeline quality analyst A failure-rate metric must not divide by zero when an event processed no rows.
A failure-rate metric must not divide by zero when an event processed no rows. Return NULL for zero or missing processed_rows; otherwise calculate failed_rows as a percentage of processed_rows.
Return
- Return event_id, processed_rows, failed_rows, and failure_rate_pct.
- Order by event_id.
Constraints
- Use CASE to guard the scalar division.
- Force decimal rather than integer division.
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
Test processed_rows IS NULL OR processed_rows = 0 before division.
Hint 2
Return NULL from the guarded branch.
Hint 3
Multiply by 100.0 so all engines perform decimal division.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, processed_rows, failed_rows, CASE WHEN processed_rows IS NULL OR processed_rows = 0 THEN NULL ELSE failed_rows * 100.0 / processed_rows END AS failure_rate_pct FROM pipeline_events ORDER BY event_id;Why this works
A scalar CASE guard is a portable conditional-calculation pattern. The curriculum deliberately avoids claiming that CASE universally suppresses every possible error: planners and aggregate expressions can have engine-specific evaluation timing.
Success check
Zero denominators return NULL and nonzero rows retain fractional percentages such as 2.5.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | processed_rows | failed_rows | failure_rate_pct |
|---|---|---|---|
| 101 | 1000 | 0 | 0 |
| 102 | 800 | 8 | 1 |
| 103 | 1000 | 25 | 2.5 |
| 104 | 1000 | 50 | 5 |
| 208 | 0 | 0 | NULL |
| 209 | 400 | 40 | 10 |
| 210 | 400 | 4 | 1 |
| 303 | 500 | 5 | 1 |
| 304 | 500 | 10 | 2 |
| 305 | 500 | 20 | 4 |
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.