# The Shape of a Day > Traffic has a rhythm across the hours. Find where it runs busiest. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem Capacity planning wants the shape of an average day: for each hour of the clock, how many API calls land in a typical day. Return every hour with that average daily call count, busiest first. ## Worked solution and explanation ### What this is really testing Underneath the capacity-planning story this is a two-level aggregation: you count at one grain (day and hour), then average at a coarser grain (hour alone). The skill being probed is whether you notice that the word 'average' is doing real work. Most candidates count total calls per hour and stop, which silently answers a different question. An hour that gets slammed once and sits quiet the rest of the week can out-total an hour that runs steadily busy every single day, yet the steady hour is the one you provision for. Average across days and the ranking can flip; sum instead, and you staff the wrong hour. --- ### Why one GROUP BY is not enough The seed is built to punish the shortcut. Hour 9 fires three times on two different days, so it accumulates six calls in total. Hour 12 fires four times, all on a single day. By lifetime volume hour 9 leads. By a typical day hour 12 leads, because four calls in the one day it appeared beats three-per-day. The correct metric inverts the naive one. **Total calls per hour (wrong)** One GROUP BY hour with COUNT(*) sums every call the hour ever saw. Hour 9 shows 6 and beats hour 12's 4, so it lands on top. But those 6 were spread across two days. **Average daily calls per hour (right)** Tally per day first, then average those daily counts. Hour 12 averages 4.0 (one busy day), hour 9 averages 3.0 (steady but lighter). The peak flips to hour 12, the hour you actually staff for. --- ### Building it #### Step 1: Count at the fine grain In a CTE, GROUP BY DATE(call_time) and the hour pulled from call_time with strftime, taking COUNT(*). Each output row is one day-hour pair: how many calls that hour saw on that specific date. This is the grain the naive query skips. #### Step 2: Collapse days into an average Over the CTE, GROUP BY the hour alone and take AVG(call_count). Because the CTE already reduced each day to a single number, AVG divides by the count of days the hour appeared, which is exactly 'calls in a typical day'. #### Step 3: Order the leaderboard ORDER BY the average descending so the busiest hours surface first, with a tie-break on the hour for stability. No LIMIT: the full day profile, not just the crest, is the deliverable. **Count per day-hour, then average per hour** ```sql 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, call_hour ``` > **Common pitfall** > > The one-pass count. A single GROUP BY hour with COUNT(*) runs clean and looks right, but it ranks by lifetime volume, not by a typical day. Any hour that appears on more days accumulates more total calls and floats up regardless of how busy a given day actually was. > **Interviewers watch for** > > Reaching for a CTE that counts per day-hour before averaging is the tell that you parsed 'average across days' correctly. Jumping straight to a single GROUP BY hour is the tell that you did not. The sharpest candidates also ask what the average divides by before they write anything. > **Cost at scale** > > At 300M rows across 365 daily partitions, the inner aggregation scans the fact table once and there is no join, so cost is dominated by the group-by over call_time. Because call_time is the partition key, a date-range filter prunes whole partitions before any scan. For a dashboard that re-runs this profile, a daily rollup (one row per day-hour) turns the outer average into a trivial scan over a tiny table. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_shape_of_a_day) - [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.