CTR by Region and Device | Dataford Interview Questions

Yelp Ads needs a performance report for advertiser 101. Write a PostgreSQL query that calculates click-through rates for impressions served during June 2024, segmented by geographic region and device type.

Requirements

LEFT JOIN the impression and click logs so impressions without clicks remain in the report.
Return one row per region and device type, including NULL dimensions as Unknown.
Calculate total impressions, distinct clicked impressions, and CTR as a percentage rounded to two decimals. Avoid counting multiple click events for one impression more than once.
Sort by region and device type, with NULL source dimensions last.

Schema

ad_impressions

Column	Type	Description
impression_idPK	INT	Unique impression identifier
advertiser_id	INT	Yelp Ads advertiser identifier
region	VARCHAR(50)	Geographic region where the impression occurred
device_type	VARCHAR(20)	Device used to view the advertisement
impression_ts	TIMESTAMP	Timestamp when the ad impression occurred

ad_clicks

Column	Type	Description
click_idPK	INT	Unique click event identifier
impression_id	INT	Impression associated with the click
clicked_at	TIMESTAMP	Timestamp when the click occurred

Your question is CTR by Region 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