BigQuery Lead/Lag Analytical Function

Question

I have a table like below:

I want to sort the port and value and then apply lead function on eventDateTime like below:

I'm able to sort the port and value together but when applying the LEAD function, I'm not getting the correct output for lead function. this is the query I'm using:

SELECT port,value, eventDateTime, LEAD(eventDateTime) OVER (ORDER BY eventDateTime DESC) AS prioreventDateTime FROM (SELECT value, port, MAX(datetime(eventTimestamp)) AS eventDateTime, FROM (SELECT JSON_VALUE(payloadJson,'$.direction') AS direction, JSON_VALUE(payloadJson,'$.port') AS port, JSON_VALUE(payloadJson,'$.value') AS value, eventTimestamp FROM `table_name` WHERE DATE(eventTimestamp) <= current_date) WHERE port IS NOT NULL AND value IS NOT NULL GROUP BY port,value) ORDER BY port,value

Please let me know, if I'm missing something here.

Answer 1

Using your sample data (which doesn't match desired output sample)

WITH
  base AS (
  SELECT
    8 AS port,
    1 AS value,
    DATETIME("2022-05-13T11:00:58.539000") AS event_timestamp
  UNION ALL
  SELECT
    14 AS port,
    1 AS value,
    DATETIME("2022-05-13T11:00:58.610000") AS event_timestamp
  UNION ALL
  SELECT
    4 AS port,
    1 AS value,
    DATETIME("2022-05-13T11:00:58.282000") AS event_timestamp
  UNION ALL
  SELECT
    5 AS port,
    0 AS value,
    DATETIME("2022-05-13T11:00:58.665000") AS event_timestamp
  UNION ALL
  SELECT
    6 AS port,
    1 AS value,
    DATETIME("2022-05-13T11:00:57.054000") AS event_timestamp
  UNION ALL
  SELECT
    14 AS port,
    0 AS value,
    DATETIME("2022-05-13T11:00:57.982000") AS event_timestamp
  UNION ALL
  SELECT
    12 AS port,
    0 AS value,
    DATETIME("2022-05-13T11:00:58.574000") AS event_timestamp
  UNION ALL
  SELECT
    13 AS port,
    0 AS value,
    DATETIME("2022-05-13T11:00:58.315000") AS event_timestamp
  UNION ALL
  SELECT
    17 AS port,
    0 AS value,
    DATETIME("2022-05-13T11:00:57.870000") AS event_timestamp
  UNION ALL
  SELECT
    8 AS port,
    0 AS value,
    DATETIME("2022-05-13T11:00:58.683000") AS event_timestamp
  UNION ALL
  SELECT
    3 AS port,
    0 AS value,
    DATETIME("2022-05-13T11:00:56.706000") AS event_timestamp
  UNION ALL
  SELECT
    15 AS port,
    0 AS value,
    DATETIME("2022-05-13T11:00:58.174000") AS event_timestamp
  UNION ALL
  SELECT
    7 AS port,
    1 AS value,
    DATETIME("2022-05-13T11:00:57.506000") AS event_timestamp
  UNION ALL
  SELECT
    1 AS port,
    1 AS value,
    DATETIME("2022-05-13T11:00:58.247000") AS event_timestamp )
SELECT
  port,
  value,
  event_timestamp,
  LEAD(event_timestamp) OVER(order by port,value asc) AS prior_event_timestamp
FROM
  base
ORDER BY
  port,
  value asc

Is this what you wanted?