Search code examples
sqlsql-serversql-server-2014

SQL Server - query find users with most popular keywords

In SQL Server 2014 I have a table look_table that lists all the keywords (with duplicates) that users have been looking for in my app with related user_id. So the table has 2 columns: keyword, user_id.

GOAL: Select 3 most popular keywords in table and find all users that was looking for all of these 3 keywords.

What I have so far is to list 3 most popular keywords:

select top 3 keyword
from look_table
group by keyword
order by count(*) desc

But I can't get users which have been looking for all of these keywords. It would be great if someone will help :)

Solution

  • If you want all three, then that is a bit tricky. Here is one method:

    with k as (
      select top 3 keyword
      from look_table
      group by keyword
      order by count(*) desc
     )
select lt.user_id
from look_table lt join
     k
     on lt.keyword = k.keyword
group by lt.user_id
having count(distinct lt.keyword) = 3;