Okay, I am new to SQL and Big Query and got an ambiguous column name error. I have checked the other answers on stack overflow, but could not find/understand an answer to my problem. So I get: Error: 2.40 - 2.68: Ambiguous column name subreddit.
For this code (which I adapted from another person's analysis of a similar thing):
#legacySQL
# Creating list of number of users who authored at least 10 posts in pairs of subreddits:
SELECT t1.subreddit, t2.subreddit, SUM(1) as NumOverlaps
FROM (SELECT subreddit, author, COUNT(1) as cnt
FROM (TABLE_QUERY([fh-bigquery:reddit_comments],
'table_id CONTAINS "2017_" AND length(table_id) >= 5'))
GROUP BY subreddit, author HAVING cnt > 5) t1
JOIN (SELECT subreddit, author, COUNT(1) as cnt
FROM(TABLE_QUERY([fh-bigquery:reddit_comments],
'table_id CONTAINS "2017_" AND length(table_id) >= 5'))
GROUP BY subreddit, author HAVING cnt > 5) t2
ON t1.author=t2.author
WHERE t1.subreddit!=t2.subreddit
GROUP BY t1.subreddit, t2.subreddit
Thanks for your help!