title: BigQuery UNNEST duplicates values
body: <p>I am trying to create a summary of Google Analytics data: sessions, transactions and conversion rate by hour and by source property (named service in my case). I am querying a table named "ga_realtime_view" from a rollup property dataset. It is a virtual view created from the "ga_realtime_sessions_" table, which allows us to use standard SQL.</p>
  
  <p>In order to have the service column, I have to use UNNEST operation. However when I do so, it duplicates all the sessions and transactions values.</p>
  
  <p>Here is the query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT
  EXTRACT(HOUR FROM TIMESTAMP_SECONDS(visitStartTime) AT TIME ZONE 'Europe/Paris') AS Hour,
  hits.sourcePropertyInfo.sourcePropertyDisplayName AS service,
  IFNULL(SUM(totals.visits),0) as sessions,
  IFNULL(SUM(totals.transactions),0) as transactions,
  IFNULL(ROUND((SUM(totals.transactions)/SUM(totals.visits))*100,2),0) AS conversionRate
  FROM `XX.ga_realtime_view` AS session, UNNEST(session.hits) AS hits
  GROUP BY
  Hour,
  service
  ORDER BY
  Hour
  </code></pre>
  
  <p>I know there has been other topics about this duplication issue, however I can't seem to find the solution in my case.</p>
  
  <p>Thanks for your help</p>