SQL Attribution First vs Last Touch | Dataford Interview Questions

Write a SQL query to compare first-touch and last-touch attribution across marketing channels. Attribute each conversion to the earliest and latest marketing touch from the same customer within the preceding 30 days, using touch time and touch ID to break ties. Include channels with no attributed conversions.

Output

One row per marketing channel with columns channel_name, first_touch_conversions, first_touch_revenue, last_touch_conversions, last_touch_revenue, and revenue_difference
Include all channels, including zero-attribution channels
Order by channel_name ascending

Schema

channels

Column	Type	Description
channel_idPK	INT	Unique marketing channel identifier
channel_name	VARCHAR(100)	Marketing channel name

marketing_touchpoints

Column	Type	Description
touch_idPK	INT	Unique marketing touch identifier
customer_id	INT	Customer associated with the touch
channel_id	INT	Marketing channel associated with the touch
touch_timestamp	TIMESTAMP	Time the customer interacted with the channel

conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion identifier
customer_id	INT	Customer who converted
conversion_timestamp	TIMESTAMP	Time the conversion occurred
revenue	NUMERIC(10,2)	Revenue generated by the conversion

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