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]
-
Any advice would be greatly appreciated.
Reply-
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 -
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
-
