Calculate a Divide-by-Zero-Safe Rate
Calculate units_per_hour, rounded to two decimals, using NULL for zero elapsed_hours.
- Numeric functions
- NULL handling
- Sorting
Exercise brief
Understand the request
Pipeline capacity analyst A throughput report must retain zero-hour records without raising a division error.
A throughput report must retain zero-hour records without raising a division error. Calculate units_per_hour, rounded to two decimals, using NULL for zero elapsed_hours.
Return
- Return record_id, completed_units, elapsed_hours, and units_per_hour.
- Order by record_id ascending.
Constraints
- Protect the denominator with NULLIF(elapsed_hours, 0).
- Do not filter zero-hour records out.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
function_cases
record_idINTEGERfirst_nameTEXTlast_nameTEXTlabelTEXTemailTEXTbackup_emailTEXTphoneTEXTraw_unitsTEXTraw_quantityTEXTraw_statusTEXTactual_valueREALtarget_valueREALcompleted_unitsREALelapsed_hoursREAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Dividing by NULL produces NULL instead of a divide-by-zero exception.
Hint 2
NULLIF(elapsed_hours, 0) returns NULL only for a zero denominator.
Hint 3
Put the protected division inside ROUND(..., 2).
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT record_id, completed_units, elapsed_hours, ROUND(completed_units / NULLIF(elapsed_hours, 0), 2) AS units_per_hour FROM function_cases ORDER BY record_id;Why this works
NULLIF is the standard portable guard for ratios. It preserves the source row and represents an undefined rate honestly as NULL.
Success check
All eight records remain visible and zero denominators yield NULL rates.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| record_id | completed_units | elapsed_hours | units_per_hour |
|---|---|---|---|
| 101 | 20 | 4 | 5 |
| 102 | 12 | 0 | NULL |
| 103 | 0 | 0 | NULL |
| 104 | 15 | 3 | 5 |
| 105 | 10 | 2.5 | 4 |
| 106 | 7 | 2 | 3.5 |
| 107 | 8 | 4 | 2 |
| 108 | 9 | 3 | 3 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Date Operations & Time-Based Analytics
Practice date arithmetic, safe timestamp ranges, calendar bucketing, dense time series, rolling windows, growth, and cohort analysis.
Open the interactive workspace and practice across SQL topics.