'Syntax error: Expected ")" but got keyword NEW at [2:93]

Can anyone help with how I need to change my query please? I am new to BigQuery

  with W as (
        select COALESCE(UNIX_TIMESTAMP(startTime) - UNIX_TIMESTAMP(LAG(startTime, 1) over UW) > 1000, 1) new
             , W.startTime
             , W.id
             , W.user
        from Workout W
        window UW AS (partition by W.user order by W.startTime)
    )
    select SUM(new) OVER (order by W.user, W.startTime) pseudoSession
         , W.id
         , W.startTime
         , W.user
    from W;
sqlgoogle-bigquery


Solution 1:[1]

As new is a reserved keyword, you likely meant to do:

with W as (
    select COALESCE(UNIX_TIMESTAMP(startTime) - UNIX_TIMESTAMP(LAG(startTime, 1) over UW) > 1000, 1) `new`
         , W.startTime
         , W.id
         , W.user
    from Workout W
    window UW AS (partition by W.user order by W.startTime)
)
select SUM(new) OVER (order by W.user, W.startTime) pseudoSession
     , W.id
     , W.startTime
     , W.user
from W;

Alternatively, some other non-reserved word can be used as well.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Paul T.

Related Questions