Deploy Count by Service

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

Asana

Problem

Every deployment attempt counts here, whether it succeeded, failed, rolled back or is still in progress. An engineer scoping a deployment automation project needs each service's total deployment count from deploy_logs, most frequently deployed first.

Some services deploy constantly. Others barely at all.

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

Strip off the automation-project framing and this is a per-service tally with the sort pointed at the tally, not at the name. Everyone writes COUNT(*). What separates answers is what they quietly add or leave out: a WHERE status = 'success' that throws away every failed and rolled-back attempt, or an ORDER BY svc_name that lists services alphabetically. Either one and the busiest services stop sitting at the top, so the automation work starts in the wrong place.

Step 1: Count every attempt, not every success

status mixes 'failed', 'rolled_back', 'in_progress' and a few case variants. Each row is still a deployment someone ran, so nothing gets filtered. COUNT(*) counts rows, which is exactly the unit the question is about.

Step 2: Collapse to one row per svc_name

GROUP BY svc_name turns 200 log rows into one row per service name. Without it, COUNT(*) next to svc_name either errors or folds the whole table into one total. SELECT DISTINCT svc_name gives you the names with no tally at all.

Step 3: Sort by the tally, biggest first

ORDER BY deploy_count DESC sorts on the alias. 'gateway' and 'search-api' both land on 26, and the question says nothing about which comes first. Either order is correct, so no tiebreaker is owed.

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

Common follow-up questions

• `payment-api` appears in `svc_name` under four spellings, two of them padded with spaces. How would you fold them into one service, and what does that do to the top of the list? (Normalizing a dirty grouping key before counting.)
• How would you return only the top 5 services, and what if the fifth and sixth tie? (A `LIMIT` that cuts between tied rows needs a stated tie rule.)
• If you also had a service catalog table, how would you include services with zero deployments? (An outer join from the catalog, counting a column that is `NULL` when there is no match.)
• How would you show each service's deployment count per month using `deploy_at`? (Adding a truncated date to the grain.)