title: BigQuery filtering records in standard sql
body: <p>I'm working on counting all visitors that submitted postcode on our homepage. I came up with following query in legacy SQL:</p>
  
  <pre><code>SELECT fullVisitorId, visitStartTime
  FROM TABLE_DATE_RANGE([ga_sessions_], TIMESTAMP('2017-01-29'), CURRENT_TIMESTAMP())
  where hits.page.pagePath = '/broadband/'
  and visitStartTime &gt; 1483228800
  and hits.type   = 'EVENT'
  and hits.eventInfo.eventCategory = 'Homepage'
  and hits.eventInfo.eventAction = 'Submit Postcode';
  </code></pre>
  
  <p>I then wanted to convert it to standard SQL to use within CTE and came up with this one that doesn't seem right though. </p>
  
  <pre><code>SELECT fullVisitorId, visitStartTime
  FROM ``ga_sessions_*``, UNNEST(hits) as h
  where 
  
  _TABLE_SUFFIX &gt; '2017-01-29'
  AND h.page.pagePath = '/broadband/'
  and visitStartTime &gt; 1483228800
  and h.type  = 'EVENT'
  and h.eventInfo.eventCategory = 'Homepage'
  and h.eventInfo.eventAction = 'Submit Postcode';
  </code></pre>
  
  <p>The first one processes 327 MB and returns 4117 results, the second one processes 6.98 GB and returns 60745 results.</p>
  
  <p>I've looked at the <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql#filtering_repeated_fields" rel="nofollow noreferrer">migration guide</a>, but it didn't prove very helpful for me. </p>
  
  <p>ga_sessions has <a href="https://support.google.com/analytics/answer/3437719?hl=en" rel="nofollow noreferrer">standard schema</a> of GA import into Bigquery.</p>