Top Campaigns by Revenue Per Client | Dataford Interview Questions

Write a query to find the top 3 performing marketing campaigns by revenue for each client in the last 30 days on Attentive.

Use the provided client, campaign, and revenue event data. Treat the last 30 days as beginning at CURRENT_DATE - INTERVAL '30 days'. Break revenue ties by the lower campaign ID.

Output

One row per qualifying campaign, with client_name, campaign_id, campaign_name, total_revenue, and campaign_rank.
Include no more than three campaigns per client, ordered by client_name, campaign_rank, and campaign_id.

Schema

clients

Column	Type	Description
client_idPK	INT	Unique Attentive client identifier
client_name	VARCHAR(100)	Client display name

campaigns

Column	Type	Description
campaign_idPK	INT	Unique marketing campaign identifier
client_id	INT	Client that owns the campaign
campaign_name	VARCHAR(150)	Campaign display name

revenue_events

Column	Type	Description
event_idPK	INT	Unique revenue event identifier
campaign_id	INT	Campaign attributed to the event
event_at	TIMESTAMPTZ	Timestamp when revenue was attributed
revenue_amount	NUMERIC(12,2)	Revenue attributed to the event

Your question is Top Campaigns by Revenue Per Client. 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