Fauzaan Big query - Engine off and on times

SELECT hour, round(lat, 4) as lat, round(lng, 4) as lng, count (*) as total from (
  SELECT *,
       LAG(engine,1) OVER
          (PARTITION BY carId ORDER BY createdAt) AS last_engine,
       LAG (engine, 2) OVER
          (PARTITION BY carId ORDER BY createdAt) as next_engine,
          EXTRACT(HOUR FROM createdAt) as hour
  FROM `my_socar_csa.csa_event`
)
WHERE engine = "ON"
AND next_engine = "OFF"
AND createdAt > '2020-1-1'

AND lat BETWEEN 2.9036133 AND 2.9398435
AND lng BETWEEN  101.6292075 AND 101.6619699
GROUP BY lat, lng, hour
having total < 60 and total > 3
order by total desc;



##############################################################

Removing all filters

SELECT hour, round(lat, 4) as lat, round(lng, 4) as lng, count (*) as total from (
  SELECT *,
       LAG(engine,1) OVER
          (PARTITION BY carId ORDER BY createdAt) AS last_engine,
       LAG (engine, 2) OVER
          (PARTITION BY carId ORDER BY createdAt) as next_engine,
          EXTRACT(HOUR FROM createdAt) as hour
  FROM `my_socar_csa.csa_event`
)
WHERE engine = "ON"
AND next_engine = "OFF"
AND createdAt > '2020-3-17'


GROUP BY lat, lng, hour
order by total desc;