SQL for Top Marketing Channel | Dataford Interview Questions

Artech wants to identify its strongest marketing channels based on recent completed revenue. Write a SQL query to find the channel or channels with the highest completed revenue during the last fully completed calendar quarter.

Output

One row per marketing channel tied for the highest revenue, with columns channel_name, total_revenue, and revenue_rank
Include only completed transactions in the previous calendar quarter. Exclude refunded and other non-completed transactions, and treat missing revenue as contributing no revenue
Include channels with no qualifying transactions when determining the top result
Return every channel tied for first place, ordered alphabetically by channel_name

Schema

marketing_channels

Column	Type	Description
channel_idPK	INT	Unique marketing channel identifier
channel_name	VARCHAR(100)	Artech marketing channel name

revenue_transactions

Column	Type	Description
transaction_idPK	INT	Unique revenue transaction identifier
channel_id	INT	Marketing channel associated with the transaction
transaction_date	DATE	Date the transaction was recorded
revenue	DECIMAL(12,2)	Revenue attributed to the transaction
transaction_status	VARCHAR(20)	Transaction state, such as completed or refunded

Your question is SQL for Top Marketing Channel. 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