SQL First-Touch Attribution | Dataford Interview Questions

Given a table of Zendesk marketing campaigns and a table of user conversions, write a query to identify the first-touch campaign for each converted user.

Assume each user has at most one conversion record. Include only campaign touches occurring at or before the conversion, and exclude converted users with no qualifying campaign touch.

Output

One row per converted user with a qualifying touch.
Return user_id, conversion_id, first_touch_campaign_id, first_touch_campaign_name, and first_touch_at.
Order by user_id ascending. If timestamps tie, choose the lowest campaign ID.

Schema

zendesk_marketing_campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign touch identifier
user_id	INT	User associated with the campaign touch
campaign_name	VARCHAR(150)	Zendesk marketing campaign name
touch_at	TIMESTAMP	Timestamp when the user touched the campaign

user_conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion identifier
user_id	INT	User who converted
converted_at	TIMESTAMP	Timestamp when the user converted

Your question is SQL First-Touch Attribution. 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