# After the Cutoff > The calendar turned but prod kept moving. See which services never stopped shipping. Canonical URL: 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. ## Tables ### deploy_logs - log_id INTEGER - svc_name TEXT - version TEXT - env_name TEXT - status TEXT - deploy_at TEXT - dur_secs INTEGER - author TEXT Sample rows (10 of 200): | log_id | svc_name | version | env_name | status | deploy_at | dur_secs | author | | --- | --- | --- | --- | --- | --- | --- | --- | | 131 | payment-api | v1.0.2 | staging | failed | 2026-02-02 01:11:00 | 23 | bob | | 162 | user-svc | v1.1.0 | dev | rolled_back | 2026-03-03 02:22:00 | 36 | charlie | | 193 | search-api | 2.0.0 | canary | in_progress | 2026-04-04 03:33:00 | 49 | dana | | 224 | gateway | v2.1.0 | Production | Success | 2026-05-05 04:44:00 | 62 | eve | | 255 | notif-svc | 2.1.1 | STAGING | FAILED | 2026-06-06 05:55:00 | 75 | frank | | 286 | analytics | v3.0.0-rc1 | production | success | 2026-07-07 06:06:00 | 88 | Alice | | 317 | ml-serving | v1.0.3 | staging | failed | 2026-08-08 07:17:00 | NULL | BOB | | 348 | auth-svc | v1.0.1 | dev | Rolled_Back | 2026-09-09 08:28:00 | 114 | alice | | 379 | Payment-API | v1.0.2 | canary | in_progress | 2026-01-10 09:39:00 | 127 | bob | | 410 | user-svc | v1.1.0 | Production | Success | 2026-02-11 10:50:00 | 140 | charlie | ## Expected output First 4 of 4 rows: | svc_name | deploy_count | max_duration | | --- | --- | --- | | gateway | 7 | 478 | | auth-svc | 7 | 346 | | user-svc | 6 | 580 | | analytics | 6 | 424 | ## 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. > **A fixed date pins one year** > > The classic miss is `WHERE deploy_at >= '2026-04-01'`. It reads like 'April onward' and runs without complaint, but it locks the result to one year. The month has to be pulled out on its own and compared with no year attached. --- ### 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. **Per-service production deploys from April onward** ```sql 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. > **Month first, year never** > > The tell is how fast you notice the window repeats every year. Strong candidates isolate the month immediately. Weaker ones write a date range, pin one year and never check how many years `deploy_at` actually covers. > **One pass, a tiny aggregate** > > This is a single scan: filter, then group. At the scale the table's profile describes (around 600,000 deploys across roughly 70 services), the aggregate holds about 70 entries, so memory is a non-issue. The month test wraps `deploy_at` in a function that a plain index on `deploy_at` cannot serve. If this ran often, a stored month column indexed alongside `env_name` would let the engine skip most of the scan. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/after_the_cutoff) - [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.