Month With Fewest Deploys

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

Vesta Innovations

Problem

We're picking the safest month for a major migration, so we need the calendar month that has historically drawn the fewest deployment attempts across every year in deploy_logs, whatever each attempt's outcome. Return that month as an integer from 1 to 12 with its deploy count, taking the earlier month if two are equally quiet.

One month, nobody deployed.

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

Underneath the release-calendar story, this is an aggregation over a cyclic calendar part. The skill being probed: can you collapse a multi-year timestamp column into just twelve month-of-year buckets, sum across every year, and pull the single lowest? Anyone can write COUNT(*) with a GROUP BY. The trap is the grouping key. Group by the whole timestamp, by date(deploy_at), or by year-and-month, and every year splits into its own bucket, so you return the sparsest individual month in one year instead of the true cross-year minimum. Get that wrong and the team schedules a migration in a month that only looked quiet because you counted a single year.

Building it step by step

Step 1: Extract the month-of-year, discard the year

STRFTIME('%m', deploy_at) isolates the month-of-year and drops the year entirely, and CAST(... AS INTEGER) in the SELECT hands it back as a plain month number. This is the whole game: grouping on the month field alone turns 750k distinct timestamps into exactly 12 buckets. Any key that keeps the year (a full date, a year-month string) breaks the fold.

Step 2: Count every attempt per month

COUNT(*) per month counts every deploy attempt. There is no status or environment filter: failed, rolled_back, and in_progress rows are all real deploy activity the team cares about, and the mixed casing on those columns is a decoy that would only tempt you to filter and undercount.

Step 3: Order ascending, break ties, take one

ORDER BY deploy_count ASC puts the quietest month first, and LIMIT 1 takes it. Because two months can tie on the count, add month_num ASC as a secondary sort so the earlier month number wins a tie. Without that tiebreaker the single returned row is nondeterministic and a correct answer can disagree with the reference.

The solution

SELECT CAST(STRFTIME('%m', deploy_at) AS INTEGER) AS month_num,
    COUNT(*) AS deploy_count
FROM deploy_logs
GROUP BY STRFTIME('%m', deploy_at)
ORDER BY deploy_count ASC, month_num ASC
LIMIT 1

Common follow-up questions

• How would you return the fewest-deploy month for each environment rather than overall? (Tests whether the candidate can extend the cyclic-part idea to a two-column key without reintroducing the year.)
• If you counted `COUNT(dur_secs)` instead of `COUNT(*)`, how would the missing durations change your month totals? (Tests understanding that `COUNT(*)` counts rows regardless of NULLs, versus `COUNT(dur_secs)` which would drop them.)
• What happens to `STRFTIME('%m', deploy_at)` if some `deploy_at` values are stored in a non-ISO format, and how would you guard against it? (Tests awareness that `STRFTIME` depends on `deploy_at` being a parseable timestamp string.)