Syntax error: Unexpected keyword INTERVAL

It's been a long time since I've done any SQL...

I'm following the guide on retention analysis from https://www.periscopedata.com/blog/how-to-calculate-cohort-retention-in-sql and falling at the first hurdle

Here is their original snippet:

select *
from activity
left join activity as future_activity on
  activity.user_id = future_activity.user_id
  and activity.date = future_activity.date - INTERVAL '1 day'

Here is the modified snippet adjusted for my data:

select *
from teams_activities
left join teams_activities as future_activity on
  teams_activities.id = future_activity.id
  and teams_activities.date_activity = future_activity.date_activity - interval '1 day'

And here's the error I get out of BigQuery.

Syntax error: Unexpected keyword INTERVAL at [9:72]

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Any advice would be greatly appreciated.

    Reply Like
      • Neha Kumar
      • Solutions Engineer
      • Neha_Kumar
      • 9 mths ago
      • 1
      • Reported - view

      Mike Bartlett The post referenced uses Redshift syntax. You would need to make a few updates to the syntax to make the code BigQuery compatible!

      If you're using Standard SQL, the following syntax would work to successfully subtract one day

      #standardSQL
      select DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

      If you're using Legacy SQL, you can use this format:

      #legacySQL
      select [DATE_ADD(CURRENT_DATE(), -1, 'DAY'):date]

      Hope this helps!

      Reply Like 1
    • Neha Kumar Thank you so much.

       

      For reference, I think this is the correct adapter SQL statement:

       

      select *
      from [teams_activities as a]
      left join a as future_activity on
        a.id = future_activity.id
        and a.date_activity = DATE_SUB(future_activity.date_activity, INTERVAL 1 DAY)
      Reply Like 1
Like Follow
  • 9 mths agoLast active
  • 3Replies
  • 482Views
  • 2 Following