BigQuery Tips: Why LAST_VALUE keeps returning the wrong result

Say you want to find the entry page and exit page for every session in your events table. The natural query writes itself:

SELECT
  CONCAT(ga_session_id, '-', user_pseudo_id) AS session_key,
  event_timestamp,
  page_path,
  FIRST_VALUE(page_path) OVER (
    PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
    ORDER BY event_timestamp
  ) AS entry_page,
  LAST_VALUE(page_path) OVER (
    PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
    ORDER BY event_timestamp
  ) AS exit_page
FROM base

Run this and entry_page works perfectly. exit_page returns the wrong result for every row except the last one in the session. No error, no warning, just quietly incorrect data.

What is actually happening

Both functions use a window defined by PARTITION BY and ORDER BY. What most people do not realise is that ORDER BY also sets a default frame for what those functions consider their window.

The default frame is: from the start of the partition to the current row.

For FIRST_VALUE this does not matter. The first row of the window is always the start of the partition, regardless of how large the frame is. It returns the right value every time.

For LAST_VALUE it matters a lot. With the default frame, the last row of the window is the current row — not the end of the partition. So LAST_VALUE returns the value on the row you are currently on, which is the same as just writing page_path. It is useless, and it looks correct until you check the numbers.

Fix one: extend the frame

Tell LAST_VALUE to look all the way to the end of the partition:

LAST_VALUE(page_path) OVER (
  PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
  ORDER BY event_timestamp
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS exit_page

Now the frame runs from the first row to the last row of the partition, and LAST_VALUE correctly returns the final value.

This works, but ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is long and easy to get wrong. There is a cleaner option.

Fix two: reverse the order and use FIRST_VALUE

FIRST_VALUE always works correctly with the default frame. So instead of fighting LAST_VALUE, reverse the sort order and use FIRST_VALUE:

SELECT
  CONCAT(ga_session_id, '-', user_pseudo_id) AS session_key,
  event_timestamp,
  page_path,
  FIRST_VALUE(page_path) OVER (
    PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
    ORDER BY event_timestamp ASC
  ) AS entry_page,
  FIRST_VALUE(page_path) OVER (
    PARTITION BY CONCAT(ga_session_id, '-', user_pseudo_id)
    ORDER BY event_timestamp DESC
  ) AS exit_page
FROM base

ORDER BY event_timestamp DESC puts the last event at the top of the window. FIRST_VALUE picks it up correctly. No frame clause needed, and the intent is immediately readable.

This is the version most people land on once they have understood the problem.

The thing worth remembering

FIRST_VALUE and LAST_VALUE are not symmetric. FIRST_VALUE works correctly with the default window frame. LAST_VALUE does not. The asymmetry is not a bug, it is how window frames are defined in SQL, but it is not something most people are taught explicitly, which is why it keeps catching people out.

Any time you reach for LAST_VALUE, either write the full frame clause or ask yourself whether reversing the sort and using FIRST_VALUE gets you there more cleanly. The choice is yours.