First and Last Peak Accuracy Dates

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

L3

Asked in

Vesta Innovations

Problem

Every training run lands in ml_models with its accuracy, and we want to know when our best-ever accuracy was first reached and whether any run has matched it since. Show the earliest and latest training dates among the runs that hit that top score.

Peak accuracy. When it first hit and when it last did.

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

This is a filter-by-extremum question wearing a model-registry costume. Anyone can write MAX(accuracy). The trap is writing MIN(train_at) and MAX(train_at) right beside it and trusting that the dates belong to the peak runs. They don't: every aggregate in a flat SELECT scans all 200 rows on its own, so the dates you get back are the whole registry's range, reaching into 2022, and you tell the team the peak was hit years before it was. The other shortcut, sorting by accuracy and keeping the top row, returns one date and silently drops the half of the question that matters: whether the peak was ever matched again.

Two passes over one table

Step 1: Fix the peak once

The peak is one number over every run, not one per mdl_name: (SELECT MAX(accuracy) FROM ml_models). Nothing in it refers to the outer row, so the engine computes it a single time and treats it as a constant.

Step 2: Keep only the runs that hit it

Compare accuracy to that constant in WHERE. On this data four runs share the top score of 0.99, two trained in 2023 and two in 2026, and all four have to survive the filter: that spread is exactly what 'matched it since' is asking about.

Step 3: Read the span off the survivors

Now MIN(train_at) and MAX(train_at) only see the peak runs. train_at is 'YYYY-MM-DD HH:MM:SS' text, so string order is time order: the minimum is the first time the peak was reached, the maximum the latest time it was matched.

SELECT MIN(train_at) AS first_date, MAX(train_at) AS last_date FROM ml_models WHERE accuracy = (SELECT MAX(accuracy) FROM ml_models)

SELECT MAX(accuracy) AS peak_accuracy,
       MIN(train_at) AS first_date,
       MAX(train_at) AS last_date
FROM ml_models

Common follow-up questions

• Accuracy is now computed from raw metrics, and two 'equal' scores differ in the sixth decimal. How does your equality filter behave, and what would you change? (Tests awareness that exact float equality is fragile, and whether the candidate rounds or uses a tolerance deliberately.)
• The team wants this per `mdl_name`: each model family's peak with its first and last dates. What changes? (Tests moving from a global extremum to a per-group one, where the peak must be scoped to each family.)
• How would you also return how many runs hit the peak and the `model_id` of the first one? (Tests whether the candidate sees that the first run's other columns need a row-level pick, not just an aggregate.)