The Ides of March

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

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, then order the endpoints from the one with the highest peak down to the one with the lowest.

Every endpoint has one March it would rather forget.

Tables

api_calls

Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT

Expected output

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.

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.

SELECT endpoint, MAX(latency) AS max_latency
FROM api_calls
WHERE strftime('%m', call_time) = '03'
GROUP BY endpoint
ORDER BY max_latency DESC

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.

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.)