SQL Joins for Campaign and Transactions | Dataford Interview Questions

Write a SQL query utilizing different types of JOINs to combine Seatgeek marketing campaign spend data with user transaction records.

Use the provided tables and return one row per campaign with spend. Include campaigns with no matching completed transactions, exclude spend rows whose campaign ID is not defined, and count only completed transactions.

Output

Columns: campaign_id, campaign_name, total_spend, completed_transactions, completed_revenue
One row per defined campaign with at least one spend record
Order by campaign_id ascending

Schema

marketing_campaigns

Column	Type	Description
campaign_idPK	VARCHAR(20)	Unique Seatgeek marketing campaign identifier
campaign_name	VARCHAR(100)	Campaign display name
channel	VARCHAR(30)	Primary marketing channel

campaign_spend

Column	Type	Description
spend_idPK	INT	Unique spend record identifier
campaign_id	VARCHAR(20)	Campaign associated with the spend record
spend_date	DATE	Date the spend was recorded
spend_amount	NUMERIC(12,2)	Marketing spend amount

user_transactions

Column	Type	Description
transaction_idPK	INT	Unique user transaction identifier
user_id	INT	User associated with the transaction
campaign_id	VARCHAR(20)	Campaign attributed to the transaction
transaction_date	DATE	Date of the transaction
status	VARCHAR(20)	Transaction lifecycle status
gross_amount	NUMERIC(12,2)	Gross transaction amount

Your question is SQL Joins for Campaign and Transactions. 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