Differences Between Row In Google Big Query

I'm currently attempting to calculate differences between rows in google big query. I actually have a working query. SELECT id, record_time, level, lag, (level - lag) as d

Solution 1:

It works fine for me. Maybe you forgot to enable standard SQL? Here is an example:

WITH Input AS (
  SELECT 1 AS id, TIMESTAMP '2017-10-17 00:00:00' AS record_time, 2 AS level UNION ALL
  SELECT 2, TIMESTAMP '2017-10-16 00:00:00', 3 UNION ALL
  SELECT 1, TIMESTAMP '2017-10-16 00:00:00', 4
)
SELECT
 id, record_time, level, lag,
 (level - lag) as diff
FROM (
  SELECT
    id, record_time, level, 
    LAG(level) OVER (ORDER BY id, record_time) as lag
  FROM Input
)
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2 ASC;