SQL for User Navigation Path | Dataford Interview Questions

Your question is SQL for User Navigation Path. 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

Discover's analytics team wants to review how a user navigated the Discover.com account experience. Write a PostgreSQL query that reconstructs the selected user's interaction sequence from raw page events.

Requirements

Join the interaction records to the user's Discover profile and filter for user_id = 101.
Order events chronologically, using interaction_id as a tie breaker, and assign each event a navigation step number.
Show the previous action and the elapsed seconds since the previous interaction using a window function.
Preserve events where action is NULL, since an event without a captured action is still part of the navigation path.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Discover user identifier
user_name	VARCHAR(100)	User display name
account_status	VARCHAR(20)	Current account status

page_interactions

Column	Type	Description
interaction_idPK	INTEGER	Unique interaction identifier
user_id	INTEGER	References users.user_id
event_time	TIMESTAMPTZ	Timestamp of the page interaction
page_name	VARCHAR(80)	Discover.com page or surface
action	VARCHAR(80)	Captured action, which may be NULL