First and Last Touch SQL | Dataford Interview Questions

Write a SQL query using window functions to identify the first and last touchpoint of a user's conversion path.

Use the conversion_touchpoints table. Treat an earlier occurred_at as first, and use the lower touchpoint_id to break ties.

Output

conversion_touchpoints

Column	Type	Description
touchpoint_idPK	INT	Unique identifier for the touchpoint event
user_id	INT	Identifier of the user whose path contains the event
touchpoint	VARCHAR(100)	Marketing or product touchpoint associated with the event
occurred_at	TIMESTAMP	Timestamp when the touchpoint occurred

Your question is First and Last Touch SQL. Start with the requirements and the one table 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.

Write a SQL query using window functions to identify the first and last touchpoint of a user's conversion path.

Use the conversion_touchpoints table. Treat an earlier occurred_at as first, and use the lower touchpoint_id to break ties.

conversion_touchpoints

Column	Type	Description
touchpoint_idPK	INT	Unique identifier for the touchpoint event
user_id	INT	Identifier of the user whose path contains the event
touchpoint	VARCHAR(100)	Marketing or product touchpoint associated with the event
occurred_at	TIMESTAMP	Timestamp when the touchpoint occurred

Your question is First and Last Touch SQL. Start with the requirements and the one table 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.