SQL for Abandonment Paths | Dataford Interview Questions

Write a SQL query to identify the most common paths users take before abandoning a conversational session at Moveworks.Ai. Use the supplied session, user, and event data. Consider only events occurring before abandonment and return the most frequent complete paths.

Output

One row per distinct path, with path_rank, conversation_path, abandoned_sessions, and distinct_users.
Include abandoned sessions only, return at most 10 paths, and order by session count descending, distinct user count descending, then path alphabetically.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	Display name of the user

sessions

Column	Type	Description
session_idPK	INT	Unique conversational session identifier
user_id	INT	User who initiated the session
status	VARCHAR(20)	Session outcome such as abandoned, completed, or active
started_at	TIMESTAMPTZ	Session start timestamp
abandoned_at	TIMESTAMPTZ	Timestamp when the session was abandoned

session_events

Column	Type	Description
session_event_idPK	INT	Unique event collection identifier
session_id	INT	Session associated with the event collection
events	JSONB	Ordered event objects containing event_type and event_at

Your question is SQL for Abandonment Paths. 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