SQL - how to get certain value and category to transform data?

Hadyan azhar 0
Viewed 51

Here is what the table looks like : google trends

I want to know the most searched terms in every region_name in Indonesia. Results just region/province, terms, and how many times those "terms" been searched. I've tried but the result gives all terms and I want to limit just the top of each region.

Code that I tried :

SELECT 
    region_name,
    term,
    count(term) AS Total_searched 
  FROM 
  `bigquery-public-data.google_trends.international_top_rising_terms` 
  WHERE 
  refresh_date between "2022-01-01" AND "2022-09-06"
    AND
    country_name = "Indonesia"
    AND
    score IS NOT NULL 
  Group by
    term,
    region_name
  order by
    Total_searched DESC

So how do I get results with just All 34 regions In Indonesia with the top most searched terms in every region from the beginning of 2022 until today?

google-bigquery sql
1 Answers

Query the result again and generate an array of the terms.

With data as
(
SELECT
  region_name,
  term,
  COUNT(term) AS Total_searched
FROM
  bigquery-public-data.google_trends.international_top_rising_terms
WHERE
  refresh_date BETWEEN "2022-09-05"
  AND "2022-09-06"
  AND country_name = "Indonesia"
  AND score IS NOT NULL
GROUP BY
  term,
  region_name
ORDER BY
  Total_searched DESC
)
Select region_name, any_value(term) as any_term,
array_agg(term order by Total_searched desc limit 1)[safe_offset(0)] as most_term,
array_agg(struct(term,Total_searched) order by Total_searched desc limit 2)  as terms,
from data 
group by 1
userG7m710
Related