Top Interaction Sequences in SQL | Dataford Interview Questions

Given a table of patient touchpoints, how would you write a query to identify the top three most frequent interaction sequences leading to a treatment decision?

Treat a sequence as the non-null touchpoint types for one decision, ordered by touchpoint order. Include only touchpoints occurring on or before the decision date.

Output

Return one row per selected sequence with sequence_rank, interaction_sequence, and decision_count.
Include the three most frequent distinct sequences, ordered by count descending and sequence text ascending to break ties.

Schema

patient_touchpoints

Column	Type	Description
touchpoint_idPK	INT	Unique touchpoint identifier
patient_id	INT	Patient associated with the touchpoint
decision_id	INT	Treatment decision associated with the touchpoint
touchpoint_type	VARCHAR(100)	Type of patient interaction
occurred_at	DATE	Date the interaction occurred
touchpoint_order	INT	Order of the interaction within the decision journey

treatment_decisions

Column	Type	Description
decision_idPK	INT	Unique treatment decision identifier
patient_id	INT	Patient receiving the decision
decision_date	DATE	Date of the treatment decision
treatment_name	VARCHAR(100)	Treatment selected at the decision

Your question is Top Interaction Sequences in SQL. 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