CTR by Country and Device | Dataford Interview Questions

Given a table of ad impressions and a table of user clicks, write a query to find the click-through rate (CTR) grouped by country and device type on Snapchat.

CTR is the percentage of distinct impressions that received at least one click. Include impression groups with no clicks, and do not count duplicate clicks on the same impression more than once.

Output

One row per country and device type, with country, device_type, impression_count, clicked_impression_count, and ctr_percent.
Include groups with zero clicks. Ignore click records with a NULL or unmatched impression ID.
Sort by country and device type ascending.

Schema

ad_impressions

Column	Type	Description
impression_idPK	INTEGER	Unique identifier for an ad impression
country	VARCHAR(100)	Country where the impression occurred
device_type	VARCHAR(50)	Device category for the impression

user_clicks

Column	Type	Description
click_idPK	INTEGER	Unique identifier for a click event
impression_id	INTEGER	Impression associated with the click, when available

Your question is CTR by Country and Device. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem