Google: Click-Through Rate at Every Organic Position

Medium
681.

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. 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

ColumnType
impression_idint
query_idint
positionint
clickedbool

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.

Example

The rows must come back in the order shown.

SearchImpression

impression_idquery_idpositionclicked
150111
250120
350131
450211
550221
650230
750310
850370
9504121

Output

positionimpressionsclicksctr_pct
13266.67
22150
32150
400NULL
500NULL
600NULL
7100
800NULL
900NULL
1000NULL
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.

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

ColumnType
impression_idint
query_idint
positionint
clickedbool

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_idquery_idpositionclicked
150111
250120
350131
450211
550221
650230
750310
850370
9504121

Output

positionimpressionsclicksctr_pct
13266.67
22150
32150
400NULL
500NULL
600NULL
7100
800NULL
900NULL
1000NULL

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.position

Another 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.position

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