First and Last Values in a Window

The First Order Problem

For each customer, I wanted to show every order alongside the date of their *first-ever* order.

`FIRST_VALUE(column)`: Returns the value from the first row in the window.

`LAST_VALUE(column)`: Returns the value from the last row (be careful with the frame!).

SELECT

customer_id, order_date, amount,

FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order_date

FROM orders;

`LAST_VALUE` often returns the current row's value because the default frame ends at the current row. To get the true last value:

LAST_VALUE(amount) OVER (

PARTITION BY customer_id ORDER BY order_date

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

)

*Day 66: NTILE—Dividing Data into Buckets.*