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 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 = - INTERVAL '1 day'

Here is the modified snippet adjusted for my data:

select *
from teams_activities
left join teams_activities as future_activity on =
  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
      • 1 yr 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


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

      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 =
        and a.date_activity = DATE_SUB(future_activity.date_activity, INTERVAL 1 DAY)
      Reply Like 1
Like Follow
  • 1 yr agoLast active
  • 3Replies
  • 518Views
  • 2 Following