lesson 41 Window functions · advanced

LEAD & the next event

Compare an event with the next event in order.

the idea

LEAD(column) reads the next row in the window order. LEAD(recorded_at) gives the time of the next reading, so the difference measures the gap. Deduplicate first: a retransmitted reading repeats a timestamp and would create a zero gap.

Dataset: IoT / Sensors

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Peek at the next reading. Run this. LEAD() shows when the same device reported again—subtract times later to measure the gap between readings.the pattern

   example.sql Run

   sql›

   SELECT
     reading_id,
     device_id,
     recorded_at,
     LEAD(recorded_at) OVER (PARTITION BY device_id ORDER BY recorded_at, reading_id) AS next_recorded_at
   FROM sensor_readings
   WHERE device_id = 1
     AND recorded_at >= '2025-03-10'
     AND recorded_at < '2025-03-11';

2. 2

   Spot duplicate deliveries. Run this. Rows with copies > 1 are the same reading sent twice; dedupe before you trust LEAD gaps.

   step-2.sql Run

   sql›

   SELECT device_id, sequence_number, recorded_at, COUNT(*) AS copies
   FROM sensor_readings
   GROUP BY device_id, sequence_number, recorded_at
   HAVING COUNT(*) > 1
   LIMIT 10;

common mistake

The last reading of each device has no next reading, so LEAD returns NULL; treat it as unknown, not as a zero gap.