lesson 42 Window functions · advanced

FIRST_VALUE, LAST_VALUE & frames

Control which rows a window includes when choosing first and last values.

the idea

FIRST_VALUE and LAST_VALUE read endpoints of a window frame. LAST_VALUE with the default ordered frame often returns the current row. Use UNBOUNDED FOLLOWING to reach the final row in the partition.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

watch_events5 columns · 26 rows

idinteger · key	viewer_idinteger	video_idinteger	watched_attext	watch_secondsinteger
1	1	1	2025-01-01 10:00:00	300
2	1	2	2025-01-01 11:00:00	450
3	2	1	2025-01-02 12:00:00	600
4	3	3	2025-01-02 13:00:00	120
5	1	4	2025-01-02 14:00:00	700

showing 5 of 26 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT
  id,
  viewer_id,
  LAST_VALUE(video_id) OVER( PARTITION BY viewer_id ORDER BY watched_at,id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_video
FROM watch_events;

Watch for: The default frame is not always the entire partition.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Follow one viewer's video sequence. LAST_VALUE needs an explicit frame to see the end of the partition as rows accumulate.
step-1.sql
sql›
```
SELECT id, viewer_id, video_id, watched_at
FROM watch_events
WHERE viewer_id = 1
ORDER BY watched_at, id;
```

Carry the last video with a frame. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING lets LAST_VALUE see the whole partition for each row.

step-2.sql

SELECT
  id,
  viewer_id,
  LAST_VALUE(video_id) OVER( PARTITION BY viewer_id ORDER BY watched_at, id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_video
FROM watch_events;