update SQL - products also purchased

Question

Using this query from the BigQuery cookbook, and added in _table_date_range_ because I want to query over more than just one day.

The query finds a product and returns other products purchased with that product.

SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM (TABLE_DATE_RANGE([ghd-analytics-xxxxxx.xxxxxx.ga_sessions_],
TIMESTAMP('2018-08-01'), TIMESTAMP('2018-09-31')))
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM (TABLE_DATE_RANGE([ghd-analytics-xxxxxx.xxxxxx.ga_sessions_],
TIMESTAMP('2018-08-01'), TIMESTAMP('2018-08-03')))
  WHERE hits.item.productName CONTAINS 'productA'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='productA'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

Error message:

(L2:1): JOIN (including semi-join) and UNION ALL (comma, date range) may not be combined in a single SELECT statement. Either move the UNION ALL to an inner query or the JOIN to an outer query.

I'm not sure how to do what it's suggesting!

Many thanks for any help.

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2019-04-15T17:22:27

I'm not sure how to do what it's suggesting!

You just literally do what is suggested in error statement - which is move the UNION ALL to an inner query
So, in below fixed version - I've just added SELECT * FROM in second line

SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM (SELECT * FROM TABLE_DATE_RANGE([ghd-analytics-xxxxxx.xxxxxx.ga_sessions_],
TIMESTAMP('2018-08-01'), TIMESTAMP('2018-09-31')))
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM (TABLE_DATE_RANGE([ghd-analytics-xxxxxx.xxxxxx.ga_sessions_],
TIMESTAMP('2018-08-01'), TIMESTAMP('2018-08-03')))
  WHERE hits.item.productName CONTAINS 'productA'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='productA'
GROUP BY other_purchased_products
ORDER BY quantity DESC

Note: I hope you have good reason to use Legacy SQL here - in any case consider Migrating to Standard SQL

update SQL - products also purchased

1 Answers