SQL for CDN Traffic Spikes | Dataford Interview Questions

Write a SQL query to identify anomalous traffic spikes from a database containing billions of Akamai CDN request logs.

For this exercise, analyze production requests during February 1, 2025. Treat an hourly request count as anomalous when it is at least three times the lowest observed hourly count for the same property during that day.

Output

One row per anomalous property and hour.
Return property_id, property_name, hour_start, request_count, baseline_min_requests, and spike_ratio.
Include production properties only, order by property_id and hour_start ascending, and exclude rows with NULL property identifiers or timestamps.

Schema

cdn_request_logs

Column	Type	Description
request_idPK	BIGINT	Unique CDN request identifier
property_id	INT	Akamai property receiving the request
requested_at	TIMESTAMP	Timestamp when the request was received
request_path	VARCHAR(255)	Requested URL path
http_method	VARCHAR(20)	HTTP method used by the request

akamai_properties

Column	Type	Description
property_idPK	INT	Unique Akamai property identifier
property_name	VARCHAR(100)	Readable property name
environment	VARCHAR(20)	Deployment environment

Your question is SQL for CDN Traffic Spikes. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem