# Off Target > Most models are fine. The bottom 10% are not. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem The ML platform team is auditing model quality for the first half of 2026, judging every model against a 0.95 accuracy target. Surface the 10% of models sitting farthest from that target, along with each model's accuracy and how far it lands from 0.95. ## Worked solution and explanation ### What this really tests Strip the ML costume off and this is a top-decile query over an absolute deviation. The skill being probed: can you turn 'the 10% of models farthest from 0.95' into a bucket over ABS(accuracy - 0.95)? Anyone can write NTILE. The trap is direction plus an off-by-one. Sorting the gap ascending puts the biggest misses in bucket 10, not bucket 1, and 'top 10%' is decile 10, not decile 9. Reach for decile 9 and you hand back the 80-to-90 band, the second-worst models, quietly missing every real outlier the audit was looking for. --- ### Working through it #### Step 1: Scope the audit window Filter ml_models with train_at BETWEEN '2026-01-01' AND '2026-06-30'. The first half of the year is inclusive on both ends. If train_at is stored as a full timestamp, the upper bound clips any June 30 evening rows, so call that out and switch to an exclusive '2026-07-01' bound if it matters. #### Step 2: Compute the gap Use ABS(accuracy - 0.95) so a model at 0.99 and a model at 0.91 both count as 0.04 away. The prompt asks how far each model lands from the target, and a signed difference would let an over-performer cancel out and hide. #### Step 3: Bucket into deciles NTILE(10) OVER (ORDER BY ABS(accuracy - 0.95)) drops each model into a 1-through-10 bucket by ascending gap. Ascending means the smallest gaps land in bucket 1 and the largest in bucket 10, so the worst outliers you want are decile 10, roughly ranks 1351 through 1500 on the 1500-row set. #### Step 4: Filter and project Wrap the window in a subquery, then WHERE decile = 10. Project mdl_name, accuracy, and CAST(ABS(accuracy - 0.95) AS REAL) AS accuracy_gap. The cast just guarantees the contract's decimal type on the gap column. --- ### The solution **Worst decile by gap** ```sql SELECT mdl_name, accuracy, CAST(ABS(accuracy - 0.95) AS REAL) AS accuracy_gap FROM ( SELECT mdl_name, accuracy, ABS(accuracy - 0.95) AS accuracy_diff, NTILE(10) OVER (ORDER BY ABS(accuracy - 0.95)) AS decile FROM ml_models WHERE train_at BETWEEN '2026-01-01' AND '2026-06-30' ) WHERE decile = 10 ``` > **Cost analysis** > > 1500 rows is trivial, but NTILE forces a full sort over the filtered set and no index helps the window itself. An index on train_at prunes the H1 scan. At 1.5M rows, push ABS(accuracy - 0.95) into a CTE so the sort runs on a thin two-column projection. > **Interviewers watch for** > > Ask which 'top 10%' semantics the team wants. NTILE(10) = 10 returns a bucket of models, PERCENTILE_DISC(0.9) returns one real model's gap as a cutoff, PERCENTILE_CONT(0.9) interpolates a synthetic cutoff. On 1500 rows they select different sets, and naming the difference unprompted reads as senior. > **Common pitfall** > > Two off-by-ones lurk here. Writing WHERE decile = 9 because it 'feels like the 90th' actually returns the 80-to-90 band. Or flipping the sort to ORDER BY ABS(...) DESC and still asking for decile 10, which now returns the models closest to target. Fix the sort direction and the bucket number together, not one at a time. > **The elegant move** > > Compute ABS(accuracy - 0.95) once in the subquery. The inner ORDER BY and the outer projection both need it, and recomputing the literal 0.95 in two places is exactly where a typo drifts the sort away from the number you report. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - Rewrite this with PERCENTILE_CONT and explain when its cutoff diverges from the NTILE bucket. _(Probes bucket-based versus interpolated percentile semantics.)_ - How would you compute the farthest-10% gap per framework instead of globally? _(Tests PARTITION BY framework on the window and the gotcha that small frameworks get coarse buckets.)_ - What if accuracy can be NULL for failed training runs? _(Probes whether NULL gaps sort first into bucket 1 and whether you filter them before the window.)_ - The team wants a clean cutoff at exactly the 90th percentile, not a whole decile. What changes? _(Tests moving to PERCENT_RANK >= 0.9 or a percentile cutoff for sharper semantics.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/off_target) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.