title: Error in querying Google Analytics data in BigQuery: 'Correlated aliases referenced in the from clause must refer to arrays that are vali..'
body: <p>In BigQuery, I have created the following query from a BigQuery partitioned table, with as initial source Google Analytics-data. The goal is to get # sessions, product revenue and shipping costs. Note that in the current setup I can't use the 'aggregated' fields like totals.visits.</p>
  
  <pre><code>SELECT c_country AS country, date As Date,  COUNT(DISTINCT CONCAT(CAST(fullVisitorId AS STRING),CAST(Visitid AS STRING), CAST(visitStartTime AS STRING))) AS Sessions, 
  (SELECT SUM(product.productRevenue)/1000000 FROM t.hits as hits, hits.product AS product) AS Product_Revenue, (SELECT SUM(hits.transaction.transactionShipping)/1000000 FROM t.hits AS hits) AS Shipping_Costs
  FROM `xx.yy.zz` as t
  WHERE c_date BETWEEN "2019-11-06" AND "2019-11-06"
  GROUP BY c_country, date
  </code></pre>
  
  <p>Now, the following error message appears:</p>
  
  <blockquote>
    <p>"Correlated aliases referenced in the from clause must refer to arrays
    that are valid to access from the outer query, but t refers to an
    array that is not valid to access after GROUP BY or DISTINCT in the
    outer query at [2:50]"</p>
  </blockquote>
  
  <p>Does anyone know how to adjust the query so that the query executes without issues?</p>