Mining Common Purchase Sequences | Dataford Interview Questions

Your question is Mining Common Purchase Sequences. Start with the requirements and the one table 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

The monday Data Analytics team wants to understand which actions most often lead to a purchase in monday Work Management. Given ordered user events, write a PostgreSQL query that identifies the most common sequence of the three actions immediately preceding a purchase.

Requirements

Partition event ordering by user_id and session_id, and order events chronologically using event_time and event_id as a tie-breaker.
Consider only purchases that have three preceding non-NULL actions in the same session.
Represent each sequence in chronological order, such as view_board > open_item > invite_member.
Count purchases and distinct users for each exact sequence.
Return every sequence tied for the highest purchase count, including its rank, ordered by purchase count descending and sequence ascending.

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	monday user identifier
session_id	VARCHAR(50)	Session containing the event
event_time	TIMESTAMP	Timestamp when the event occurred
action	VARCHAR(50)	Recorded user action

Your question is Mining Common Purchase Sequences. Start with the requirements and the one table 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.

Requirements

Partition event ordering by user_id and session_id, and order events chronologically using event_time and event_id as a tie-breaker.
Consider only purchases that have three preceding non-NULL actions in the same session.
Represent each sequence in chronological order, such as view_board > open_item > invite_member.
Count purchases and distinct users for each exact sequence.
Return every sequence tied for the highest purchase count, including its rank, ordered by purchase count descending and sequence ascending.

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	monday user identifier
session_id	VARCHAR(50)	Session containing the event
event_time	TIMESTAMP	Timestamp when the event occurred
action	VARCHAR(50)	Recorded user action