Off Target

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

American Express

Problem

ml_models holds one row per trained model version, stamped with the moment training finished and the accuracy it scored against a 0.95 target. Every scored run finished from the start of 2026 through the April 4 freeze, that day included, belongs in one of ten tenths by how far it lands from the target, the farthest in tenth 10. When two runs sit the same distance off, the lower model id takes the nearer tenth.

Most models are fine. The bottom 10% are not.

Tables

ml_models

Columns: model_id INTEGER, mdl_name TEXT, version TEXT, accuracy REAL, status TEXT, train_at TEXT, framework TEXT

Expected output

Worked solution and explanation

What this really tests

Take away the ML framing and this is decile banding over an absolute deviation, run on a column that misrepresents its own type. Anyone can type NTILE(10). What separates answers is which rows go into it. Band on raw accuracy and a 0.99 model looks like one of the best in the fleet, when it is 0.04 off. Leave the unscored models in and their NULL gaps sort to the front, filling tenth 1 and pushing every real model outward. And train_at is a full timestamp stored as text. Compare it raw against '2026-04-04' and every freeze-day run disappears. Because every tenth is sized from the whole population, the boundaries around that gap move too.

Working through it

Step 1: Convert the timestamp to a day once

In the inner dated query, CAST(train_at AS DATE) AS train_date turns '2026-04-04 03:00:00' into '2026-04-04'. Cast once, and the day you filter on is the day you report. Compute ABS(accuracy - 0.95) AS accuracy_gap in the same pass, so a model at 0.99 and one at 0.91 both sit 0.04 away.

Step 2: Fix the population before you band it

train_date BETWEEN '2026-01-01' AND '2026-04-04' compares dates to dates, so a run finished at any hour on freeze day is kept. A NULL train_at casts to a NULL train_date and fails the test. accuracy BETWEEN 0 AND 1 keeps only scored runs, because a NULL fails every comparison. Both filters have to act before the banding, since every tenth is measured against whatever survives.

Step 3: Band the survivors

The window function runs after the WHERE, so NTILE(10) OVER (ORDER BY accuracy_gap, model_id) splits only the in-window, scored runs. Ascending gap puts the nearest runs in tenth 1 and the farthest in tenth 10. Accuracy has two decimals, so equal gaps are common, and model_id decides which tenth a tied run lands in instead of engine row order.

The solution

SELECT
  model_id,
  mdl_name,
  train_date,
  accuracy,
  accuracy_gap,
  NTILE(10) OVER (ORDER BY accuracy_gap, model_id) AS tenth
FROM (
  SELECT
    model_id,
    mdl_name,
    CAST(train_at AS DATE) AS train_date,
    accuracy,
    ABS(accuracy - 0.95) AS accuracy_gap
  FROM ml_models
) AS dated
WHERE train_date BETWEEN '2026-01-01' AND '2026-04-04'
  AND accuracy BETWEEN 0 AND 1

Timestamp left as text

train_at BETWEEN '2026-01-01' AND '2026-04-04' compares '2026-04-04 03:00:00' to '2026-04-04' character by character. The longer string sorts after, so the row fails. Freeze-day runs are missing from the output, and every run behind them in the gap order moves toward a different tenth.

Timestamp cast to a date

CAST(train_at AS DATE) produces train_date, so every comparison happens at date grain. A freeze-day run finished at any hour counts, the population is whole, and each tenth is sized from the right count.

Common follow-up questions

Common follow-up questions

• If you only needed the runs in tenth 10, what simpler shapes would also work, and do they round the same way? (Probes whether a count-sized `LIMIT` becomes sufficient once labels are no longer required.)
• How would you band each `framework` separately, and what goes wrong with the values as stored? (Tests `PARTITION BY framework` and the dirty casing in `framework` ('TensorFlow' vs 'tensorflow').)
• Rewrite the tenth-10 cut with `PERCENTILE_CONT(0.9)` on the gap. When does it select different runs than the `NTILE` bucket? (Probes bucket semantics versus an interpolated threshold.)
• The table grows to a billion rows with an index on `train_at`. How do you keep freeze-day runs without casting in the filter? (Tests a half-open range as the sargable alternative to casting in the filter.)