Promoted Click Conversion Rate | Dataford Interview Questions

Given a table of user clicks and a table of purchases, write a query to find the conversion rate of users who clicked on a promoted item on Wish. Count a user as converted only when they purchased the same item after clicking it. Treat each user once in the denominator and numerator.

Output

Return one row with clicked_users, converted_users, and conversion_rate_pct.
Report the conversion rate as a percentage, rounded to two decimal places.
The result must include promoted-item clickers even when they made no qualifying purchase.

Schema

user_clicks

Column	Type	Description
click_idPK	INT	Unique click identifier
user_id	INT	Wish user who clicked
item_id	INT	Clicked item identifier
clicked_at	TIMESTAMP	Time of the click
is_promoted	BOOLEAN	Whether the item was promoted

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
user_id	INT	User who made the purchase
item_id	INT	Purchased item identifier
purchased_at	TIMESTAMP	Time of the purchase

Your question is Promoted Click Conversion Rate. 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