Analyze Ecommerce Conversion Funnel | Dataford Interview Questions

Your question is Analyze Ecommerce Conversion Funnel. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

NovaCart wants to measure how users move through a simple purchase funnel: visit → checkout → purchase. Write a SQL query to calculate funnel performance by visit date.

Your query should use the event stream and user data to report how many distinct users reached each stage on each day, along with conversion rates between stages.

Requirements

For each visit_date, count distinct users who had a visit, then a checkout, then a purchase on the same day.
A user should count at a later stage only if they also reached the earlier stage that day.
Return visit_date, visitors, checkout_users, purchase_users, visit_to_checkout_rate, and checkout_to_purchase_rate.
Include only users from the web acquisition channel.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(20)	Channel that acquired the user
country	VARCHAR(20)	User country

events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_name	VARCHAR(20)	Event type in the funnel
event_time	TIMESTAMP	Timestamp of the event
session_id	VARCHAR(20)	Session identifier