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
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 :)
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;