The Tag Order

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

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.

Tags arrived in chaos. The system needs them in line.

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

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.

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