Get max count value of category based on date time

Question

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

Answer 1

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;