CODEWITHSUNDEEP
sql
user1639681
user1639681

Reputation: 11

SQL to determine peak volume and hour for all days

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.

Upvotes: 0

Views: 4945

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74287

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270011

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.

Upvotes: 2

Related Questions