SQL Conversion Rate by Region

MediumSQL · PostgreSQL

Problem

Xiaomi Operations needs to evaluate the regional performance of the Xiaomi 15 product launch. Write a PostgreSQL query that calculates the percentage of exposed users who purchased the launched product during the launch window.

Requirements

Analyze only the Xiaomi 15 launch and exposures within its launch window.
Group results by region, labeling a missing region as Unknown.
Count each exposed user once per region, even if the user received multiple exposures.
Count a user as converted only when they purchased the Xiaomi 15 after exposure and before the launch window ends. Return exposure count, converted-user count, and conversion rate rounded to two decimals, ordered by region.

Output details

Return columns named region, exposed_users, converted_users, and conversion_rate, in that order.
Order rows by region ascending.
Treat launch-window starts as inclusive and ends as exclusive for exposures and purchases.
Treat a purchase timestamp equal to exposure time as qualifying.
Express conversion_rate as a percentage by multiplying the converted-user proportion by 100.

Schema

product_launches

Column	Type	Description
launch_idPK	INT	Unique product launch identifier
product_id	INT	Product identifier
product_name	VARCHAR(100)	Product name
launch_start	TIMESTAMP	Inclusive start of the launch window
launch_end	TIMESTAMP	Exclusive end of the launch window

launch_exposures

Column	Type	Description
exposure_idPK	INT	Unique exposure identifier
launch_id	INT	References product_launches.launch_id
user_id	INT	User exposed to the launch
region	VARCHAR(50)	User region
exposed_at	TIMESTAMP	Time of product exposure

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
user_id	INT	User who made the purchase
product_id	INT	Purchased product identifier
purchased_at	TIMESTAMP	Purchase timestamp

Tablesproduct_launcheslaunch_exposurespurchases