Sequence Search Behavior Mining | Dataford Interview Questions

Write a query using common table expressions to identify customers who have exhibited a specific sequence of search behaviors on W.W. Grainger’s site.

For this exercise, the sequence is a search for safety glasses, followed within 7 days by a search for ANSI Z87, followed within 24 hours by a product click. Return each customer’s earliest qualifying sequence.

Output

One row per qualifying customer with customer_id, email, initial_search_at, refined_search_at, clicked_at, and product_sku.
Exclude customers without the complete chronological sequence. Order by customer_id.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
email	VARCHAR(255)	Customer email address

search_events

Column	Type	Description
event_idPK	INT	Unique search or click event identifier
customer_id	INT	Customer associated with the event
event_type	VARCHAR(20)	Event type, such as search or click
query_text	VARCHAR(255)	Search text for search events
product_sku	VARCHAR(50)	Clicked product SKU
event_ts	TIMESTAMP	Event timestamp

Your question is Sequence Search Behavior Mining. 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