The Silent Probe

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

L4

Asked in

Yelp

Problem

Some probes in svc_health never recorded a latency reading, so that column has gaps where a value should be. An on-call engineer needs every field of every health check reported with latency as a whole number, leaving out any record whose latency cannot be converted.

One probe never recorded. The rest still need reporting.

Tables

svc_health

Columns: check_id INTEGER, svc_name TEXT, status TEXT, latency REAL, uptime REAL, checked TEXT, region TEXT

Expected output

Worked solution and explanation

What this problem is really about

Strip the data-quality framing away and this is a type conversion with a landmine in it. Most latency readings convert to a whole number cleanly, but 23 of the 200 checks carry a NULL where a probe never recorded anything. The tell is the phrase 'cannot be converted', because a bare CAST does not fail loudly on a NULL. It quietly hands back a NULL and keeps going. So the whole problem hinges on one decision: do you drop the rows you cannot convert, or let a NULL ride along inside a column you just promised was clean whole numbers? Miss it and you ship 200 rows where 177 belong, with holes in the 'cleaned' column.

Walking through it

Step 1: Find the rows that cannot convert

The sample rows already show one: 'cache-svc' has an empty latency where every other check has a decimal. That is the record the prompt calls unconvertible, and it is not alone in the full table. WHERE latency IS NOT NULL removes every one of them, not just a single id you happened to spot.

Step 2: Cast the survivors with CAST(latency AS INTEGER)

The cast truncates each surviving reading toward zero, so 3.6 becomes 3 and 31.5 becomes 31. The expected preview shows 3 for 3.6, not 4, which tells you plain truncation is wanted and rounding would grade wrong.

Step 3: Pass the other six columns through untouched

Every field is reported, so check_id, svc_name, status, uptime, checked and region come through as stored. The cast keeps the name latency, so the output reads like the source table. The mixed-case status values and the missing uptime readings stay as they are: cleaning those is not this sweep's job.

The solution

SELECT
    check_id,
    svc_name,
    status,
    CAST(latency AS INTEGER) AS latency,
    uptime,
    checked,
    region
FROM svc_health
WHERE latency IS NOT NULL

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.)