Hive tables join with count

Question

Assume there are two tables, user and comments. below are the fields in each of the tables,

user:

userid
username

Comments:

commentID
commentDescription
userid

write a query to find users who have more than 2 comments

o/p file:

userid
username
commentDescription

Answer 1

select u.userid,
       u.username,
       c.commentDescription 
  from user u
       inner join (select c.userid, c.commentDescription, 
                          count(*) over(partition by userid) cnt
                     from comments c
                  ) c 
        on u.userid=c.userid and c.cnt>2;