SQL Rank and SUM

Admin 1
Viewed 265

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

google-bigquery sql
3 Answers

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;
user_11440350

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().

user_55294450

Below is for BigQuery Standard SQL 

#standardSQL
SELECT AS VALUE ARRAY_AGG(t ORDER BY sum_sale DESC LIMIT 1)[OFFSET(0)]
FROM (
  SELECT country, id, name, SUM(sale) sum_sale
  FROM `project.dataset.vendors`
  JOIN `project.dataset.sales`
  USING(id)
  GROUP BY id, name, country
) t
GROUP BY country
user_52219440
Related