Google: Click-Through Rate at Every Organic Position
A Search ranking analyst plots click-through rate against the ten organic positions of the first results page.
- Difficulty: Medium
- Topics: Aggregation, Subqueries
- Dialect: MySQL
- Problem: #681
Problem statement
A Search ranking analyst plots click-through rate against the ten organic positions of the first results page. The chart needs all ten positions, even ones that got no impressions in the sample.
Return one row for each position 1 to 10: position, impressions (rows at that position), clicks (those with clicked = 1) and ctr_pct = 100 × clicks ÷ impressions rounded to 2 decimals. A position with no impressions shows 0 impressions, 0 clicks and a NULL ctr_pct. Impressions on later pages (position above 10) are ignored. Order by position.
A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.
Tables
Table: SearchImpression
| Column | Type |
|---|---|
| impression_id | int |
| query_id | int |
| position | int |
| clicked | bool |
Primary key: impression_id.
One organic result shown for one query. position is 1-based (11 and above are on page two or later); clicked is 1 if the user clicked it.
Examples
Example 1
SearchImpression
| impression_id | query_id | position | clicked |
|---|---|---|---|
| 1 | 501 | 1 | 1 |
| 2 | 501 | 2 | 0 |
| 3 | 501 | 3 | 1 |
| 4 | 502 | 1 | 1 |
| 5 | 502 | 2 | 1 |
| 6 | 502 | 3 | 0 |
| 7 | 503 | 1 | 0 |
| 8 | 503 | 7 | 0 |
| 9 | 504 | 12 | 1 |
Output
| position | impressions | clicks | ctr_pct |
|---|---|---|---|
| 1 | 3 | 2 | 66.67 |
| 2 | 2 | 1 | 50 |
| 3 | 2 | 1 | 50 |
| 4 | 0 | 0 | NULL |
| 5 | 0 | 0 | NULL |
| 6 | 0 | 0 | NULL |
| 7 | 1 | 0 | 0 |
| 8 | 0 | 0 | NULL |
| 9 | 0 | 0 | NULL |
| 10 | 0 | 0 | NULL |
How to solve Google: Click-Through Rate at Every Organic Position
Grouping SearchImpression by position can only produce positions that occur in it, but the chart needs all ten. The fix is a driving table of the positions themselves: a recursive CTE starts at 1 and adds one until 10. Every position then exists as a row, and the impressions are LEFT JOINed onto it. Impressions at 11 or beyond have no partner in the list and simply never join, which is how page-two results are ignored.
After the join, an empty position is one row of NULL impression columns. COUNT(i.impression_id) skips the NULL and gives 0 — COUNT(*) would wrongly give 1. SUM(i.clicked) is NULL there, so COALESCE turns the clicks into 0, and the CTR expression divides NULL by 0, which is NULL in MySQL, as the statement asks.
The ten numbers can also be written as a UNION ALL derived table, and the aggregation can be done first and joined to the list afterwards. All approaches read the impressions once.
Reference solution (MySQL)
WITH RECURSIVE slots (position) AS (
SELECT 1
UNION ALL
SELECT position + 1 FROM slots WHERE position < 10
)
SELECT s.position,
COUNT(i.impression_id) AS impressions,
COALESCE(SUM(i.clicked), 0) AS clicks,
ROUND(100 * SUM(i.clicked) / COUNT(i.impression_id), 2) AS ctr_pct
FROM slots s
LEFT JOIN SearchImpression i ON i.position = s.position
GROUP BY s.position
ORDER BY s.positionAnother way
SELECT s.position, COUNT(i.impression_id) AS impressions, SUM(CASE WHEN i.clicked = 1 THEN 1 ELSE 0 END) AS clicks, ROUND(100 * SUM(CASE WHEN i.clicked = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(i.impression_id), 0), 2) AS ctr_pct FROM (SELECT 1 AS position UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) s LEFT JOIN SearchImpression i ON i.position = s.position GROUP BY s.position ORDER BY s.positionAnother way
WITH RECURSIVE slots (position) AS (SELECT 1 UNION ALL SELECT position + 1 FROM slots WHERE position < 10), agg AS (SELECT position, COUNT(*) AS n, SUM(clicked) AS c FROM SearchImpression WHERE position <= 10 GROUP BY position) SELECT s.position, COALESCE(a.n, 0) AS impressions, COALESCE(a.c, 0) AS clicks, ROUND(100 * a.c / a.n, 2) AS ctr_pct FROM slots s LEFT JOIN agg a ON a.position = s.position ORDER BY s.position← Google: Chromebooks Not Synced in 90 Days · Google: Drive Files Shared Outside the Owner's Domain →