The Shape of a Day

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

Uber

Problem

On a typical day in the api_calls log, how many calls land in each hour of the clock? List every hour with its average daily call count, busiest first.

Traffic has a rhythm across the hours. Find where it runs 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 is really testing

This is a two-level aggregation dressed up as capacity planning. The thing you are asked to average, a day, is not a row in api_calls. It only exists once you count each hour per calendar date. Anyone can pull the hour out of call_time. What separates candidates is building that per-day count before averaging it. Skip it and you either report lifetime totals per hour, which run to dozens where a typical day holds two or three, or you divide by every date in the table and shrink every hour below one call a day.

Why one pass is not enough

Each clock hour in this log is active on a different set of dates. The February traffic block stacks several endpoints onto the same day and hour, while the backfill scatters single calls across four years. An average per day needs, for each hour, the number of days that hour actually fired. A single grouping on the hour can count calls, but nothing in that grouping knows how many days stand behind the count.

Total calls per hour (wrong)

COUNT(*) grouped by the hour alone sums every call the hour ever saw across four years. The numbers land in the dozens and describe the log's history, not a day.

Average daily calls per hour (right)

Count per call_date and call_hour first, then take AVG(call_count) per hour. Hour 4 comes out at 2.73: on the days it fired, it took a little under three calls.

Building it

Step 1: Count at the fine grain

In a CTE, group by DATE(call_time) and the hour pulled from call_time, taking COUNT(*). Each output row is one day-hour pair: how many calls that hour saw on that date. This is the grain the one-pass query never forms.

Step 2: Collapse days into an average

Over hourly, group by call_hour alone and take AVG(call_count). Each day is already one number, so the average divides by the days that hour appeared, which is exactly calls in a typical day.

Step 3: Sort busiest first

Order by avg_count descending. There is no LIMIT: the whole day profile is the deliverable, and hours that share an average can come in either order.

WITH hourly AS (
    SELECT
        DATE(call_time) AS call_date,
        CAST(strftime('%H', call_time) AS INTEGER) AS call_hour,
        COUNT(*) AS call_count
    FROM api_calls
    GROUP BY call_date, call_hour
)
SELECT call_hour, AVG(call_count) AS avg_count
FROM hourly
GROUP BY call_hour
ORDER BY avg_count DESC

Common follow-up questions

• Alongside the average, report each hour's single busiest day and how many calls it saw. How does that change the query? (Tests whether the candidate can carry a second aggregate at the same grain without a second pass.)
• If an hour had zero calls on some days, should those days count as a 0 in the average or be ignored entirely? How does your query behave today? (Exposes the denominator assumption behind the average.)
• The table is partitioned by day. A user asks for this profile over a rolling 90-day window that updates hourly. How do you keep it cheap? (Tests partition pruning and incremental rollup thinking at scale.)