Rank Absolute Target Deviations
Calculate absolute_deviation as the absolute difference between actual_value and target_value.
- Numeric functions
- Sorting
Exercise brief
Understand the request
Data observability analyst A pipeline health report ranks records by distance from their target regardless of direction.
A pipeline health report ranks records by distance from their target regardless of direction. Calculate absolute_deviation as the absolute difference between actual_value and target_value.
Return
- Return record_id, actual_value, target_value, and absolute_deviation.
- Order largest deviation first, then record_id ascending.
Constraints
- Use ABS rather than separate above-target and below-target branches.
- Use record_id as the deterministic tie-breaker.
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
Subtracting values preserves direction; ABS removes that sign.
Hint 2
The largest anomaly has the largest absolute distance.
Hint 3
Order ABS(actual_value - target_value) descending, then record_id.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT record_id, actual_value, target_value, ROUND(ABS(actual_value - target_value), 1) AS absolute_deviation FROM function_cases ORDER BY absolute_deviation DESC, record_id;Why this works
ABS converts signed variance into a direction-free magnitude, a common data-quality and anomaly-ranking pattern.
Success check
Over-target and under-target records share one comparable, correctly ordered distance measure.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| record_id | actual_value | target_value | absolute_deviation |
|---|---|---|---|
| 104 | 80 | 100 | 20 |
| 107 | 110 | 100 | 10 |
| 106 | 95 | 100 | 5 |
| 108 | 90 | 95 | 5 |
| 102 | 104 | 100 | 4 |
| 103 | 87.5 | 90 | 2.5 |
| 101 | 98.4 | 100 | 1.6 |
| 105 | 100 | 100 | 0 |
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.