Rush Hour API Latency

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Lyft

Problem

Across every day in api_calls, what is the average latency in each hour from 15:00 through 17:59, earliest hour first? That table logs only the us-east traffic an on-call engineer is sizing afternoon capacity for.

Rush hour hits the API differently.

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

This is time-of-day bucketing dressed up as capacity planning. call_time is a full timestamp spread over years of traffic, and the question wants three buckets that recur every single day: the 15, 16 and 17 o'clock hours. No contiguous stretch of timestamps is 'the afternoon peak', so any range filter on call_time catches one afternoon or one arbitrary span. The skill is pulling the hour out of each row and letting that one derived value do double duty, as the filter and as the bucket. Get it wrong and capacity gets sized off a single afternoon, or off one blended number that hides how latency climbs through the peak.

Building it

Step 1: Derive the hour from call_time

call_time is stored as text like '2026-06-15 16:01:00'. strftime('%H', call_time) pulls out '16', but as a string. CAST(... AS INTEGER) turns it into a number you can compare against 15 and 17 and that sorts numerically.

Step 2: Filter on the hour, not the timestamp

BETWEEN 15 AND 17 is inclusive on both ends, and because you compare whole hours, 17 already covers 17:00 through 17:59. < 18 says the same thing; <= 18 quietly drags in the 18 o'clock rush.

Step 3: Bucket by that same hour and average

Group on the alias hour and take AVG(latency). Two calls inside the window never recorded a latency; AVG skips those NULLs, which is right, since a missing measurement is not a zero-millisecond call.

Step 4: Read the peak in order

ORDER BY hour lays the three buckets out from 15 to 17, so the climb across the afternoon reads top to bottom.

SELECT
    CAST(strftime('%H', call_time) AS INTEGER) AS hour,
    AVG(latency) AS avg_latency
FROM api_calls
WHERE CAST(strftime('%H', call_time) AS INTEGER) BETWEEN 15 AND 17
GROUP BY hour
ORDER BY hour

Timestamp range

WHERE call_time BETWEEN '... 15:00:00' AND '... 17:59:59' keeps one contiguous slice of time. On a log spanning years it catches one afternoon and, without a bucket, returns one number.

Hour of day

WHERE CAST(strftime('%H', call_time) AS INTEGER) BETWEEN 15 AND 17 keeps the peak hours of every day, and grouping on that hour returns the three rows the planning question needs.

SELECT AVG(latency) AS avg_latency
FROM api_calls
WHERE call_time BETWEEN '2026-06-15 15:00:00' AND '2026-06-15 17:59:59'

Common follow-up questions

• Planning now wants the peak split by day of week, so Monday 16:00 is separate from Saturday 16:00. What changes? (Adds a second derived key to the same bucket.)
• `call_time` is stored in UTC but the peak is defined in Eastern local time. Where does the conversion go? (Time zone shift before extracting the hour.)
• The team wants p95 latency per hour instead of the mean. How do you get it? (Percentiles versus averages on a skewed latency distribution.)