Find Deploy Authors

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

L3

Asked in

Google

Problem

Which spellings of alice and bob appear in deploy_logs, counting every capitalization of either name? We're cleaning up the author field and need each variant the logs carry, exactly as written.

Same person. Many different spellings.

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 data-quality audit wearing a lookup costume. Anyone can filter a name. The skill is folding case in exactly one place: inside the comparison, never in what you return. Match with = and you only find 'alice' and 'bob'. Fold the output too and 'Alice' merges into 'alice', which hides the exact inconsistency the cleanup exists to surface.

Break down the requirements

Step 1: Match case-insensitively

WHERE LOWER(author) IN ('alice', 'bob') catches 'Alice', 'BOB' and any other casing. Keep the literals lowercase so they meet the folded column on equal terms.

Step 2: Return the raw spelling

Select author itself, not LOWER(author). The cleanup wants to see 'Alice' and 'alice' as separate rows, because that difference is the problem being fixed.

Step 3: Collapse repeat deploys

deploy_logs holds 500,000 rows and each person ships many deploys, so DISTINCT turns hundreds of matching rows into one row per spelling.

The solution

SELECT DISTINCT author
FROM deploy_logs
WHERE LOWER(author) IN ('alice', 'bob')

Folded output

SELECT DISTINCT LOWER(author) returns 'alice' and 'bob': two tidy rows that tell the cleanup nothing is wrong.

Folded predicate only

SELECT DISTINCT author ... WHERE LOWER(author) IN (...) returns every stored casing, which is the list the cleanup actually has to fix.

Common follow-up questions

• How would you make this lookup index-friendly on a hot path? (An expression index on `LOWER(author)` lets the planner seek instead of scan.)
• How would you report how many deploys each spelling accounts for? (Add `COUNT(*)` and `GROUP BY author`; the `LOWER()` filter stays in the `WHERE`.)
• What if you needed every author whose name starts with 'al'? (`LOWER(author) LIKE 'al%'` can still use an expression index because the wildcard trails.)