SQL Conversion Rate and Ranking | Dataford Interview Questions

Write a SQL query to calculate conversion rate by campaign and date, and rank campaigns by performance. Use the provided campaign and event data, treating clicks as the denominator and conversions as the numerator. Exclude events without a valid campaign or date.

Output

One row per campaign and event date.
Columns: campaign_name, event_date, click_count, conversion_count, conversion_rate, and performance_rank.
Return conversion rate as a percentage, rank campaigns separately for each date, sort by date, rank, and campaign ID, and assign equal ranks to ties.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name
campaign_channel	VARCHAR(50)	Marketing channel for the campaign

campaign_events

Column	Type	Description
event_idPK	INT	Unique event identifier
campaign_id	INT	Campaign associated with the event
event_date	DATE	Date on which the event occurred
event_type	VARCHAR(20)	Event classification, such as click or conversion
user_id	VARCHAR(50)	Anonymous user identifier

Your question is SQL Conversion Rate and Ranking. 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