Given a table of event logs, identify the first and last interaction for each user using common table expressions.
Use occurred_at to determine chronological order. Ignore rows where occurred_at is NULL. If a user has multiple events at the same timestamp, use the smallest interaction_id as the first event and the largest as the last event.
user_id, first_interaction_at, first_event_type, last_interaction_at, last_event_type.user_id ascending.| Column | Type | Description |
|---|---|---|
| interaction_idPK | INT | Unique identifier for the interaction |
| user_id | INT | Identifier of the user who generated the event |
| occurred_at | TIMESTAMPTZ | Timestamp when the interaction occurred |
| event_type | VARCHAR(40) | Type of user interaction |
| surface | VARCHAR(40) | Product surface where the event occurred |