Great information, thanks for that.

To me it was not clear immediately, what the difference between the MAX Aggregate Window Function and LAST_VALUE(X) was. In the end MAX(x) OVER (PARTITION BY y) should return the same as FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY X DESC)

But obviously, the MAX / MIN Aggregate Window Functions do not allow you to order your partition by a different column before applying the aggregate. (And it actually does not really make sense to impose a different order on MAX or MIN.)

So the whole point of FIRST_VALUE and LAST_VALUE seem to be:

FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY SomeOtherColumn DESC)

Any other points I may have missed?