# The Elite > The top-scoring models. What's their average? Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The ML team is deciding whether to retrain the highest-accuracy models or leave them alone. Accuracy is stored as a decimal between 0 and 1. Compute the average accuracy across all models whose accuracy, when expressed as a percentage, falls between 91 and 100 inclusive. ## Worked solution and explanation ### What this really is This is a single-column range filter wearing an ML-evaluation costume. The only real skill being probed: can you apply a range check to a metric that is stored on one scale but described on another? Accuracy lives as a 0-to-1 decimal, but the ask is phrased in percentage points, 91 to 100. The candidates who stumble compare the raw decimal against 91 and 100, so nothing ever clears the bar and the query returns NULL. Scale first, then filter, then average. Miss the scaling and you confidently hand back an empty result. --- ### Building the query #### Step 1: Filter to the high-accuracy band Accuracy is a 0-to-1 decimal, so multiply it by 100 to put it on the percentage scale the prompt speaks in, then keep only the rows where that value sits in the 91-to-100 band. BETWEEN is inclusive on both ends, which matches the 'inclusive' wording exactly. NULL accuracy rows fail this predicate silently, so they drop out here with no extra handling. #### Step 2: Average the survivors Apply AVG(accuracy) over the surviving rows. Note you average the original decimal accuracy, not the scaled value; the scaling was only a means to express the filter. AVG ignores any NULLs by definition, so the average is over genuine measured models. #### Step 3: Name the result Alias the aggregate as avg_accuracy so the single scalar comes back under a stable, readable name instead of a raw expression string. --- ### The solution **Average accuracy of the top band** ```sql SELECT AVG(accuracy) AS avg_accuracy FROM ml_models WHERE accuracy * 100 BETWEEN 91 AND 100 ``` > **The move that cracks it** > > The whole problem hinges on one line: accuracy * 100 BETWEEN 91 AND 100. Transform the metric onto the scale the requirement is written in, then compare. Comparing the raw 0-to-1 decimal against 91 matches nothing. > **Common pitfall** > > Writing WHERE accuracy BETWEEN 91 AND 100 against the raw decimal returns zero rows, and AVG over zero rows yields NULL rather than an error, so the query looks like it ran fine while being completely wrong. Always sanity-check that a filtered aggregate actually matched something. > **Interviewers watch for** > > Rows with NULL accuracy are handled twice over: they fail the BETWEEN predicate, and AVG would ignore them anyway. That is why no COALESCE or explicit IS NOT NULL guard is needed here. Knowing that both the filter and the aggregate drop NULLs, rather than adding a redundant guard, is the tell of someone comfortable with SQL's three-valued logic. --- ## Common follow-up questions - The accuracy column has roughly 2 percent NULLs. Where do those rows go in your query, and how would the answer change if NULL accuracy were treated as 0? _(Tests whether the candidate understands that both the range predicate and AVG already exclude NULLs, and how forcing them to zero would drag the average down.)_ - A model has accuracy exactly 0.91. Does it make the cut, and how would you rewrite the filter if the band were meant to be strictly above 91? _(Tests understanding of boundary inclusivity and how BETWEEN differs from strict comparisons.)_ - The team now wants this average broken out by framework instead of one overall number. How does the query change? _(Tests whether the candidate can extend a single scalar into a grouped breakdown while preserving the same filter.)_ - If accuracy were indexed, would accuracy * 100 BETWEEN 91 AND 100 use that index, and how might you rewrite the predicate so it can? _(Tests awareness of index usability: wrapping a column in an expression can prevent an index on accuracy from being used.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_elite) - [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.