Top 2 Busiest API Slots

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

L4

Asked in

Travelport

Problem

A reliability team is triaging which failures crowd the day. Take only the calls that came back with an error message, and read each call's hour as a whole number from 0 to 23: hours before 12 are Morning, hours 12 through 15 are Early Afternoon, and anything later is Late Afternoon. For every HTTP status, count its failures in each slot and return the status-and-slot pairs whose failure count is among the three highest, keeping every pair tied at a count.

Two time slots per week. The busiest.

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

Strip the reliability story and this is a tie-preserving top-N over a filtered, two-key bucketized count. The filter is the whole game. Successful calls carry no error message, and on this traffic the success codes (led by status 200, the single hottest status) are the overwhelming majority. Keep them in and you are no longer measuring failures, you are measuring total volume, and the pairs you report become the busiest slots overall. So WHERE err_msg IS NOT NULL is not hygiene, it is the definition of the population.

The second move is the tie rule. Several status-and-slot pairs land on the same count at the bottom of the top three, so DENSE_RANK earns its place over LIMIT 3: LIMIT keeps three rows by an engine-dependent coin flip and silently drops the pairs it did not happen to pick. Get either move wrong and the answer is confidently incorrect: either you are ranking successful traffic, or you are hiding legitimately tied slots.

The three traps

Step 1: The failure filter defines the population

err_msg is empty on the large majority of rows, because those calls succeeded, and status 200 alone dwarfs every error code. Filter on err_msg IS NOT NULL at the source, before the buckets form, and only real failures survive. Drop it and status 200 reappears, floods Morning and both afternoon slots, and the top pairs you return describe successful traffic rather than failures. Filtering after the grouping has the same effect if it lets successes into the count first.

Step 2: Off-by-one on the segment boundaries

The middle slot, '12:00 through 15:00', is inclusive on both ends, so the CASE must read <= 15, not < 15. Write < 15 and hour 15 falls through to the ELSE, lands in 'Late Afternoon', and quietly moves an hour of afternoon failures into the wrong slot. Morning is the easy end (< 12 correctly excludes hour 12); the Early Afternoon upper bound is where people slip. Note the hour has to be read as an integer: CAST(strftime('%H', call_time) AS INTEGER), so the comparison is numeric rather than string.

Step 3: DENSE_RANK vs RANK vs ROW_NUMBER

All three express 'top N' but answer different questions.

- ROW_NUMBER: a fixed number of rows, ties broken arbitrarily. Wrong here. - RANK: skips numbers after a tie, so a wide tie can shove the cutoff past rnk <= 3 unpredictably. - DENSE_RANK: numbers the count tiers with no gaps, so rnk <= 3 reliably means 'every pair in the top three failure counts'.

When pairs tie at the third count, DENSE_RANK returns all of them; that is exactly 'keeping every pair tied at a count'.

The solution

WITH segmented AS (
  SELECT
    status,
    CASE
      WHEN CAST(strftime('%H', call_time) AS INTEGER) < 12 THEN 'Morning'
      WHEN CAST(strftime('%H', call_time) AS INTEGER) <= 15 THEN 'Early Afternoon'
      ELSE 'Late Afternoon'
    END AS time_segment,
    COUNT(*) AS call_count
  FROM api_calls
  WHERE err_msg IS NOT NULL
  GROUP BY status, time_segment
),
ranked AS (
  SELECT *, DENSE_RANK() OVER (ORDER BY call_count DESC) AS rnk
  FROM segmented
)
SELECT status, time_segment, call_count
FROM ranked
WHERE rnk <= 3

Common follow-up questions

• If you swapped `DENSE_RANK` for ORDER BY `call_count` DESC LIMIT 3, on what input would the output differ? (Probes whether the candidate sees the tie boundary. Answer: any input where two or more pairs tie at the lowest kept count; LIMIT 3 returns three rows, `DENSE_RANK` returns every tied pair.)
• Hour 15 falls into which slot per the prompt, and what does the query actually do? (Forces the candidate to re-read the boundaries. Answer: '12:00 through 15:00' inclusive puts hour 15 in Early Afternoon, which the <= 15 check confirms; a < 15 check would be wrong.)
• If you dropped the `err_msg` filter, how would the result change and why? (Tests that the candidate knows the filter is load-bearing. Answer: successful calls (led by status 200) rejoin the count, every bucket inflates, and the ranking reflects total traffic rather than failures, changing which pairs reach the top.)
• Rewrite this so each HTTP status gets its own busiest slot. (Tests partitioned window functions. Answer: `DENSE_RANK`() OVER (PARTITION BY status ORDER BY `call_count` DESC) and filter rnk <= 1 (or the desired depth). Each status gets its own leaderboard instead of one global ranking.)
• Across all statuses combined, which single time slot sees the most failures? How does the query change? (Tests that the candidate can collapse a dimension. Answer: drop status from the GROUP BY and the SELECT, leaving three rows; order by `call_count` DESC and LIMIT 1.)