Click-Through Rate of Every Ad Group — SQL Problem
The ads platform exports one row per ad group per day with that day's impressions and clicks.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #274
Problem statement
The ads platform exports one row per ad group per day with that day's impressions and clicks. The account manager wants the lifetime click-through rate of each ad group.
Return one row per ad group with ad_group_id, impressions (the total), clicks (the total) and ctr_pct = 100 × total clicks ÷ total impressions, rounded to 2 decimals. An ad group whose total impressions are 0 has ctr_pct NULL. Return the rows in any order.
Tables
Table: AdGroupDailyStat
| Column | Type |
|---|---|
| ad_group_id | int |
| stat_date | date |
| impressions | int |
| clicks | int |
Primary key: ad_group_id, stat_date.
One row per ad group per day it was eligible to serve. clicks never exceeds impressions.
Examples
Example 1
AdGroupDailyStat
| ad_group_id | stat_date | impressions | clicks |
|---|---|---|---|
| 11 | 2024-02-01 | 1200 | 36 |
| 11 | 2024-02-02 | 800 | 28 |
| 12 | 2024-02-01 | 0 | 0 |
| 12 | 2024-02-02 | 0 | 0 |
| 13 | 2024-02-01 | 4500 | 60 |
| 14 | 2024-02-02 | 300 | 9 |
Output
| ad_group_id | impressions | clicks | ctr_pct |
|---|---|---|---|
| 11 | 2000 | 64 | 3.2 |
| 12 | 0 | 0 | NULL |
| 13 | 4500 | 60 | 1.33 |
| 14 | 300 | 9 | 3 |
How to solve Click-Through Rate of Every Ad Group
Group the daily rows by ad_group_id and sum both counters. The click-through rate of the whole period is the ratio of the totals, 100 × SUM(clicks) / SUM(impressions). Averaging the daily rates instead would give a day with 50 impressions the same weight as a day with 5,000, which is not what CTR means.
An ad group that never served has a total of 0 impressions. NULLIF(SUM(impressions), 0) turns that zero into NULL, and any arithmetic with NULL is NULL, so the rate comes out NULL without an error in any engine. A CASE on the total, as in the alternative, says the same thing explicitly.
Round last, on the exact ratio. The query is one pass over the table with a hash aggregate on the ad group.
Reference solution (MySQL)
SELECT ad_group_id,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
ROUND(100 * SUM(clicks) / NULLIF(SUM(impressions), 0), 2) AS ctr_pct
FROM AdGroupDailyStat
GROUP BY ad_group_idAnother way
SELECT ad_group_id, SUM(impressions) AS impressions, SUM(clicks) AS clicks,
CASE WHEN SUM(impressions) = 0 THEN NULL ELSE ROUND(SUM(clicks) * 100 / SUM(impressions), 2) END AS ctr_pct
FROM AdGroupDailyStat GROUP BY ad_group_id← Ad Campaigns Still Live at the Quarter Close · Campaigns That Got Clicks but No Conversions →