Track First and Most Recent Customer Touchpoints with FIRST_VALUE in SQL

Question

Given `touchpoints` (customer_id, channel, touched_at), write a SQL query using window functions to display each customer's `customer_id`, original first marketing channel `first_channel`, and most recent marketing channel `last_channel`.

Accepted Answer

Optimal Solution:
SELECT DISTINCT
  customer_id,
  FIRST_VALUE(channel) OVER (
    PARTITION BY customer_id 
    ORDER BY touched_at ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_channel,
  LAST_VALUE(channel) OVER (
    PARTITION BY customer_id 
    ORDER BY touched_at ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_channel
FROM touchpoints
ORDER BY customer_id ASC;

Technical Explanation:
`FIRST_VALUE()` captures the initial channel. For `LAST_VALUE()`, specifying `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` is mandatory because the default window frame stops at the current row.

customer_id	channel	touched_at
101	Organic Google	2026-01-01 10:00:00
101	Instagram Ad	2026-01-15 14:00:00
101	Direct Email	2026-02-01 09:00:00