Both Ends of the Pipe

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

Asked in

TripAdvisor

Problem

A release manager is auditing deploy_logs, where environment and author names were typed with inconsistent casing: 'Production' and 'production' are one environment, and 'Alice' and 'alice' are one author. List, alphabetically, each author who has deployed to at least two of the three standing environments, dev, staging and production, along with how many of them they reached.

Dev and prod, both touched by the same hand. Who are they?

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 distinct count over two dirty keys dressed up as a release audit. Anyone can filter to dev, staging and production and count. What separates candidates is noticing that the casing mess sits on both sides of the grouping. Every author who reached production has rows under both 'production' and 'Production', and 'alice' also deploys as 'Alice'. If you fold only one of the two keys, you either count production twice or report one person as two rows. Either way the at-least-two test ends up judging the wrong numbers.

Step 1: Fold the environment once and use that fold everywhere

Compare UPPER(env_name) against the upper-case literals 'DEV', 'STAGING' and 'PRODUCTION'. That keeps the 'Production', 'STAGING' and 'Dev' rows and drops canary. The same UPPER(env_name) has to go inside the count, or 'production' and 'Production' come back as two environments.

Step 2: Fold the author before grouping

GROUP BY LOWER(author) merges 'Alice' and 'alice' into one person. Select the same LOWER(author) so the name you report matches the grain you grouped at. Grouping on the raw author reports one person as two rows, each judged on only part of their history.

Step 3: Count stages, then test the count

COUNT(DISTINCT UPPER(env_name)) is how many of the three stages an author reached, and HAVING COUNT(DISTINCT UPPER(env_name)) >= 2 keeps the authors who span at least two. 'bob', 'dana' and 'frank' only ever deployed to staging, so they arrive with 1 and drop out.

SELECT LOWER(author) AS author,
       COUNT(DISTINCT UPPER(env_name)) AS env_count
FROM deploy_logs
WHERE UPPER(env_name) IN ('DEV', 'STAGING', 'PRODUCTION')
GROUP BY LOWER(author)
HAVING COUNT(DISTINCT UPPER(env_name)) >= 2
ORDER BY author;

Half-folded

WHERE UPPER(env_name) IN (...) with COUNT(DISTINCT env_name) and GROUP BY author. 'alice' and 'Alice' arrive as separate rows, and each counts 'production' and 'Production' as different environments, so they report 3 and 4 environments out of a possible three.

Folded on both keys

UPPER(env_name) in both the filter and the count, and LOWER(author) in both the select and the grouping. Three authors come back, each with 2, and 'bob', 'dana' and 'frank' are filtered out by the count.

Common follow-up questions

Common follow-up questions

• What if `env_name` also carries stray whitespace from a CSV ingest? (Wrap the fold in `TRIM`, and ideally clean the value at ingest so downstream queries stop paying for it.)
• Now require dev and production specifically, not any two stages? (A count of stages cannot tell you which two were reached; each required stage has to be checked on its own.)
• How would you list authors who never made it past staging? (Same grain, opposite test: keep the authors whose folded environments never include 'PRODUCTION'.)