# 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 Our release team is auditing production deploys from April onward, regardless of year, to see which services stayed active late in the cycle. Break those deploys down by service, showing how many each had and the 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 | | --- | --- | --- | | auth-svc | 7 | 346 | | gateway | 7 | 478 | | analytics | 6 | 424 | | user-svc | 6 | 580 | ## Worked solution and explanation ### What this problem is really testing This is a two condition filter feeding a per service count, wearing a deployment audit costume. The real skill being probed: can you derive a month from a timestamp and use it as a filter next to a plain equality check, then roll the survivors up by service. Anyone can write COUNT and MAX. The part that separates people is 'April onward, regardless of year'. Compare the raw timestamp to a fixed date and you silently scope everything to one year; extract the month and keep 4 through 12 and it works across every year in the table. --- ### The trap The data spans several years. 'April or later, regardless of year' means calendar months 4 through 12 in any of them, not a continuous window starting from one April. That single word, regardless, is the whole difficulty of an otherwise routine count. > **Common pitfall** > > The classic miss is filtering with `deploy_at` >= '2026-04-01'. That reads like April onward but locks the result to one year and quietly drops every other year's spring and summer deploys. The prompt says regardless of year, so the month has to be pulled out on its own with strftime. --- ### Building the query #### Step 1: Keep only the deploys that qualify Two conditions live in the WHERE: `env_name` = 'production' for the environment, and the month of `deploy_at` at 4 or higher for the April onward window. Extract the month with strftime and cast it to an integer before comparing, so '04' through '12' behave as numbers rather than strings. #### Step 2: Roll the survivors up per service GROUP BY `svc_name` collapses each service into one row. COUNT(*) gives the number of qualifying deploys and MAX(`dur_secs`) gives the longest one; MAX quietly skips any NULL duration, so a missing value never wins. #### Step 3: Surface the busiest first ORDER BY `deploy_count` DESC puts the heaviest services on top. A secondary sort on `svc_name` makes ties deterministic, which matters when several services share the same count. **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, svc_name ``` **Pins a single year** WHERE `deploy_at` >= '2026-04-01' looks reasonable but hard-codes 2026 and drops every other year's April onward deploys. In a multi-year table it silently undercounts. **Extracts the month** CAST(strftime('%m', `deploy_at`) AS INTEGER) >= 4 keeps months 4 through 12 in every year, which is exactly what 'regardless of year' asks for. > **Interviewers watch for** > > The tell is how you handle 'regardless of year'. Strong candidates reach for month extraction immediately; weaker ones write a date range and pin a single year without noticing. Interviewers also watch whether you make the tie order deterministic instead of leaving it to chance. > **Cost analysis** > > The table holds 600,000 rows and the query is a single pass: filter, then group. `env_name` and a computed month are both cheap predicates, and the grouping key `svc_name` has only about 70 distinct values, so the hash aggregate stays tiny. A composite index on (`env_name`, `deploy_at`) would let the engine skip most of the scan if this ran often. --- ## Common follow-up questions - If the log grew to cover five years, does your filter still capture April onward in each of them? _(Tests whether the candidate keeps the month-based window correct once the data crosses 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 extending the aggregation to include services that qualify on the environment but have zero April-onward deploys.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/production_deploys_from_april_onward) - [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.