Mock interview. You're interviewing for an ads analyst job on a visual discovery app. The interviewer says: "Advertisers keep asking why one campaign gets twice the clicks of another. Let's see if we can explain it."
ad_events has one row per impression (an ad was shown), click or checkout. The click-through rate (CTR) is clicks divided by impressions. Each user has a top_interest, and each campaign has a category from the same list.
"Show each campaign's impressions, clicks and CTR, best first."
Expected output
ctr as a percentage rounded to 1 decimal; sorted by ctr, highest firstExample of the shape (made-up values):
| campaign_id | impressions | clicks | ctr |
|---|---|---|---|
| 7 | 200 | 30 | 15.0 |
Hints, the later steps and the answer come with Pro.
Step 1 of 3
Write your query here. Ctrl + Enter submits.