BigQuery StandardSQL: Time travel last 7 Days using _TABLE_SUFFIX

Problem: To pull and show data that has been inserted only in the last 7 days

Solution:

Based on the BigQuery Cookbook, that has an example for legacy SQL:

#LegacySQL
SELECT
  date,
  SUM (totals.visits) AS visits
FROM 
  (TABLE_DATE_RANGE([73156703.ga_sessions_], 
  DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), 
  DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
  date
ORDER BY
  date ASC 

dan-irving

posted on 14 Aug 18