Top Channels by Engagement | Dataford Interview Questions

Write a SQL query using window functions to identify the top three media channels by user engagement for each healthcare campaign at Omnicom Health.

Use the provided campaign and engagement event data. Treat total engagement as the sum of valid engagement scores, excluding events without a campaign, channel, or user.

Output

One row per qualifying campaign and media channel, with campaign_name, channel_name, total_engagement, and engagement_rank.
Include at most the three highest-ranked channels per campaign.
Order by campaign name, engagement rank, and channel name. Break ties alphabetically by channel name.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique healthcare campaign identifier
campaign_name	VARCHAR(150)	Healthcare campaign name

engagement_events

Column	Type	Description
event_idPK	INT	Unique engagement event identifier
campaign_id	INT	Referenced healthcare campaign
channel_name	VARCHAR(100)	Media channel associated with the event
user_id	VARCHAR(40)	User associated with the engagement event
engagement_score	INT	Engagement value contributed by the event

Your question is Top Channels by Engagement. 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