After the Cutoff

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

Microsoft

Problem

deploy_logs records one row per deploy across several years, with the environment it shipped to, when it ran and how long it took. Only rows whose environment reads exactly 'production', in lowercase, are live deploys. For those that ran in April through December of any year, show each service with its number of deploys and its longest deployment duration, busiest services first.

The calendar turned but prod kept moving. See which services never stopped shipping.

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

This is a recurring calendar window dressed up as a deployment audit. The skill being probed: can you select April through December in every year of a log that spans five years, rather than one stretch of dates starting at a single April? Anyone can write COUNT(*) and MAX(dur_secs). What separates people is 'any year'. Compare deploy_at to a fixed date and you keep either one year alone or one year's late months plus everything after it. Either way your counts, your longest durations and the order of the services all shift.

The trap

The log covers five calendar years. 'April through December of any year' means months 4 to 12 inside each of them, not a continuous window starting from one April. A filter like deploy_at >= '2026-04-01' keeps only the latest year's late months and quietly drops the four earlier years. deploy_at >= '2022-04-01' swings the other way and keeps every January to March after that first spring.

Building the query

Step 1: Keep only the deploys that qualify

Two conditions go in the WHERE: env_name = 'production' for the environment, and the month of deploy_at at 4 or higher. strftime('%m', deploy_at) returns text such as '04', so cast it to an integer before comparing. Left as text, it never compares as the number you meant, and every month slips through.

Step 2: Roll the survivors up per service

GROUP BY svc_name collapses each service to one row. COUNT(*) counts every qualifying deploy, including the ones with no recorded duration. MAX(dur_secs) skips those NULL durations, so a missing value never wins.

Step 3: Put the busiest services on top

ORDER BY deploy_count DESC is the whole ordering the question asks for. Services that tie on count may come back in either order. Nothing in the ask separates them, so a second sort key would answer a question nobody posed.

SELECT svc_name,
       COUNT(*) AS deploy_count,
       MAX(dur_secs) AS max_duration
FROM deploy_logs
WHERE env_name = 'production'
  AND CAST(strftime('%m', deploy_at) AS INTEGER) >= 4
GROUP BY svc_name
ORDER BY deploy_count DESC

Pins a single year

WHERE deploy_at >= '2026-04-01' looks reasonable, but it hard-codes one year and drops every other year's April to December deploys. On a multi-year log it silently undercounts.

Tests the month alone

CAST(strftime('%m', deploy_at) AS INTEGER) >= 4 keeps months 4 through 12 in every year, which is exactly what 'any year' asks for.

Common follow-up questions

• If the log grew to cover ten years, does your filter still capture April onward in each of them? (Tests whether the month-based window stays correct as the data crosses more year boundaries.)
• Some deploys have a NULL `dur_secs`. How does that affect your count versus your maximum duration? (Probes NULL handling in aggregates on `dur_secs`.)
• How would you also list services that deployed to production but had nothing from April onward, showing a count of zero? (Tests keeping services that qualify on environment but have no deploys in the window.)