Conversion Rate by User Segment | Dataford Interview Questions

Amazon DSP analysts need to compare advertising performance across audience segments. Using users and impression-level events, write a PostgreSQL query that calculates the user conversion rate during June 2025.

Requirements

Return every user segment, including segments with no impressions during the period. Label users with a missing segment as Unclassified.
Calculate distinct exposed users, distinct converted users, and conversion rate per segment. An impression with clicked = TRUE counts as a conversion.
Restrict events to June 1 through June 30, 2025, and return conversion rate as converted users divided by exposed users. Return 0.0000 when no users were exposed.
Order results alphabetically by segment.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_segment	VARCHAR(30)	Amazon DSP audience segment

ad_impressions

Column	Type	Description
impression_idPK	INT	Unique impression identifier
user_id	INT	User who received the impression
campaign_name	VARCHAR(100)	Amazon DSP campaign name
impression_date	DATE	Date when the impression occurred
clicked	BOOLEAN	Whether the impression produced a click

Your question is Conversion Rate by User Segment. 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