touches(user_id, channel, touched_at) and conversions(user_id, converted_at, revenue)

For each conversion, return the first and the last marketing channel that user touched strictly before the conversion.

Return user_id, converted_at, first_touch_channel and last_touch_channel, ordered by user then conversion time. A conversion with no earlier touch keeps the row, with NULL in both channel columns.

A user can convert twice, and the second conversion sees the touches that happened between the two, so the window is per conversion rather than per user.

Tables

conversions

user_id  converted_at         revenue
-------  -------------------  -------
1        2026-03-05 10:00:00  40.00
1        2026-03-15 10:00:00  60.00
2        2026-03-18 10:00:00  25.00

touches

user_id  channel  touched_at
-------  -------  -------------------
1        search   2026-03-01 10:00:00
1        email    2026-03-02 10:00:00
1        social   2026-03-10 10:00:00
2        display  2026-03-20 10:00:00

Expected result

user_id  converted_at         first_touch_channel  last_touch_channel
-------  -------------------  -------------------  ------------------
1        2026-03-05 10:00:00  search               email
1        2026-03-15 10:00:00  search               social
2        2026-03-18 10:00:00  NULL                 NULL