# First and Last Peak Accuracy Dates

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

Canonical URL: <https://datadriven.io/problems/first_and_last_peak_accuracy_dates>

Domain: SQL · Difficulty: medium · Seniority: L3 · Asked in: Vesta Innovations

## Problem

The ML team wants to know when peak model performance was first achieved and whether it has been replicated since. Find the highest accuracy score across all models, then show the earliest and latest training dates where that accuracy was hit.

## Tables

### ml_models

- model_id INTEGER
- mdl_name TEXT
- version TEXT
- accuracy REAL
- status TEXT
- train_at TEXT
- framework TEXT

Sample rows (10 of 200):

| model_id | mdl_name | version | accuracy | status | train_at | framework |
| --- | --- | --- | --- | --- | --- | --- |
| 123 | rec_engine | v1.1 | 0.72 | training | 2026-02-02 01:00:00 | TensorFlow |
| 146 | churn_pred | v2.0 | 0.89 | archived | 2026-03-03 02:00:00 | XGBoost |
| 169 | spam_filter | 3.0 | 0.61 | failed | 2026-04-04 03:00:00 | sklearn |
| 192 | img_classify | v2.1-beta | 0.78 | validating | 2026-05-05 04:00:00 | pytorch |
| 215 | sentiment | 1.0.0 | 0.95 | Deployed | 2026-06-06 05:00:00 | tensorflow |
| 238 | demand_fcst | v1.0 | 0.67 | deployed | NULL | PyTorch |
| 261 | price_opt | v1.1 | 0.84 | Training | 2026-08-08 07:00:00 | TensorFlow |
| 284 | fraud_detect | v2.0 | NULL | archived | 2026-09-09 08:00:00 | XGBoost |
| 307 | rec_engine | 3.0 | 0.73 | failed | 2026-01-10 09:00:00 | sklearn |
| 330 | churn_pred | v2.1-beta | 0.9 | validating | 2026-02-11 10:00:00 | pytorch |

## Expected output

First 1 of 1 rows:

| first_date | last_date |
| --- | --- |
| 2023-01-10 13:00:00 | 2026-02-27 10:00:00 |

## Worked solution and explanation

### Why this problem exists in real interviews

Interviewers use the `ml_models` table here to probe row numbering within partitions combined with nested subqueries. The columns `mdl_name`, `version`, `accuracy` force candidates to reason about the correct grain before writing any aggregation.

---

### Break down the requirements

#### Step 1: Partition by `mdl_name`

Compute the highest accuracy across all models with a scalar subquery: (SELECT MAX(accuracy) FROM `ml_models`).

#### Step 2: Filter to rank 1

Filter `ml_models` to only the rows whose accuracy equals that peak value.

---

### The solution

**Row-number for first last peak accuracy**

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

> **Cost Analysis**
>
> Window function sorts within each `mdl_name` partition. An index on `(mdl_name, train_at)` avoids a full sort.

> **Interviewers Watch For**
>
> The interviewer checks whether you use `ROW_NUMBER` (one row) vs. RANK/`DENSE_RANK` (ties) based on the prompt requirements.

> **Common Pitfall**
>
> Using GROUP BY with MIN(`train_at`) gives the value but not the other columns. `ROW_NUMBER` gives the full row.

---

## Common follow-up questions

- The `accuracy` column in `ml_models` has roughly 3% NULLs. How does your query handle those rows, and would the result change if NULLs were replaced with zeros? _(Tests whether the candidate understands how NULLs propagate through aggregation functions and whether their WHERE/JOIN conditions implicitly filter them out.)_
- Your window function uses a default frame. What is the implicit frame, and would switching to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW change anything? _(Tests knowledge of default window frames (RANGE vs ROWS) and when the distinction matters.)_
- The `mdl_name` column in `ml_models` has a zipf distribution, meaning a few values dominate. How does that skew affect your query plan and parallelism? _(Tests understanding of data skew: the optimizer may choose a bad plan when histogram statistics are stale.)_
- Could you express this same logic as a single query without CTEs or subqueries? What readability trade-off does that introduce? _(Tests whether the candidate can flatten nested logic and understands when decomposition aids maintainability.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/first_and_last_peak_accuracy_dates)
- [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.