Get max count value of category based on date time

user_929122 0
Viewed 215

I have a table that looks something like this:

datetime category
2021.31.01 12:12:00 X
2021.31.01 12:23:00 X
2021.31.01 15:01:00 X
2021.31.01 15:23:00 X
2021.31.01 15:37:00 X
2021.30.02 11:23:00 Y
2021.30.02 15:13:00 X

What is needed is to get the max count value of each type grouped by date and hour.

So, the end result would look something this:

category date_hour number_of_occurances
X 2021.31.01 15:00:00 3
Y 2021.30.02 11:00:00 1

How the query looks now:

SELECT
    category,
    to_timestamp(datetime, 'y-M-d H') as date_hour,
    COUNT(*) AS number_of_occurances
FROM <my_table>
GROUP BY category, date_hour
ORDER BY number_of_occurances DESC
impala sql
1 Answers

Use a window function to find the top count per category and then only show the category/hours ranked #1.

SELECT category, date_hour, number_of_occurances
FROM
(
  SELECT
    category,
    TRUNC(datetime, 'HH24') AS date_hour,
    COUNT(*) AS number_of_occurances,
    RANK() OVER (PARTITION BY category ORDER BY COUNT(*) DESC) AS rnk
  FROM <my_table>
  GROUP BY category, TRUNC(datetime, 'HH24')
) ranked
WHERE rnk = 1
ORDER BY category;
user_22707620
Related