FIRST_VALUE & LAST_VALUE: Retrieving Endpoints

It's **Day 69**, and we're looking for the beginning and the end.

The Concept

Sometimes شما don't want to join to find the "First order date." You just want it available on every row of the current query.

Finding the Original Source

"For every conversion, show me what the VERY FIRST page this user ever visited was."

SELECT

user_id,

visited_at,

page_url,

FIRST_VALUE(page_url) OVER(

PARTITION BY user_id

ORDER BY visited_at

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) as landing_page

FROM page_views;

The "Window Trap"

By default, `FIRST_VALUE` only looks at the start of the window up to the current row. If you want the true first/last value of the *entire* group, you must include the `ROWS BETWEEN UNBOUNDED...` frame specification.

When to use LAST_VALUE

Your Task for Today

Find the very first purchase price for every customer in your orders table.

*Day 70: Why Window Functions Are Fast (And When They Aren't).*