- Write a query to return the top searched term in the US and UK on new year's day (2021-01-01), separately
- The order of your results doesn't matter.
- Rank each search query based on the number of unique users who searched this query.
col_name| col_type ----------+------------ country | varchar(2) date | date user_id | integer search_id | integer query | text
country | query --------------+------- US | Joe Biden UK | David Beckham
SELECT country, query FROM ( SELECT query, country, COUNT(DISTINCT user_id), ROW_NUMBER() OVER(PARTITION BY country ORDER BY COUNT(DISTINCT user_id) DESC) AS row_num FROM search WHERE country IN ('US', 'UK') AND date = '2021-01-01' GROUP BY 1,2 ) X WHERE row_num = 1;
This query selects the country and search query from a table called "search". It filters the results to only include data from the US and UK on January 1st, 2021.
The inner query aggregates the data by grouping it by query and country. It then counts the number of unique user IDs associated with each query and country combination. The results are ordered by the count of unique users in descending order for each country group, and assigned a row number within that group.
The outer query filters the results to only include the rows with a row number of 1, which means the query with the highest number of unique users in each country group.
In summary, this query finds the most popular search query for each country (US and UK) on January 1st, 2021, based on the number of unique users who searched for it.