BigQuery Duplicate column names

Question

I have a query:

WITH DAY_WIDGET_STAT AS (
SELECT hits.eventInfo.eventCategory, hits.eventInfo.eventAction, hits.eventInfo.eventLabel, hits.page.pagePath  
FROM `api-open-broker.108613784.ga_sessions_20181125`, UNNEST(hits) as hits
WHERE hits.type='EVENT' and hits.eventInfo.eventCategory LIKE "%Widget%"
),
DAY_PAGEVIEWS_STAT AS (
SELECT hits.page.pagePath, COUNT(*) AS pageviews
FROM `api-open-broker.108613784.ga_sessions_20181125`,UNNEST(hits) AS hits
WHERE hits.type = 'PAGE' GROUP BY hits.page.pagePath ORDER BY pageviews DESC)

SELECT * 
FROM DAY_WIDGET_STAT INNER JOIN DAY_PAGEVIEWS_STAT ON DAY_WIDGET_STAT.pagePath=DAY_PAGEVIEWS_STAT.pagePath
LIMIT 10

Which gives me "Duplicate column names in the result are not supported. Found duplicate(s): pagePath". Why? Table DAY_WIDGET_STAT: Look DAY_PAGEVIEWS_STAT: Look

Answer 1

The duplicate is pagePath; this is in both the CTEs. In this case, I would probably recommend:

SELECT w.*,
       p.* EXCEPT (pagePath) 
FROM DAY_WIDGET_STAT w INNER JOIN
     DAY_PAGEVIEWS_STAT p
     ON w.pagePath = p.pagePath
LIMIT 10;

Alternatively, USING should also do what you want:

SELECT * 
FROM DAY_WIDGET_STAT w INNER JOIN
     DAY_PAGEVIEWS_STAT p
     USING (pagePath)
LIMIT 10;

You might be able to rewrite the query to be a single aggregation, but without sample data and desired results, it is a little hard to figure out. If you are interested, you can ask another question.