Best Accuracy to Training Time Ratio

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

Problem

A model's training time is its training timestamp measured in days since the Unix epoch, fractions of a day included, and a model missing either that timestamp or an accuracy score cannot be measured at all. From ml_models, find the three models delivering the least accuracy per million days of training time, lowest first, showing each one's name, accuracy, training timestamp, training days and that rate.

Fast to train. Accurate too. Which model?

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 is

This is a type-coercion problem wearing a model-quality costume. The sort is trivial; the denominator is not. train_at is text, and STRFTIME('%s', train_at) hands back text too, so dividing it by 86400 quietly becomes integer division and every training time snaps to a whole day. The second trap is quieter still: the list runs lowest first, and a NULL rate sorts ahead of every real one. Skip the null filter and your bottom three are models nobody ever scored.

Step 1: Drop the rows that cannot be measured

Filter accuracy IS NOT NULL AND train_at IS NOT NULL. Either NULL makes the rate NULL, and an ascending sort hands those rows the first slots. Both conditions matter on this data: the seed has rows missing each one.

Step 2: Turn train_at into fractional days

STRFTIME('%s', train_at) gives epoch seconds as text. Wrap it in CAST(... AS FLOAT) before dividing by 86400, so a model trained at 09:00 carries its 0.375 of a day instead of losing it.

Step 3: Use the same denominator in the rate

A SELECT cannot reference its own training_days alias, so the rate repeats the cast expression: 1000000 * accuracy / (CAST(...) / 86400). Keep the cast in both places; drop it from either and that column silently goes back to whole days. Scaling by a million keeps the values readable at four decimals.

Step 4: Lowest first, keep three

Sort on accuracy_per_million_days ascending and cut with LIMIT 3. The rates are distinct on the seed, so no tiebreaker is owed at the cut.

SELECT
  mdl_name,
  accuracy,
  train_at,
  CAST(STRFTIME('%s', train_at) AS FLOAT) / 86400 AS training_days,
  ROUND(1000000 * accuracy / (CAST(STRFTIME('%s', train_at) AS FLOAT) / 86400), 4) AS accuracy_per_million_days
FROM ml_models
WHERE
  accuracy IS NOT NULL
  AND train_at IS NOT NULL
ORDER BY
  accuracy_per_million_days ASC
LIMIT 3

Raw division

STRFTIME('%s', train_at) / 86400 coerces the text to an integer and divides integer by integer. A model trained at '2026-01-10 09:00:00' lands on day 20463 flat, and every rate shifts.

Cast first

CAST(STRFTIME('%s', train_at) AS FLOAT) / 86400 keeps the fraction: day 20463.375. The query runs either way, which is exactly why the wrong one survives review.

Common follow-up questions

• What changes if a real `training_duration_seconds` column existed? (Whether you see the epoch denominator as a proxy and would swap in the true duration.)
• Return the three weakest models per `framework` instead of overall. (Moving from a global `LIMIT` to a per-group selection.)
• Two models tie exactly at the third slot. Which one should win? (When a cut through tied rows needs a stated tie rule and an extra sort key.)
• Why might `STRFTIME('%s', train_at)` return `NULL` on a non-null `train_at`? (Malformed timestamp strings silently fall out of the list.)