Shipped to Prod

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

Shopify

Problem

The release manager wants to see how deployment volume splits across the pipeline. Count how many deployments landed in each environment, busiest environment first.

Every environment keeps its own tally. Read the funnel.

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

Strip the deployment-pipeline costume and this is a plain bucket-and-tally: one row per environment, with a headcount of deploys in each. Everyone gets the grouping. The thing that quietly separates people is which count they reach for and how they order the result. Pick the wrong count and you silently drop rows; forget the ordering and the release manager cannot read the funnel at a glance.

The trap: which COUNT do you write?

The instinct on a table like this is to count some column that feels representative, say COUNT(dur_secs). That is a trap. dur_secs is nullable (the ml-serving staging row has no duration), and COUNT(column) skips NULLs. Count that column and staging comes back short instead of matching its true deploy count. The question asks for deployment volume, meaning every logged row, so you want COUNT(*), which counts rows and never inspects a value.

SELECT env_name, COUNT(*) AS deploy_count
FROM deploy_logs
GROUP BY env_name
ORDER BY deploy_count DESC

Step 1: Collapse to one row per environment

GROUP BY env_name defines the grain of the answer. env_name has only four distinct values here, so you get exactly four output rows regardless of how many hundreds of thousands of deploy logs feed in.

Step 2: Tally every row in each bucket

COUNT(*) counts rows within each group. Because it never looks at a column value, in_progress, failed, and rolled_back deploys all count the same as a success, which is exactly what 'total deployment volume' means.

Step 3: Order so the funnel reads top-down

ORDER BY deploy_count DESC surfaces the busiest environment first. That is the whole point of the report: the release manager wants to see where volume concentrates without scanning the whole table.

COUNT(*)

Counts every row in the group. Answers 'how many deployments'.

COUNT(`dur_secs`)

Counts only rows where dur_secs is present, so any NULL-duration deploy is dropped. Answers a subtly different question and is wrong here.

Common follow-up questions

• How would you also show environments that had zero deployments in the window? (Tests whether they know GROUP BY only surfaces environments present in the table, and that zero-count rows need a LEFT JOIN from an environment dimension table.)
• Restrict the count to only successful deploys. What changes? (Checks that they add a WHERE status = 'success' filter and understand it answers a different question than total volume.)
• Two environments tie on count. How do you make the output deterministic? (Looks for a stable secondary sort such as ORDER BY `deploy_count` DESC, `env_name` ASC.)