First and last touch attribution

Marketing wants to know which channel introduced a customer and which one was in front of them when they bought.

Input

touches touch_id bigint -- unique conversion_id bigint channel string -- 'SEARCH', 'EMAIL', 'SOCIAL', 'DIRECT', 'AFFILIATE' touched_at string -- ISO-8601 UTC, 'YYYY-MM-DDTHH:MM:SSZ'

Output

One row per conversion_id, with the columns:

conversion_id
first_channel — the channel of the earliest touch
last_channel — the channel of the latest touch
touch_count

Order touches by touched_at; where two share one, the smaller touch_id is the earlier.

Example

touch_id	conversion_id	channel	touched_at
1	100	SEARCH	2024-01-01T10:00:00Z
2	100	EMAIL	2024-01-02T10:00:00Z
3	100	DIRECT	2024-01-03T10:00:00Z

conversion_id	first_channel	last_channel	touch_count
100	SEARCH	DIRECT	3

Constraints

A conversion with one touch has that channel as both first and last.
last_value over an ordered window without an explicit frame returns the current row, not the last one. It is right on the final row of each partition and wrong on every other, which is why it usually looks correct.
Row order is not graded. Column names and their order are.

Community-reported interview topic. Not an official company question and no affiliation is implied.