Checkout Funnel Drop-Off Query | Dataford Interview Questions

Given a table of user web sessions, write a query to identify the drop-off rate at each stage of the checkout funnel for Wayfair.

Use all sessions in the table. Treat a session as reaching a stage when its corresponding flag is true. Calculate each stage's drop-off rate relative to the immediately preceding stage.

Output

One row per funnel stage, ordered from landing through purchase
Columns: stage_name, sessions_reaching_stage, drop_off_rate
Return NULL for the first stage's drop-off rate

Schema

web_sessions

Column	Type	Description
session_idPK	INT	Unique identifier for a web session
user_id	INT	Wayfair customer identifier when available
source_channel	VARCHAR(30)	Marketing or traffic source for the session
viewed_product	BOOLEAN	Whether the session viewed a product
added_to_cart	BOOLEAN	Whether the session added an item to cart
started_checkout	BOOLEAN	Whether the session started checkout
entered_payment	BOOLEAN	Whether the session reached payment
completed_order	BOOLEAN	Whether the session completed an order

Your question is Checkout Funnel Drop-Off Query. Start with the requirements and the one table 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