SQLPad
- 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.
Table: search
col_name| col_type ----------+------------ country | varchar(2) date | date user_id | integer search_id | integer query | text
Sample results
country | query
--------------+-------
US | Joe Biden
UK | David Beckham
Expected results
Solution postgres
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;
Explanation
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.