Search code examples
sqldistinctgoogle-bigqueryweb-trafficreferrals

google big query on sorting unique combination of multiple columns

We already have a table of all referral of a website: ( one entry per referral )

String : referral
String : target
integer: date

Now, we want to use big query to sort all referrals based on counts, like:

referral       : target,         count:

google.com/... : welcome.html,   28353 
bing.com/...   : welcome.html,   5334 
gmail.com/...  : about.html,     343
...

What should the big query sql be?

Solution

  • why not just group by both columns?

    SELECT referral, target, COUNT(*) as cnt
FROM [mydataset.referrallog] 
GROUP BY referral, target
ORDER BY cnt DESC