Oracle SQL query to get the max logins per hour from am_session table

Question

How to get the max logins per hour from am_session table which has columns : userid, create_time (datatype:timestamp(6)).

Attached the sample data.

Thanks in advance

Answer 1

You can do as below -

select 
to_char(create_time, 'YYYY-MM-DD') as create_date, 
extract(hour from create_time) as Hour,
Count(*)
from am_session
group by 
to_char(create_time, 'YYYY-MM-DD'),
extract(hour from create_time)
order by 1, 2 desc;

Note : This will not provide you count for hours where there was no login.