# The Tag Order > Tags arrived in chaos. The system needs them in line. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L3 · Asked in: Google ## Problem Deployment outcomes and environment names in `deploy_logs` were written by several tools that never agreed on capitalization, and the release team's failure review needs every one of them read under a single spelling. List each failed deployment with its log ID and its environment in lowercase. ## Tables ### deploy_logs - log_id INTEGER - svc_name TEXT - version TEXT - env_name TEXT - status TEXT - deploy_at TEXT - dur_secs INTEGER - author TEXT Sample rows (10 of 200): | log_id | svc_name | version | env_name | status | deploy_at | dur_secs | author | | --- | --- | --- | --- | --- | --- | --- | --- | | 131 | payment-api | v1.0.2 | staging | failed | 2026-02-02 01:11:00 | 23 | bob | | 162 | user-svc | v1.1.0 | dev | rolled_back | 2026-03-03 02:22:00 | 36 | charlie | | 193 | search-api | 2.0.0 | canary | in_progress | 2026-04-04 03:33:00 | 49 | dana | | 224 | gateway | v2.1.0 | Production | Success | 2026-05-05 04:44:00 | 62 | eve | | 255 | notif-svc | 2.1.1 | STAGING | FAILED | 2026-06-06 05:55:00 | 75 | frank | | 286 | analytics | v3.0.0-rc1 | production | success | 2026-07-07 06:06:00 | 88 | Alice | | 317 | ml-serving | v1.0.3 | staging | failed | 2026-08-08 07:17:00 | NULL | BOB | | 348 | auth-svc | v1.0.1 | dev | Rolled_Back | 2026-09-09 08:28:00 | 114 | alice | | 379 | Payment-API | v1.0.2 | canary | in_progress | 2026-01-10 09:39:00 | 127 | bob | | 410 | user-svc | v1.1.0 | Production | Success | 2026-02-11 10:50:00 | 140 | charlie | ## Expected output First 5 of 66 rows: | log_id | env_name | | --- | --- | | 131 | staging | | 255 | staging | | 317 | staging | | 441 | staging | | 503 | staging | ## Worked solution and explanation ### What this is really asking This is a case-folding problem wearing a reporting costume. Everyone reads "in lowercase" and wraps the output column, which is the easy half. The half that separates candidates is the filter: the outcome column was written by tools that disagree on capitalization, so 'failed' and 'FAILED' are the same event. Match it as typed and you hand the release team a failure review missing every deploy a loud tool logged. #### Step 1: Look at the values before the filter Scan the sample rows of `deploy_logs`. `status` carries 'failed' next to 'FAILED', and `env_name` carries 'production' next to 'Production'. The prompt told you the tools disagree on capitalization; the data shows you exactly where. #### Step 2: Fold the outcome where you compare it Write the predicate as `LOWER(status) = 'failed'`. The fold has to happen inside the comparison, because the comparison is where a mismatched spelling silently throws a row away. #### Step 3: Fold the environment where you report it Project `LOWER(env_name) AS env_name`. Keep the original column name so whoever reads the review sees the column they expect, now in one spelling. **Failed deployments, one spelling** ```sql SELECT log_id, LOWER(env_name) AS env_name FROM deploy_logs WHERE LOWER(status) = 'failed' ``` **Formatting only** `WHERE status = 'failed'` with `LOWER(env_name)` in the select. The output looks clean, but every 'FAILED' row is gone and nobody notices because nothing errors. **Matching and formatting** `WHERE LOWER(status) = 'failed'` with `LOWER(env_name)` in the select. Every failure is found, and every environment is reported once. > **Lowercase is a matching rule here** > > Candidates treat "in lowercase" as presentation and fix only the projection. The same inconsistency that dirties `env_name` dirties `status`, and on the filter side it costs rows instead of cosmetics. > **Asking which columns are dirty** > > The strong move is to ask, or check, whether the casing problem stops at the column you were told to clean. Spotting that `status` needs the same fold before you write the `WHERE` is the tell. > **A function on the column defeats a plain index** > > `LOWER(status)` cannot use an ordinary index on `status`. On a large log table, an expression index on `LOWER(status)` or a cleaned column written at load time keeps this a seek instead of a scan. > **Fix it once, upstream** > > In production this query is a patch. The durable fix is to normalize `status` and `env_name` when the deploy tools write them, so every downstream report stops paying for the disagreement. ## Common follow-up questions - How would you count failures per environment without double-booking 'Production' and 'production'? _(Grouping must happen on the folded value, not the raw `env_name`.)_ - What if `svc_name` also had stray spaces around some values? _(Casing is one kind of dirt; padding needs its own trim before matching.)_ - How would you stop new mixed-case outcomes from landing in `deploy_logs` at all? _(A check constraint or a write-time normalization step.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_tag_order) - [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.