# The Silent Probe > One probe never recorded. The rest still need reporting. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L4 ## Problem A data quality sweep of these service health checks flagged the latency column, where one probe never recorded a reading and left a gap. Report every field with latency as a whole number, leaving out any record whose latency cannot be converted. ## Worked solution and explanation ### What this problem is really about Strip the data-quality framing away and this is a cast with a landmine in it. Every latency reading converts to a whole number cleanly except one: the probe that never fired left a null behind. The tell is the phrase 'cannot be converted', because a bare CAST in SQLite does not fail loudly on that null. It quietly hands back a null and keeps going. So the whole problem hinges on one decision: do you drop the row you cannot convert, or do you let a null ride along inside a column you just promised was clean integers? Miss it and you ship a 'cleaned' column that still has a hole in it. > **Trick to solving** > > The requirement 'leaving out any record whose latency cannot be converted' points straight at the single null in the latency column. Filter it out first, then cast what remains. The habit of thinking 'remove the unconvertible, then convert' is what keeps you safe on the day the cast really can throw. --- ### Walking through it #### Step 1: Find the row that cannot convert Scan the sample rows and the latency column tells the story: nine numeric readings and one null, where cache-svc never recorded. That null is the record the prompt calls unconvertible, so WHERE latency IS NOT NULL removes it. #### Step 2: Cast the survivors to whole numbers CAST(latency AS INTEGER) truncates each surviving reading toward zero, so 3.6 becomes 3 and 31.5 becomes 31. The expected preview shows the truncated values rather than rounded ones, which confirms plain CAST is what the task wants. --- ### The solution **Filter the null, then cast** ```sql SELECT check_id, svc_name, status, CAST(latency AS INTEGER) AS latency, uptime, checked, region FROM svc_health WHERE latency IS NOT NULL ``` > **Cost analysis** > > The table is 20M rows partitioned by checked. This is a full scan with a cheap IS NOT NULL predicate and a per-row cast. No index helps, because every surviving row is returned. The cast itself is nearly free; the cost is the scan you cannot avoid. > **Interviewers watch for** > > Whether you handle the unconvertible row at all. A candidate who casts blindly and returns a null latency inside a 'cleaned' result has missed the point. The senior move is to name the null out loud and decide, deliberately, to drop it. > **Common pitfall** > > Assuming CAST will reject bad input the way a strict engine would. In SQLite, CAST(NULL AS INTEGER) is just NULL and CAST of a non-numeric string silently yields 0. If you lean on the cast to throw out bad rows, the bad rows sail straight through. Filter them yourself. --- ## Common follow-up questions - How would the query change if latency were stored as text like '3.14' or 'timeout'? _(Tests knowing that in SQLite a non-numeric string casts to 0 rather than erroring, so an explicit validity check is needed before the cast.)_ - Would you round the readings instead of truncating them? _(Truncation versus ROUND(latency) is a real product decision; the candidate should ask which behavior the downstream consumer expects.)_ - How would you report which records got dropped? _(Negating the predicate to WHERE latency IS NULL surfaces exactly the excluded records for an audit trail.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_silent_probe) - [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.