14 Aug 18 · dan-irving · #bigquery ·   Bookmark   ×

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

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied