Top CTR Campaigns by Therapeutic Area | Dataford Interview Questions

Write a query to find the top three highest-performing advertising campaigns based on click-through rate (CTR), grouped by therapeutic area at PulsePoint.

Calculate CTR as clicks divided by impressions. Include only campaigns with positive impressions and non-null clicks. Return up to three campaigns per therapeutic area.

Output

One row per qualifying campaign, with therapeutic_area, campaign_id, campaign_name, and ctr.
Sort by therapeutic area, CTR descending, then campaign ID ascending to break ties.

Schema

therapeutic_areas

Column	Type	Description
therapeutic_area_idPK	INT	Unique therapeutic area identifier
therapeutic_area_name	VARCHAR(100)	Name of the therapeutic area

campaigns

Column	Type	Description
campaign_idPK	INT	Unique advertising campaign identifier
campaign_name	VARCHAR(150)	Advertising campaign name
therapeutic_area_id	INT	Associated therapeutic area identifier

campaign_performance

Column	Type	Description
performance_idPK	INT	Unique performance record identifier
campaign_id	INT	Campaign associated with the performance metrics
impressions	INT	Number of ad impressions
clicks	INT	Number of ad clicks

Your question is Top CTR Campaigns by Therapeutic Area. Start with the requirements and the three 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