Top Three Event Sequences | Dataford Interview Questions

Munich Re wants to understand common navigation patterns in its Risk Intelligence portal. Each interaction belongs to a session and has an event timestamp. Write a PostgreSQL query that identifies the three most frequent complete event sequences across sessions.

Requirements

Join sessions to their interactions and order events within each session by event_at, using interaction_id as a deterministic tie-breaker.
Represent each session sequence as event names joined with >, excluding interactions whose event name is NULL.
Count how many sessions produced each sequence and return the top three, sorted by frequency descending and sequence ascending to make ties deterministic.

Schema

sessions

Column	Type	Description
session_idPK	INT	Unique portal session identifier
user_id	INT	Munich Re portal user identifier
started_at	TIMESTAMP	Session start timestamp

interactions

Column	Type	Description
interaction_idPK	INT	Unique interaction identifier
session_id	INT	Associated session identifier
event_name	VARCHAR(50)	Risk Intelligence portal event name
event_at	TIMESTAMP	Event occurrence timestamp

Your question is Top Three Event Sequences. 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