Click-Through Rate of Every Ad Group

Easy
274.

Click-Through Rate of Every Ad Group

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

ColumnType
ad_group_idint
stat_datedate
impressionsint
clicksint

Primary key (together): ad_group_id, stat_date.

One row per ad group per day it was eligible to serve. clicks never exceeds impressions.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

AdGroupDailyStat

ad_group_idstat_dateimpressionsclicks
112024-02-01120036
112024-02-0280028
122024-02-0100
122024-02-0200
132024-02-01450060
142024-02-023009

Output

ad_group_idimpressionsclicksctr_pct
112000643.2
1200NULL
134500601.33
1430093
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
ad_group_idint
stat_datedate
impressionsint
clicksint

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_idstat_dateimpressionsclicks
112024-02-01120036
112024-02-0280028
122024-02-0100
122024-02-0200
132024-02-01450060
142024-02-023009

Output

ad_group_idimpressionsclicksctr_pct
112000643.2
1200NULL
134500601.33
1430093

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_id

Another 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 →