title: BigQuery StandardSQL: Last 7 Days using _TABLE_SUFFIX
body: <p><strong>Question:</strong> I want to pull data from multiple Google Analytics sessions tables using _TABLE_SUFFIX, but I want to set the suffix parameters to between "seven days ago" and "one day ago" (i.e. pulling data for the last 7 days)</p>
  
  <p>The current syntax (that doesn't work):</p>
  
  <pre><code>#StandardSQL
  SELECT
    date,
    SUM (totals.visits) AS visits
  FROM
    `projectname.123456789.ga_sessions_*`
  WHERE
    _TABLE_SUFFIX BETWEEN 
    'DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)' AND 
    'DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL -1 DAY)'
  GROUP BY 
     date
  ORDER BY 
     date ASC
  </code></pre>
  
  <p>Which gives me the message "<strong>Valid:</strong> This query will process 0 B when run." To my eyes, there is no error in the syntax, but BigQuery is unable ot read my date functions and thus unable to suffix them to the ga_sessions_* wildcard</p>
  
  <p><strong>Inspiration:</strong></p>
  
  <p>BigQuery Cookbook has an example for legacySQL that I have been basing this on: (<a href="https://support.google.com/analytics/answer/4419694?hl=en#7days" rel="noreferrer">https://support.google.com/analytics/answer/4419694?hl=en#7days</a>)</p>
  
  <pre><code>#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 
  </code></pre>
  
  <p><strong>Things I've tried:</strong> (that doesn't work)</p>
  
  <p>Using DATE_SUB instead of DATE_ADD and using CURRENT_DATE instead of CURRENT_TIMESTAMP: </p>
  
  <pre><code>WHERE
     _TABLE_SUFFIX BETWEEN 
     'DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)' AND 
     'DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)'
  </code></pre>
  
  <p>Resulting in "<strong>Valid:</strong> This query will process 0 B when run."</p>
  
  <p>Using DATE_FORMAT around DATE_SUB and CURRENT_DATE in order to get the dates without dashes: </p>
  
  <pre><code>WHERE
     _TABLE_SUFFIX BETWEEN 
     'FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))' AND 
     'FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))'
  </code></pre>
  
  <p>Resulting in "<strong>Valid:</strong> This query will process 0 B when run."</p>
  
  <p>Tried skippingt he hyphens '' around the DATE_SUB clause</p>
  
  <pre><code>WHERE
    _TABLE_SUFFIX BETWEEN 
    DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND
    DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  </code></pre>
  
  <p>Resulting in the following error message "<strong>Error:</strong> No matching signature for operator BETWEEN for argument types: STRING, DATE, DATE. Supported signature: (ANY) BETWEEN (ANY) AND (ANY)"</p>
  
  <p>Thanks in advance, </p>