# Average Response Time by Hour

> Hour by hour. When does latency spike?

Canonical URL: <https://datadriven.io/problems/average_response_time_by_hour>

Domain: SQL · Difficulty: easy · Seniority: L3

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

## Worked solution and explanation

### What this is really asking

`strftime('%H', log_timestamp)` returns TEXT like '07', not an integer. Group on it directly and you get 24 string buckets; cast to INTEGER so hour 0..23 sort and display as numbers across 50M rows.

---

### Break down the requirements

#### Step 1: Extract hour-of-day

strftime('%H', log_timestamp) pulls the zero-padded hour. CAST to INTEGER so consumers and ORDER BY treat it numerically.

#### Step 2: Aggregate the average

AVG(response_time_ms) per hour bucket. AVG ignores NULLs automatically, which matters if any log rows skipped the field.

#### Step 3: Chronological output

ORDER BY hour_of_day ASC. With the INTEGER cast this is true numeric ordering.

---

### The solution

**AVERAGE RESPONSE TIME BY HOUR**

```sql
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;
```

> **Cost Analysis**
>
> 50M rows partitioned by log_timestamp, but we need every partition. Full scan plus hash aggregate over 24 keys. Adding a date filter prunes partitions hard.

> **Interviewers Watch For**
>
> Whether you ask 'which timezone is log_timestamp in?' before answering an Asia-Pacific question. Hours are server-clock unless stated.

> **Common Pitfall**
>
> AVG over a tail-heavy distribution hides p95 and p99 spikes. The morning slowness users feel may be a long-tail problem. Quantiles tell a richer story.

> **The False Start**
>
> First instinct is `HOUR(log_timestamp)` or `EXTRACT(HOUR FROM log_timestamp)`. SQLite has neither and throws a syntax error. Pivot to `strftime('%H', ...)` and CAST AS INTEGER so the bucket sorts numerically.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- Convert these to Asia-Pacific local time? _(Apply a timezone offset: strftime('%H', datetime(log_timestamp, '+8 hours')). Better, store with explicit TZ.)_
- What if slow loads concentrate in p95, not the mean? _(AVG flattens spikes. Use NTILE(100) or PERCENTILE_CONT bucketed by hour.)_
- Add a server_name breakdown without blowing up the result set? _(Add server_name to GROUP BY, then TOP-N per hour with ROW_NUMBER, or pre-filter to the worst 5 servers.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/average_response_time_by_hour)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.