First and latest value per group
The first and most recent event per user in one pass, using DuckDB's min_by/max_by instead of a window function and a filter.
Last updated
SELECT user_id, min_by(event_name, occurred_at) AS first_event, max_by(event_name, occurred_at) AS latest_event FROM events GROUP BY user_id;
Why it’s written this way
min_by(event_name, occurred_at) returns the event_name from whichever row in the group has the smallest occurred_at — it's an aggregate that picks a value based on a different column's ordering, rather than aggregating the column it returns the way MIN() would. max_by does the same for the largest occurred_at. Both run as ordinary GROUP BY aggregates, with no subquery or window function needed.
This is DuckDB's spelling; the same idea appears as arg_min/arg_max in DuckDB as well and under other names in other engines, but the underlying mechanism — pick this column's value at the row where that other column is extremal — is identical wherever it's offered.
Edge cases to know
- →Where an engine has neither min_by nor max_by (standard MySQL, Postgres without an extension), the portable alternative is two ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY occurred_at ASC/DESC) subqueries each filtered to rn = 1 — more verbose, but it works everywhere.
- →A tie in occurred_at (two events at the identical timestamp) resolves arbitrarily, the same way an unqualified ROW_NUMBER() would — add a tiebreaker column to the comparison if determinism matters.
- →Rows with a NULL occurred_at are excluded from consideration entirely, so a user whose only rows have a NULL timestamp comes back with NULL for both first_event and latest_event.