SQL Scenarios for Marketing Tables

MediumSQL · PostgreSQL

Problem

Canva’s marketing analytics team wants to identify the best-performing channel for each active campaign during Q1 2026. Write one PostgreSQL query using the campaign, channel, and daily performance tables.

Requirements

Include only active campaigns launched on or before 2026-03-31, active marketing channels, and performance recorded from 2026-01-01 through 2026-03-31.
Aggregate spend, impressions, clicks, and conversions by campaign and channel.
Calculate click-through rate as clicks divided by impressions, expressed as a percentage, and cost per conversion.
Rank channels within each campaign by conversions descending, then spend ascending, and return only the top-ranked channel for each campaign.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Canva marketing campaign name
campaign_status	VARCHAR(20)	Campaign lifecycle status
launch_date	DATE	Date the campaign launched

marketing_channels

Column	Type	Description
channel_idPK	INT	Unique marketing channel identifier
channel_name	VARCHAR(50)	Marketing channel name
channel_type	VARCHAR(30)	Channel category
channel_status	VARCHAR(20)	Whether the channel is active

campaign_daily_performance

Column	Type	Description
performance_idPK	INT	Unique performance row identifier
campaign_id	INT	References campaigns.campaign_id
channel_id	INT	References marketing_channels.channel_id
performance_date	DATE	Date of the performance measurement
spend	DECIMAL(12,2)	Daily media spend
impressions	INT	Daily ad impressions
clicks	INT	Daily ad clicks
conversions	INT	Daily attributed conversions

Tablescampaignsmarketing_channelscampaign_daily_performance