# The Ides of March > Every endpoint has one March it would rather forget. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The reliability team is auditing how the API holds up during March, when the spring promotions drive the year's heaviest traffic. For each endpoint, find its worst response time across any March on record, slowest first. ## Worked solution and explanation ### What this is really asking Strip the costume and this is a one-table extremes problem with a sneaky date predicate. The phrase 'any March on record' is doing all the work: you are not slicing one bounded date range, you are pulling the month component out of every timestamp and keeping the calls that land in month 03, no matter the year. Anyone can write MAX(latency) grouped by endpoint. The candidates who stumble are the ones who reach for a year-bounded date range, or compare the month to the integer 3, and quietly ship the wrong rows. > **The whole problem hides in the month predicate** > > strftime('%m', call_time) returns a zero-padded STRING, so March is the text '03', not the number 3. Compare string to string and a single WHERE predicate does the entire 'every year, March only' job before any grouping happens. ### Building it step by step #### Step 1: Pin the month, ignore the year call_time carries a full timestamp, but the requirement only cares about the month. strftime('%m', call_time) extracts it as '01' through '12'. Filtering on = '03' in the WHERE clause keeps every March call across every year present in the table, which is exactly what 'any March on record' means. #### Step 2: Collapse to one row per endpoint Group the surviving March rows by endpoint and take MAX(latency). MAX skips nulls by design, so an endpoint that logged a missing latency on one call still reports its worst real number instead of disappearing from the result. #### Step 3: Put the worst on top Sort by the aggregated max_latency descending so the slowest endpoint sits first. The metric is the only sort key the answer depends on, so no extra tiebreaker is required to match the expected output. **Worst March latency per endpoint** ```sql SELECT endpoint, MAX(latency) AS max_latency FROM api_calls WHERE strftime('%m', call_time) = '03' GROUP BY endpoint ORDER BY max_latency DESC ``` *One predicate isolates March of every year, then a plain grouped MAX gives the peak.* > **Two ways this query goes empty or wrong** > > The classic miss is WHERE strftime('%m', call_time) = 3 or = '3'. strftime returns '03', so an unpadded 3 or the bare integer matches nothing and you ship zero rows. The second miss is WHERE call_time BETWEEN '2026-03-01' AND '2026-03-31', which silently throws away March from every other year. **Year-bounded range (wrong)** WHERE call_time BETWEEN '2026-03-01' AND '2026-03-31' keeps only one year's March. Any endpoint whose worst March latency happened in a different year is understated or vanishes entirely. **Month extraction (right)** WHERE strftime('%m', call_time) = '03' keeps March from every year, so MAX(latency) truly reflects the worst March response time on record. > **The tell of a careful reader** > > The signal is whether you ask 'does any March mean one specific year or all of them' before writing a line. Reaching straight for the month-extraction predicate, and being able to say why a BETWEEN range would be wrong, shows you read the requirement instead of pattern-matching it to a date filter. ## Common follow-up questions - What if the team wants the worst latency per endpoint for each month separately, not just March? _(Tests grouping by the extracted month alongside endpoint.)_ - How would you also return the call_id or user_id that hit that peak latency? _(Tests moving from a plain aggregate to a per-group argmax.)_ - If latency can be null and a null must count as worse than any number, how does the query change? _(Tests awareness of how aggregates and ordering treat nulls.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_ides_of_march) - [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.