CTR by Position

ASKED IN INTERVIEW

4pts

Google

Problem Statement

Google Search tracks every time a result is shown (Impression) and every time it is clicked (Click). To evaluate ranking effectiveness, we need to see the CTR for the top 10 positions.

Write an SQL query to calculate the CTR for each position.

Rules:

CTR is defined as: (Total Clicks / Total Impressions) * 100.
Only analyze positions 1 through 10.
If a position has impressions but zero clicks, the CTR should be 0.00.
Output columns: search_position, total_impressions, total_clicks, ctr.
Round ctr to 2 decimal places.
Results must be returned in ascending order of search_position.

Table Schema:

Impressions: search_id, result_id, search_position (INTEGER).
Clicks: click_id, search_id, result_id, click_timestamp.

Tests your understanding of

Joins, Aggregation, Arithmetic and Conditional Logic

Input Tables

Impressions

search_id(INTEGER)	search_position(INTEGER)
1	1
1	2
2	1
2	2
3	1
3	10
4	1

Clicks

search_id(INTEGER)	search_position(INTEGER)
1	1
2	1
3	10

Expected Output

search_position(INTEGER)	total_impressions(INTEGER)	total_clicks(INTEGER)	ctr(DECIMAL)
1	4	2	50
2	2	0	0
10	1	1	100