Most Common Funnel Path | Dataford Interview Questions

Write a SQL query to identify the most common path users take through a funnel. Each user's path should contain the distinct funnel steps they reached, ordered by the funnel's defined step order. Ignore events with an unknown step or missing user ID.

Output

One row per tied most-common path
Columns: path, user_count, and path_rank
Include only paths with the highest user count, with ties included
Sort alphabetically by path

Schema

funnel_steps

Column	Type	Description
step_codePK	VARCHAR(40)	Stable identifier for a funnel step
step_name	VARCHAR(100)	Display name of the funnel step
step_order	INT	Canonical order of the step in the funnel

user_funnel_events

Column	Type	Description
event_idPK	INT	Unique funnel event identifier
user_id	VARCHAR(30)	Identifier of the user who generated the event
step_code	VARCHAR(40)	Funnel step associated with the event
event_at	TIMESTAMP	Timestamp when the event occurred

Your question is Most Common Funnel Path. 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