Median CTR by Meta Campaign | Dataford Interview Questions

Meta Ads analysts want to compare campaign performance using the median click-through rate (CTR) across daily delivery records. Write a PostgreSQL query to return the median CTR for each ad campaign using window functions.

CTR for a row is defined as clicks::numeric / impressions. Rows with impressions = 0 or impressions IS NULL should be excluded from the median calculation.

Requirements

Compute CTR for each valid row in ad_delivery.
For each campaign_id, calculate the median CTR using window functions rather than PERCENTILE_CONT.
If a campaign has an odd number of valid CTR rows, return the middle CTR.
If a campaign has an even number of valid CTR rows, return the average of the two middle CTR values.
Output campaign_id, campaign_name, and median_ctr, ordered by campaign_id.

Schema

meta_campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name in Meta Ads Manager
objective	VARCHAR(50)	Campaign objective

ad_delivery

Column	Type	Description
delivery_idPK	INT	Unique delivery record
campaign_id	INT	Campaign identifier
delivery_date	DATE	Daily delivery date
impressions	INT	Number of impressions served
clicks	INT	Number of clicks received

Your question is Median CTR by Meta Campaign. 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