lesson 41 Window functions · advanced

LEAD & the next event

Compare an event with the next event in order.

the idea

LEAD looks forward within a partition. Use it to see the next event or compute a gap. The last row has no next event and returns NULL. Deterministic ordering needs a tie-breaker.

tables in this lesson

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

example

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

SELECT
  id,
  viewer_id,
  LEAD(watched_at) OVER( PARTITION BY viewer_id ORDER BY watched_at,id ) AS next_watch_at
FROM watch_events;

Watch for: Using global rather than per-viewer order compares unrelated people.

build it step by step

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

1. 1

   Order a viewer's watch timeline. LEAD looks forward in the same partition. Sort one viewer's events by time to see what “next” means.

   step-1.sql Run

   sql›

   SELECT id, viewer_id, watched_at
   FROM watch_events
   WHERE viewer_id = 1
   ORDER BY watched_at, id;

2. 2

   Read the next event time. LEAD(watched_at) returns the following event's timestamp for that viewer. The last event has NULL for next.

   step-2.sql Run

   sql›

   SELECT
     id,
     viewer_id,
     LEAD(watched_at) OVER( PARTITION BY viewer_id ORDER BY watched_at, id ) AS next_watched_at
   FROM watch_events;