Sales amounts of the top n selling vendors by month in bigquery

user_17334324 0
Viewed 139

i have a table in bigquery like this (260000 rows):

vendor  date                  item_price
x       2021-07-08 23:41:10   451,5
y       2021-06-14 10:22:10   41,7
z       2020-01-03 13:41:12   74
s       2020-04-12 01:14:58   88
....

exactly what I want is to group this data by month and find the sum of the sales of only the top 20 vendors in that month. Expected output:

month     sum_of_only_top20_vendor's_sales
2020-01   7857
2020-02   9685
2020-03   3574
2020-04   7421
.....
google-bigquery
2 Answers

Consider below approach

select month, sum(sale) as sum_of_only_top20_vendor_sales
from (
  select vendor, 
    format_datetime('%Y%m', date) month, 
    sum(item_price) as sale 
  from your_table
  group by vendor, month
  qualify row_number() over(partition by month order by sale desc) <= 20
)
group by month
user_52219440

Another solution that potentially can show much much better performance on really big data:

select month, 
  (select sum(sum) from t.top_20_vendors) as sum_of_only_top20_vendor_sales
from (
  select 
    format_datetime('%Y%m', date) month, 
    approx_top_sum(vendor, item_price, 20) top_20_vendors
  from your_table
  group by month
) t

or with a little refactoring

select month, sum(sum) as sum_of_only_top20_vendor_sales
from (
  select 
    format_datetime('%Y%m', date) month, 
    approx_top_sum(vendor, item_price, 20) top_20_vendors
  from your_table
  group by month
) t, t.top_20_vendors
group by month
user_52219440
Related