SQL syntax error in aggregates_3_4.sql?

[sebxwolf]

Describe the bug
I tried running queries in aggregates_3_4.sql but got an SQL syntax error due to the 'week' in quotes.

To Reproduce
Run the sql queries in aggregates_3_4.sql

Expected behavior
Create the tables

Additional context
Maybe this is supposed to run in an environment with different flavor of SQL? I tried running it in Spark. But an SQL syntax validator gave me the same error: https://www.eversql.com/sql-syntax-check-validator/

Suggested solution
Remove the quotes and it works...

[jc-harrison]

Ah, you're right. extract('week' FROM calls.call_date) is valid in PostgreSQL, but is not in the SQL standard. We should either find a form that's valid in all flavours of SQL, or document alternatives for the most common flavours.

From a quick search:

• MySQL: extract(week FROM calls.call_date) (without quotes)
• Oracle: extract(calls.call_date, week)
• SQL Server: datepart(week, calls.call_date)
• Spark: weekofyear(calls.call_date)
• SQLite: cast(strftime('%W', calls.call_date) AS NUMERIC)

extract(week FROM calls.call_date) (without quotes) is also valid in PostgreSQL, so perhaps we should at least make that change so it's compatible with more (if not all) SQL flavours.