Deployments per Environment

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

L3

Asked in

ESPN

Problem

Staging has felt overloaded lately: is it really taking more deploys than production? From our deploy_logs, how many deploys has each environment received?

Dev, staging, prod. Where do most deploys land?

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 really is

This is a one-pass tally dressed up as an infrastructure complaint, and the only real decision is the grain. Anyone can write the group-and-count. What separates candidates is grouping on the label exactly as `deploy_logs` stores it. The log holds 'staging' and 'STAGING', 'production' and 'Production', and even a lone 'Dev' sitting next to 33 'dev'. Fold the case and you return 4 rows where the answer has 7, and you also bury the fact that something upstream writes inconsistent labels.

Step 1: Read the grain off the preview

The expected output lists 'Dev' with 1 and 'dev' with 33 as separate rows. That settles it: one row per stored env_name value, not one per cleaned-up environment.

Step 2: Count every logged deploy

Staging's load comes from every attempt, not only the ones that worked. COUNT(*) counts every row whatever its status, so failed, rolled back and in-progress deploys all land in the tally.

Step 3: Stop at the GROUP BY

Nothing needs filtering and no order is asked for, so the query ends once each label has its count.

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

Case-folded

Grouping on LOWER(env_name) returns 4 rows: staging 66, production 66, dev 34, canary 34. It looks tidy, and it is graded wrong.

As stored

Grouping on env_name returns 7 rows, including 'Dev' with 1 and 'STAGING' with 32. That split is the evidence that labels are drifting.

Common follow-up questions

• How would you answer the staging versus production question from this result? (Tests reading split labels: adding 'staging' to 'STAGING' and 'production' to 'Production' gives 66 against 66.)
• How would you show each environment's share of all deploys? (Tests a ratio over the grand total, such as `COUNT(*)` divided by `SUM(COUNT(*)) OVER ()`.)
• What if some deploys had a NULL `env_name`? (They form their own group; tests whether you label that group or exclude it.)
• Where would you fix the inconsistent labels for good? (Tests upstream thinking: normalize once at write time or in a staging model, not in every query.)