Unreviewed Models

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

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

A machine-learning platform team keeps a registry of models in ml_models beside a log of data-quality checks in dq_checks, where a check that fails on a given day flags bad data landing that day. Among the models with a recorded training date, find the ones that trained on a day when no check was failing, along with each model's version, framework, and status.

Models that have never been evaluated.

Tables

ml_models

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

dq_checks

Columns: check_id INTEGER, tbl_name TEXT, col_name TEXT, rule TEXT, passed INTEGER, fail_pct REAL, run_at TEXT, severity TEXT

Expected output

Worked solution and explanation

This is an anti-join hiding under a data-governance story: for each model you have to prove a negative, that no data-quality check was failing on the day it trained. A plain filter cannot express absence, and an inner join keeps the exact opposite set: the models that DID coincide with a failure. The move is to LEFT JOIN the checks on, then keep only the rows where nothing attached. The trap is the join key. train_at and run_at are both timestamps, so comparing them raw asks 'trained at the exact second a check ran', not 'trained the same day'. Get that wrong and you flag the wrong models and let every model with a missing training date slip straight through.

What you're really being asked

Flip the question around. For every model with a known training date, was there any failing check that day? If yes, it trained on a dirty day and is out. If no, it had a clean run and belongs in the answer. That 'no failing check that day' is an absence, and absence in SQL is an anti-join, not a WHERE.

How to build it

Step 1: Join the checks onto each model by day

LEFT JOIN dq_checks d onto ml_models m, matching date(m.train_at) = date(d.run_at). Truncating both timestamps to their date is what turns 'same instant' into 'same day': a check that ran at 09:00 still counts against a model trained at 02:00 that morning.

Step 2: Attach only the failing checks

Add AND d.passed = 0 to the ON clause. A passing check that day is harmless, so only failures should attach. Putting d.passed = 0 in the WHERE instead filters on a column that is NULL for every unmatched row, quietly collapsing the LEFT JOIN back into an inner join and deleting the very clean-day models you are hunting.

Step 3: Keep the rows that found nothing

WHERE d.check_id IS NULL keeps the models where no failing check attached: that is the anti-join. Then AND m.train_at IS NOT NULL drops models with no recorded training day, because a NULL training date never matches any run_at and would otherwise survive as a false clean run.

passed = 0 in the WHERE

... WHERE d.check_id IS NULL AND d.passed = 0: d.passed is NULL for every unmatched row, so this keeps nothing. The outer join is silently undone into an inner join.

passed = 0 in the ON

... ON date(m.train_at) = date(d.run_at) AND d.passed = 0 ... WHERE d.check_id IS NULL: failures attach, everything else stays NULL, and the anti-join returns the clean-day models.

SELECT m.mdl_name, m.version, m.framework, m.status
FROM ml_models m
LEFT JOIN dq_checks d
  ON date(m.train_at) = date(d.run_at)
 AND d.passed = 0
WHERE d.check_id IS NULL
  AND m.train_at IS NOT NULL

Common follow-up questions

• What if a check recorded a window rather than a single `run_at`: how would you match a training run that fell inside it? (Moves from an equality join to a range/`BETWEEN` join.)
• How would you also return, for each clean model, proof there was a passing check that day? (Turns the anti-join back into a reporting inner join on `d.passed = 1`.)
• If `train_at` and `run_at` were stored in different time zones, what breaks? (Tests awareness that `date()` truncation is time-zone sensitive.)