I have a table which saves the time customer views the video. I want to find out the peak hour when the most video are viewed and what the peak number is. I need this data for all the days in the database. How can I accomplish this?
I am able to get only one record by using max and count functions and inner joins but I want the peak volume and hour for every day.
The syntax depends on the database. Also, your question is vague. I am interpreting it as "peak hour of days combined" rather than "peak hour of a particular day".
The following is almost-ANSI syntax:
select *
from (select extract(hour from ViewTime) as hr, count(*) as cnt
from VideoViews
group by extract(hour from ViewTime)
) t
order by cnt desc
limit 1
In some databases, you might be using "datepart()" or "to_char()" to get the hour from the date. In some databases, you might use "top (1)" or "rownum = 1" rather than "limit 1". But, the overall idea is the same: aggregate to get the results you want and then choose the largest value by using order by and selecting one row.
Given a table schema like:
create table customer_view_log
(
customer_id int not null ,
dt_viewed datetime not null ,
video_id int not null ,
constraint customer_view_log_PK primary key nonclustered ( customer_id , dt_viewed ) ,
constraint customer_view_log_AK01 unique nonclustered ( dt_viewed , customer_id ) ,
constraint customer_view_log_FK01 foreign key ( customer_id ) references customer ( id ) ,
constraint customer_view_log_FK01 foreign key ( video_id ) references video ( id ) ,
)
A query like this
select top 1
Hour = datepart(hour,dt_viewed) ,
Viewings = count(*)
from customer_view_log
group by datepart(hour,dt_viewed)
order by 2 desc
should do the trick. The above is SQL Server: your implementation might vary as to the details. Date/Time related stuff varies widely across implementations.
