Need top 5 searched keywords from sum of searched words

Question

Category	Searched Word	Search Count	Date
Clothing	Men Shirt	3544	2022-01-19
Footwear	Shoes	7293	2022-02-02
Mobile	Iphone	4901	2022-02-12
Clothing	Hoodie	2049	2021-12-30
Footwear	crocs	3328	2022-01-28
Clothing	Men shirt	903	2022-02-12

I am working on bigquery and from above table I need to find top 5 searched word for each category on the basis of sum of search count column between date 2022-01-01 to 2022-02-14, I have tried SQL windows function but couldn't solve. Any help is appreciated.

Answer 1

Consider any of below approaches

Option 1

select Category, string_agg(SearchedWord, ', ' order by totalCount desc limit 5) topFiveSearchedWord
from (
  select Category, initcap(SearchedWord) SearchedWord, sum(SearchCount) totalCount
  from your_table
  where date between '2022-01-01' and '2022-02-14'
  group by Category, SearchedWord
)
group by Category

Option 2

select Category, 
  ( select string_agg(value, ', ' order by sum desc)
    from t.arr
  ) topFiveSearchedWord
from (
  select Category, approx_top_sum(initcap(SearchedWord), SearchCount, 5) Arr
  from your_table
  where date between '2022-01-01' and '2022-02-14'
  group by Category            
) t