Peak Hour Power Callers

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

Asked in

Redfin

Problem

Afternoon API traffic between 3 and 6 PM keeps spiking, and the on-call support engineer wants to know which callers in api_calls are driving it. List each user with at least 3 calls in that window and their call count, busiest first, with calls that carry no user id counted together as a single caller.

One hour. The phone lines exploded.

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 costume and this is a per-caller frequency count fenced inside a recurring time-of-day window. The window is what bites first: call_time is a full timestamp spread over years of days, so any plain range on it selects one stretch of the calendar, not the same 3 to 6 PM slice on every day. Only the hour read out of each row isolates the afternoon. After that, the skill is keeping two filters apart: the window filters calls, and the 'at least 3' test filters callers. Read '3 to 6 PM' as hours 15 through 18 and you fold the whole 6 PM hour into every count.

The two-stage filter

Step 1: Fence the afternoon window

The window is a time of day, not a date range, so pull the hour out of call_time and keep 15, 16 and 17. EXTRACT(HOUR FROM call_time) BETWEEN 15 AND 17 captures 15:00:00 through 17:59:59 on every day in the table. Comparing call_time itself against '15:00:00' and '18:00:00' looks right and matches nothing, because every value starts with a date.

Step 2: Count per caller, keep the heavy hitters

Collapse the surviving rows to one row per caller with GROUP BY user_id, then keep the heavy hitters with HAVING COUNT(*) >= 3. Calls with no user_id fall into one group of their own, which the statement keeps as a single caller. COUNT(*) counts that group's rows; COUNT(user_id) would score it 0 and quietly drop it.

Step 3: Sort busiest first

Return the caller and the count with ORDER BY call_count DESC. Callers tied on a count can come back in any order: the question asks who is busiest, and nothing in it ranks two equally busy callers.

The solution

SELECT user_id, COUNT(*) AS call_count
FROM api_calls
WHERE EXTRACT(HOUR FROM call_time) BETWEEN 15 AND 17
GROUP BY user_id
HAVING COUNT(*) >= 3
ORDER BY call_count DESC

Common follow-up questions

• The output keeps calls with no `user_id` as one blank caller. If support wanted only identified users, what would you change, and in which clause? (Tests whether the candidate knows a NULL grouping key forms its own group and that excluding it is a row filter, not a group filter.)
• Why can the hour-of-day filter live in `WHERE` while the 3-call threshold cannot? (Tests understanding of pre-aggregation versus post-aggregation filtering.)
• The table is partitioned by day but you filter on the hour of day. What index or precomputation keeps this fast across all 200M rows? (Tests indexing intuition for a time-of-day predicate on a date-partitioned table.)