Search code examples
sqlgoogle-bigquerygreatest-n-per-groupwindow-functions

Google BigQuery Analytic Functions - MAX(FUNCTION)

I'm trying to write a Query that will return the maximum search volume associated with a given keyword for its corresponding url.

The sample data set looks like: enter image description here

The syntax I have so far is:

enter image description here

Ideally, the query would return the keyword with the highest search volume along with its url pair.

I'm not sure if I need to use an analytic function or if I can just create a self join? I've tried a variety of methods but not luck so far.

Any help would be much appreciated.

Solution

  • Ideally, the query would return the keyword with the highest search volume along with its url pair.

    It looks like you just want order by and limit:

    select t.*
from mytable t
order by search_vol desc limit 1

    This gives you the row with the greatest search_vol within the whole table.

    On the other hand, if you want the most searched keyword per url, then it is a greatest-n-per-group problem. Here is a solution in the spirit of your original attempt, using an analytic function:

    select t.* except (rn)
from (
    select t.*, rank() over(partition by url order by search_vol desc) rn
    from mytable t
) t
where rn = 1

    rank() allows top ties, if any. If you want to avoid that, use row_number().