Average Response Time by Hour

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

Users in Asia-Pacific keep reporting slow page loads during their morning. To check whether the servers show a time-of-day pattern, compute the average response time for each hour of the day and present the hours in chronological order.

Hour by hour. When does latency spike?

Tables

server_logs

Columns: log_id INTEGER, server_name TEXT, log_level TEXT, message TEXT, response_time_ms INTEGER, log_timestamp TEXT

Expected output

Worked solution and explanation

What this is really asking

This is a time-of-day profile dressed up as an Asia-Pacific latency complaint. Every row of server_logs records a moment, but the question wants a recurring bucket: the hour on the clock, so months of logs fold into 24 rows. Anyone can write AVG(response_time_ms). The trap is grouping on log_timestamp itself or on its date. Nearly every timestamp is its own group, and a date bucket gives one row per day, so the morning pattern never shows up.

The second trap is quieter. strftime('%H', log_timestamp) hands back text like '07', and the expected answer is the number 7. The hours where no row recorded a response time still belong in the profile, with a blank average.

Break down the requirements

Step 1: Derive the hour from log_timestamp

Pull the hour-of-day field out of the full datetime and cast it to an integer, so the bucket reads 0 to 23 rather than '00' to '23'. This derived value is the grouping key; the stored column never can be.

Step 2: Average response_time_ms per hour

AVG already skips NULLs, so a missing reading neither drags the mean toward zero nor counts as a row. An hour whose readings are all missing comes back NULL, and that is the right answer.

Step 3: Sort by hour_of_day

Ascending on the integer key reads as a daily timeline. On text keys the sort happens to work only because of the zero padding, and the column would still be the wrong type.

The solution

SELECT CAST(strftime('%H', log_timestamp) AS INTEGER) AS hour_of_day,
       AVG(response_time_ms) AS avg_response_time
FROM server_logs
GROUP BY hour_of_day
ORDER BY hour_of_day ASC;

Common follow-up questions

Common follow-up questions

• How would you report these hours in Asia-Pacific local time? (Shift before extracting, for example `log_timestamp + INTERVAL '8 hours'`, or better, store timestamps with an explicit zone.)
• What if the slow loads live in the p95, not the mean? (Swap or pair the average with a per-hour percentile such as `PERCENTILE_CONT(0.95)`.)
• Can you add a `server_name` breakdown without flooding the result? (Group by hour and `server_name`, then keep the worst few servers per hour with `ROW_NUMBER()`.)