Joining Tables for Conversion Rate | Dataford Interview Questions

Write a SQL query to join three tables and calculate the conversion rate of a specific user flow at Id.Me.

Use the provided users, verification_flows, and flow_events tables. For the signup_to_verified flow, count each user at most once as started or completed. Calculate conversion rate as completed users divided by started users, expressed as a percentage.

Output

One row for the requested flow
Columns: flow_name, started_users, completed_users, and conversion_rate
Return the percentage rounded to two decimal places and order by flow_name

Schema

users

Column	Type	Description
user_idPK	INT	Unique Id.Me user identifier
account_type	VARCHAR(30)	User account classification
created_at	DATE	Date the user account was created

verification_flows

Column	Type	Description
flow_idPK	INT	Unique verification flow identifier
flow_name	VARCHAR(80)	Business name of the verification flow
is_active	BOOLEAN	Whether the flow is currently active

flow_events

Column	Type	Description
event_idPK	INT	Unique flow event identifier
user_id	INT	User associated with the event
flow_id	INT	Verification flow associated with the event
event_name	VARCHAR(30)	Event type recorded in the flow
event_at	TIMESTAMP	Timestamp when the event occurred

Your question is Joining Tables for Conversion Rate. Start with the requirements and the three 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