Find Publishers With Low CTR | Dataford Interview Questions

A digital advertising platform wants to identify publishers whose recent ad performance is substantially below the network benchmark. Write a SQL query to find publishers with low click-through rates using metric records from a fixed 24-hour reporting window ending at 2026-09-15 12:00:00+00.

Output

Return one row per eligible publisher with at least 100 impressions in the reporting window.
Include publisher_id, publisher_name, impressions, clicks, and ctr.
Include publishers whose CTR is less than half of the overall CTR across eligible publishers.
Round ctr to six decimal places.
Order by ctr ascending, then publisher_id ascending.
Records with NULL revenue do not affect the result because revenue is not used.

Schema

publishers

Column	Type	Description
publisher_idPK	INT	Unique publisher identifier
publisher_name	VARCHAR(120)	Publisher display name

ad_metrics

Column	Type	Description
metric_idPK	INT	Unique metric record identifier
publisher_id	INT	Publisher associated with the metric record
observed_at	TIMESTAMPTZ	Start time of the metric interval
impressions	INT	Number of ad impressions
clicks	INT	Number of ad clicks
revenue	NUMERIC(12,2)	Revenue attributed to the metric interval

Your question is Find Publishers With Low CTR. 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