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.
Part by part
- min_by(event_name, occurred_at)
- Returns the event_name belonging to the row with the smallest occurred_at. It answers "which value" rather than "what minimum", which plain MIN cannot.
- max_by(event_name, occurred_at)
- The same for the largest timestamp. Together they collapse a per-group first-and-last query into a single pass with no window function.
Common mistakes
MIN(event_name), MAX(event_name)Returns the alphabetically first and last event names, which have nothing to do with time. It produces two real values from the group and answers a question nobody asked.
min_by(event_name, occurred_at), max_by(event_name, occurred_at)SELECT DISTINCT ON (user_id) ... ORDER BY user_id, occurred_atPostgres-specific and it gives you the first row only — a second query or a window function is needed for the last. Fine when you need one end; more work when you need both.
Assuming a deterministic result when timestamps tieTwo events at the same instant make the choice arbitrary and potentially different between runs. Add a tiebreak into the ordering column, or accept that the answer is not reproducible.
Questions
Which databases have min_by and max_by?
DuckDB, ClickHouse, BigQuery, Snowflake and Presto family engines. Postgres and MySQL do not, so there the equivalent is a window function with ROW_NUMBER or Postgres's DISTINCT ON.
What is the portable alternative?
ROW_NUMBER partitioned by the group and ordered by the timestamp, ascending and descending, then filter for rank 1 on each. More verbose, and it works everywhere window functions do.
Can I return several columns from the same row?
Call min_by once per column, all ordered by the same timestamp — they resolve to the same row. Where ties are possible that is not guaranteed, and the window-function form is safer.
Is this faster than a window function?
Usually, since it is a single aggregate pass with no sorting of the whole partition. On a large events table the difference is worth having where the function is available.