Hive: Query to get max count per word per date

Question

Here's the data I have:

date        |    word    |    count

01/01/2020       #abc           1
01/01/2020       #xyz           2
02/05/2020       #ghi           2
02/05/2020       #def           1 
02/04/2020       #pqr           4
02/04/2020       #cde           3
01/01/2020       #lmn           1

Here's the result that I want:

date        |    word    |    count

01/01/2020       #xyz           2
02/04/2020       #pqr           4
02/05/2020       #ghi           2

So basically, I want the word with maximum count on each particular date. Can someone help me out with the query?

Answer 1

Use row_number window function with partition by and order by clause and select only the maximum count from the partition!

SELECT date,word,count
FROM (
      SELECT date,word,count,row_number() over (partition by date order by count desc) as rn 
      from <table_name>) sq
    WHERE sq.rn = 1;