# Buried Digits

> The version number is buried in the log.

Canonical URL: <https://datadriven.io/problems/buried_digits>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The release team wants to compare deploy versions as plain numbers, but each log records the version with a leading 'v' and dots or dashes between the parts, like 'v1.2-3'. The environment field was typed with inconsistent capitalization, and only the deploys whose environment is stored as the exact lowercase word 'staging' should count, so 'STAGING' and 'Staging' fall outside the set. For those rows, drop the 'v' and the dot and dash separators so the digits run together, and return that value as an integer beside the service name.

## Worked solution and explanation

### What this is really asking

Strip away the costume and this is a case-sensitivity trap wearing a string-cleaning problem. Anybody can nest three REPLACE calls to peel 'v', '.', and '-' off the version and cast what is left to an integer. The real tell is the env_name filter: the column is deliberately littered with 'staging', 'STAGING', and 'Staging', and the ask is the literal lowercase value only. Reach for LOWER() out of tidiness and you quietly pull in the uppercase rows and overshoot the count; the grader compares env_name = 'staging' exactly.

---

### Break down the requirements

#### Step 1: Strip the known noise

Nest three REPLACE calls. Each one removes one fixed character ('v', then '.', then '-'). Order does not matter here because the three sets do not overlap.

#### Step 2: Cast and filter

Wrap the stripped string in CAST(... AS INTEGER) so version_num is numeric and orderable. Filter env_name = 'staging' in WHERE, and resist folding case, so the mixed-capitalization rows stay out.

---

### The solution

**The graded query**

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

> **Cost Analysis**
>
> Single scan of deploy_logs (600k rows) with a WHERE on env_name. An index on env_name would prune to staging cheaply; the three REPLACE calls are O(length) per row and trivial at this scale.

> **Interviewers Watch For**
>
> The env_name column is recorded with inconsistent casing on purpose: 'staging', 'STAGING', 'Staging' all appear. The grading filter compares env_name = 'staging' exactly, so folding case with LOWER() pulls in extra rows and overshoots the expected count. Read the expected preview row count and you can tell whether case folding was intended.

> **Portability Note**
>
> Reaching for REGEXP_REPLACE to strip non-digits is the instinct, but plenty of engines (SQLite, older MySQL) do not ship it, and the cast then dies on the missing function. When the noise is a small fixed set of characters, nested REPLACE is the portable move that always compiles.

> **Common Pitfall**
>
> Treating the result as a real version comparator. '1.2-3' becomes 123 and '1.23' also becomes 123. The numeric form collapses semantically distinct versions; fine for sort within one service, dangerous across services.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- What breaks if a version string contains letters other than 'v', like 'v1.2-rc3'? _(REPLACE only strips 'v', so 'rc' stays. CAST AS INTEGER then fails or returns 0 depending on the engine.)_
- 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, not just the three known characters; without it, you extend the nested REPLACE chain or split and re-cast.)_
- Why is 1.2.10 a problem for numeric comparison? _(It collapses to 1210, which sorts below 1.3.0 (130). Lexicographic-by-component compare or split-and-cast is needed for correct semver ordering.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/buried_digits)
- [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.