Ad Conversion Rate
ASKED IN INTERVIEWGoogle
Problem Statement
Google Ads managers need to see which campaigns are most efficient. The conversion rate is defined as the percentage of clicks that result in a conversion.
Write an SQL query to calculate the conversion rate for each campaign.
Rules:
- conversion_rate = (Total Conversions / Total Clicks) * 100
- Round the conversion_rate to 2 decimal places.
- If a campaign has 0 clicks, the conversion_rate should be 0.00.
- Output columns: campaign_name, conversion_rate.
- Results must be sorted by conversion_rate in descending order, then campaign_name in ascending order.
Tests your understanding of
Aggregation, Ratios and CASE WHEN
Input Tables
Campaigns
| campaign_id(INTEGER) | campaign_name(VARCHAR) |
|---|---|
| 1 | Search_Tech |
| 2 | Display_Fashion |
| 3 | Video_Games |
Ad_Events
| event_id(INTEGER) | campaign_id(INTEGER) | event_type(VARCHAR) |
|---|---|---|
| 1 | 1 | click |
| 2 | 1 | click |
| 3 | 1 | conversion |
| 4 | 2 | click |
| 5 | 2 | click |
| 6 | 3 | click |
Expected Output
| campaign_name(VARCHAR) | conversion_rate(DECIMAL) |
|---|---|
| Search_Tech | 50 |
| Display_Fashion | 0 |
| Video_Games | 0 |
Tags
MediumASKED IN INTERVIEWAggregationRatiosCASE WHEN
10-15 min
68%