Conversion Rate Per City | Dataford Interview Questions

Practo wants to compare how effectively its healthcare discovery platform converts registered users into users who make at least one booking. Write a PostgreSQL query to calculate the booking conversion rate for each user city.

Requirements

Use users as the denominator population so cities with no bookings are included.
Count distinct users who have at least one booking as converted users. Multiple bookings by the same user must count only once.
Return the total users, converted users, and conversion rate as a percentage rounded to two decimal places.
Display missing cities as Unknown and sort the output alphabetically by city.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique Practo user identifier
city	VARCHAR(100)	User's registered city

bookings

Column	Type	Description
booking_idPK	INTEGER	Unique booking identifier
user_id	INTEGER	User associated with the booking

Your question is Conversion Rate Per City. 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