BigQuery: Dynamic TABLE_DATE_RANGE depending on iso week and day of the week

0
votes

I have a query on BQ that I run on a weekly basis, but not always on the same day of the week (sometimes on Monday, sometimes on Tuesday etc.)

Every week, I need to query data of the last two calendar weeks: e.g. assuming that we are now in CW 27 (isoweek, where Monday is the first day), I would like to have data from Monday of CW 25 to Sunday of CW 26.

FROM (TABLE_DATE_RANGE([xxx.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -14, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))) could work if I ran my query every week on Monday, but the limit is that I run it on different weekdays.

Does anyone have any idea? Any input is very much appreciated.

Thanks in advance!

1
sqlgoogle-bigquery
Just wondering, is there a reason for not using the Standard Version of BQ? It's much better than the legacy one :)! – Willian Fuks
I will consider the migration for sure, I am just pretty new with SQL and BQ and still need to figure out the differences between the 2 ;) - Thanks for the suggestion! – Learner.S

1 Answers

1
votes

Below is for BigQuery Legacy SQL that looks like you are still using 

#legacySQL
SELECT 
  <your fields here ...>
FROM (TABLE_DATE_RANGE([xxx.ga_sessions_], 
        DATE_ADD(CURRENT_TIMESTAMP(), -14 - DAYOFWEEK(CURRENT_TIMESTAMP()) + 2, 'DAY'), 
        DATE_ADD(CURRENT_TIMESTAMP(),  -1 - DAYOFWEEK(CURRENT_TIMESTAMP()) + 2, 'DAY')
      ))

Please note, it is very recommended to migrate to Standard SQL

With Standard SQL you can achieve the same semantics of TABLE_DATE_RANGE using a filter on the _TABLE_SUFFIX pseudocolumn

see example below 

#standardSQL
SELECT 
  <your fields here ...>
FROM `xxx.ga_sessions_*`
WHERE _TABLE_SUFFIX 
  BETWEEN FORMAT_DATE('%Y%m%d',DATE_ADD(CURRENT_DATE(), INTERVAL -14 - EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) + 2 DAY))
  AND FORMAT_DATE('%Y%m%d',DATE_ADD(CURRENT_DATE(), INTERVAL -1 - EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) + 2 DAY))