BigQuery - Find Rows Where A Value Has Changed Then Changed Back

Find AssetIds in a table where a value has changed and then changed back again.

SELECT
  *
FROM (
  SELECT
    FromDateTime,
    AssetId,
    Value,
    LAG(Value)  OVER(PARTITION BY AssetId ORDER BY FromDateTime) AS Prev_Value,
    LEAD(Value) OVER(PARTITION BY AssetId ORDER BY FromDateTime) AS Next_Value
  FROM
    `<database>.<schema>.<table>`
) t
WHERE
  Value <> Prev_Value AND
  Value <> Next_Value AND
  Prev_Value = Next_Value
ORDER BY
  AssetId,
  FromDateTime