First and Last Order Pivot — Solution & ExplanationProblems / First and Last Order Pivot / EditorialApproachPer customer we need four values taken from two specific rows — the earliest and the latest order:

MIN(order_ts) / MAX(order_ts) give the endpoint timestamps directly.

The channels must come from *those same rows*. DuckDB's arg_min(channel, order_ts) returns the channel value on the row where order_ts is minimal — the "argument at the minimum" — and arg_max mirrors it (min_by / max_by are aliases). Unique timestamps per customer make the answer deterministic.

GROUP BY customer_id, ORDER BY customer_id.

In SQLFrame/PySpark there is no native arg_min; F.expr("arg_min(channel, order_ts)") inside .agg() reaches the engine function. The classical portable alternative is row_number() over (PARTITION BY customer_id ORDER BY order_ts) ascending and descending, then conditional aggregation on the rn = 1 rows.Key InsightMIN(channel) is the classic wrong answer: it takes the alphabetical minimum, which has nothing to do with time — customer 101's first order came through 'web', but their alphabetically-first channel is 'app'. arg_min / arg_max express "the value of column A at the extreme of column B" in a single aggregate — the same pattern as "top product per store", with no window function in sight.Hints

MIN(order_ts) / MAX(order_ts) give the endpoint timestamps, but the channels must come from those same rows — MIN(channel) is merely alphabetical and wrong.

DuckDB's arg_min(channel, order_ts) returns the channel of the row where order_ts is smallest — 'the argument at the minimum'. arg_max is the mirror; min_by/max_by are aliases.

In PySpark/SQLFrame there is no native arg_min function: use F.expr('arg_min(channel, order_ts)') inside .agg(), or a Window row_number() ordered ascending/descending and pick the rn = 1 rows with conditional aggregation.

Solve First and Last Order Pivot yourself →