# Shipped to Prod > Every environment keeps its own tally. Read the funnel. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## 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. ## 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. > **COUNT(nullable_col) undercounts** > > COUNT(dur_secs) ignores rows where dur_secs IS NULL. On this data that drops any deploy whose duration was never recorded. Whenever the ask is 'how many rows', reach for COUNT(*), not COUNT of an attribute that might be missing. **Deploys per environment, busiest first** ```sql SELECT env_name, COUNT(*) AS deploy_count FROM deploy_logs GROUP BY env_name ORDER BY deploy_count DESC ``` *GROUP BY collapses to one row per environment; COUNT(*) tallies every deploy; ORDER BY deploy_count DESC puts the busiest environment on top.* #### 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. > **The tell they listen for** > > Reaching for COUNT(*) without prompting, and being able to say why it differs from COUNT(col), signals you have been burned by NULL semantics in production. Bonus points for asking whether env_name casing is clean before you trust the grouping. > **Why this stays cheap at 400K rows** > > This is a single sequential scan with a hash aggregate over four groups, then a tiny four-row sort. There is no join and no subquery. Even at 400K rows the engine touches each row once and the sort is negligible, so an index on env_name buys almost nothing here. > **In production, env_name is rarely clean** > > Real deploy logs pick up 'production', 'Production', and 'prod' from different tooling. A raw GROUP BY splits one environment across several buckets. When the data is dirty, group on LOWER(env_name) or a normalized alias so the tally is honest. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/shipped_to_prod) - [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.