CODEWITHSUNDEEP
sqlgoogle-bigquery
jenmul90
jenmul90

Reputation: 19

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;

Upvotes: 0

Views: 623

Answers (1)

Paul T.
Paul T.

Reputation: 4907

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.

Upvotes: 1

Related Questions