SQL Rank and SUM

Question

I am facing a bit of issue with an SQL query:

Currently I have 2 tables. The first table lists sales by a vendor and country eg and there are a lot more rows but this is just the gist.

Country id     Sale
US      1      100
UK      2      1000
US      3      150 
UK      2      200

In the second table I have ids that links to the vendor's name eg

id name
1  john
2  david 
3  tom 

I need to get the top vendor in each country but sum of sales. the output should look something like this country id name sum_sales

Would you be able to help. Currently I am only able to groupby and sum and am unable to obtain the top guy in each country. thank you!

I am running this on big_query sql

Answer 1

Use dense_rank() with aggregation :

select yr, Country, id, name, total_sales
from (select extract(year from s.date) as yr,
             s.Country, s.id, v.name, sum(s.sales) as total_sales,
             dense_rank() over (partition by s.date, s.country order by sum(s.sales) desc) as seq
      from sales s inner join
           vendors v
           on v.id = v.id
      group by s.date, s.Country, s.id, v.name
     ) t
where seq <= 2;

EDIT : For specific year format use FORMAT_DATETIME

FORMAT_DATETIME("%Y", DATETIME "2020-03-19")

By this way, you will get vendors for each country which are having higher sales.

Note : This will display two or more vendors which are having same total sales. If you want only one from them, then use row_number() instead of dense_rank().

Answer 2

In BigQuery, you can use window functions with aggregation:

select id, name, country, sum_sales
from (select s.id, v.name, s.country, sum(sales) as sum_sales
             row_number() over (partition by s.country order by sum(sales) desc) as seqnum
      from sales s join
           vendors v
           on v.id = v.id
      group by s.id, v.name, s.country
     ) sv
where seqnum = 1;