Buried Digits

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

Domain

SQL

Difficulty

medium

Seniority

L4

Problem

Only deploys whose environment is stored exactly as the lowercase 'staging' belong in this comparison; the uppercase 'STAGING' rows in deploy_logs came from a retired pipeline and stay out. We want each of those deploys' version as a plain number, so drop the leading 'v' where there is one and the dots between the parts ('v1.0.2' becomes 102), and return it as an integer beside the service name.

The version number is buried in the log.

Tables

deploy_logs

Columns: log_id INTEGER, svc_name TEXT, version TEXT, env_name TEXT, status TEXT, deploy_at TEXT, dur_secs INTEGER, author TEXT

Expected output

Worked solution and explanation

What this is really asking

This is a filter-then-normalize problem where the filter is the trap. Turning 'v1.0.2' into 102 takes two REPLACE calls and a CAST, and almost everyone gets there. What separates candidates is the env_name predicate: the column holds both 'staging' and 'STAGING', and only the lowercase rows belong in the comparison. Fold case out of tidiness and the 32 'STAGING' rows ride along. The result grows from 34 rows to 66, and every number in it still looks plausible, so nothing tells you it is wrong.

Break down the requirements

Step 1: Keep only lowercase 'staging'

Compare env_name = 'staging' as it is. Text equality is case-sensitive here, which is exactly what the business rule wants: 'STAGING' came from a retired pipeline and stays out. Any LOWER() or ILIKE lets those rows back in.

Step 2: Strip every non-digit before converting

The staging versions are 'v1.0.2', 'v1.0.3', '2.0.0' and '2.1.1'. One REPLACE removes the 'v' and a second removes the dots. The 'v' pass does nothing on the bare versions, which is fine: it costs nothing and keeps one expression for every row.

Step 3: Cast last

Only once the string is pure digits does CAST(... AS INTEGER) give 102 rather than a truncated 1 or a zero. Name the result version_num so the output reads as the answer.

The solution

SELECT svc_name,
       CAST(REPLACE(REPLACE(version, 'v', ''), '.', '') AS INTEGER) AS version_num
FROM deploy_logs
WHERE env_name = 'staging'

Common follow-up questions

Common follow-up questions

• What breaks if a staging deploy arrived with version 'v3.0.0-rc1'? (The two `REPLACE` calls leave '300-rc1'. SQLite's cast reads 300 and other engines raise an error, so you would strip the pre-release suffix or reject the row explicitly.)
• How would you make this robust across formats and across engines? (On an engine that has it, `REGEXP_REPLACE(version, '[^0-9]', '')` strips any non-digit. Without it, you extend the `REPLACE` chain or split the string and cast each part.)
• How would you list each service's versions newest first? (Split on '.' into major, minor and patch integers and sort by all three, never by `version_num`.)