mysql - Finding what hour someone visits a hotel the most

Question

I want to know which hour period is the most frequent time a person visits, 2nd most frequent, 3rd most and so on. So for example 1 hour period starts at the top of the hour until the next top Eg 07:00:00-07:59:59 would be the 7am hour period.

CREATE TABLE visits (
id primary key
date_visited datetime not null,
cus_name varchar(32) not null
);

I am a little confused because I don't get how I can group by an hour

SELECT count(*)
FROM visits
GROUP BY date_visited
ORDER BY count(*) DESC

question from:https://stackoverflow.com/questions/65952598/finding-what-hour-someone-visits-a-hotel-the-most

Answer 1

You can use HOUR function:

SELECT hour(date_visited), count(*) as number of visits
FROM visits
GROUP BY hour(date_visited)
ORDER BY count(*) DESC

If you want to give numbers to the hours according to number of visits then you can use the analytical function (MySql 8.0 or higher) as follows:

SELECT hour(date_visited), count(*) as number of visits,
       row_number() over (order by count(*) desc) as num
FROM visits
GROUP BY hour(date_visited)
ORDER BY num