CDN Traffic by Day and Hour

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

DoorDash

Problem

The infra team is sizing CDN edge capacity and wants a throughput heatmap across the week, broken out by weekday and hour of day. For every weekday-and-hour pairing find the average net bytes per request, where net bytes is the gross bytes left after a flat 5 percent transfer overhead is removed; spell the weekday out in full like 'Monday' and round the average to two decimals. Lay the heatmap out so it reads like a calendar: the days running Sunday through Saturday, and within each day the hours climbing from the earliest to the latest.

CDN traffic, hour by hour.

Tables

cdn_logs

Columns: log_id INTEGER, edge_loc TEXT, req_path TEXT, status INTEGER, bytes INTEGER, cache_hit INTEGER, served_at TEXT

Expected output

Worked solution and explanation

Why this problem exists in real interviews

CDN capacity planning lives or dies on weekday-by-hour heatmaps. Interviewers use this prompt to see whether you can extract weekday and hour from a TEXT timestamp in SQLite (no DATE_PART, no TO_CHAR), translate the numeric weekday into its English name with a CASE, and apply a percentage adjustment to bytes inside the average.

Break down the requirements

Step 1: Map numeric weekday to its name

strftime('%w', served_at) returns '0' for Sunday through '6' for Saturday as text. SQLite has no day-of-week name function, so the canonical pattern is a CASE expression with seven branches that returns the English name.

Step 2: Extract hour and apply the 5 percent overhead deduction

CAST(strftime('%H', served_at) AS INTEGER) gives an integer hour 0 to 23. For net bytes, multiply bytes by 0.95 inside AVG so the average reflects net throughput per request, not gross.

Step 3: Group by raw weekday number and hour, then order by them

GROUP BY strftime('%w', served_at), strftime('%H', served_at) keeps the grouping cheap (no CASE in the GROUP BY). Order by the raw '%w' so days come out Sunday through Saturday, then by hour ascending. AVG already returns a float, so ROUND(..., 2) needs no explicit cast around it.

The solution

SELECT
  CASE WHEN strftime('%w', served_at) = '0' THEN 'Sunday'
       WHEN strftime('%w', served_at) = '1' THEN 'Monday'
       WHEN strftime('%w', served_at) = '2' THEN 'Tuesday'
       WHEN strftime('%w', served_at) = '3' THEN 'Wednesday'
       WHEN strftime('%w', served_at) = '4' THEN 'Thursday'
       WHEN strftime('%w', served_at) = '5' THEN 'Friday'
       WHEN strftime('%w', served_at) = '6' THEN 'Saturday'
  END AS weekday,
  CAST(strftime('%H', served_at) AS INTEGER) AS hour,
  ROUND(AVG(bytes * 0.95), 2) AS avg_net_bytes
FROM cdn_logs
GROUP BY strftime('%w', served_at), strftime('%H', served_at)
ORDER BY strftime('%w', served_at), hour

Common follow-up questions

• How would you adapt this to compute peak hour per weekday instead of every hour? (Forces a window function: `ROW_NUMBER` OVER (PARTITION BY weekday ORDER BY `avg_net_bytes` DESC) and filter to rn = 1. The candidate should mention this would shrink the result from 168 rows to 7.)
• How would you handle a billion-row scan in production? (Partition `cdn_logs` by `served_at` day or week, store a precomputed weekday/hour column, and run the aggregation as a daily rollup so this query reads from a small summary table instead of the raw logs.)
• What if the overhead were 5 percent of bytes plus 200 fixed bytes per request? (Now the formula is non-linear in the average. The candidate should compute net bytes per row (bytes * 0.95 - 200) and then AVG that expression, and explicitly call out why pulling constants outside the aggregate no longer works.)