Top Marketing Channel by Revenue | Dataford Interview Questions

Groupon marketing wants to identify which acquisition channel generated the most recognized revenue from attributed orders. Write a PostgreSQL query using Groupon campaign attribution data.

Requirements

Join marketing channels, campaigns, and orders using their key relationships.
Include only completed orders placed during January 2025, from January 1 through January 31.
Aggregate gross revenue by marketing channel and return the top-performing channel or channels, including ties.
Return channel_name and total_revenue, ordered by revenue descending and channel name ascending.

Schema

marketing_channels

Column	Type	Description
channel_idPK	INT	Marketing channel identifier
channel_name	VARCHAR(80)	Groupon marketing channel name
channel_type	VARCHAR(40)	Channel classification

campaigns

Column	Type	Description
campaign_idPK	INT	Campaign identifier
channel_id	INT	Referenced marketing channel
campaign_name	VARCHAR(120)	Groupon campaign name
launched_at	DATE	Campaign launch date

orders

Column	Type	Description
order_idPK	INT	Order identifier
campaign_id	INT	Attributed campaign
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Order processing status
gross_revenue	DECIMAL(12,2)	Recognized gross order revenue

Your question is Top Marketing Channel by Revenue. 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