CODEWITHSUNDEEP
mysqlsqloracle-database
user674669
user674669

Reputation: 12392

sql query to find the most popular hour

Given a table with 2 columns

Name(type VARCHAR) Time(type DATETIME)

I need to write a SQL query to find the hour with largest number of entries.

For example:

Name   Time 
----   ----
a     12:30pm
b     12:05pm
c     13:55pm
d     12:50pm
e     01:02am

Desired result is the hour: 12pm - 1pm

My general idea is:
1) Group the rows by hour
2) count the rows / group
3) sort the counts in descending order
4) print the largest count

I need help converting this into an SQL query.

Upvotes: 0

Views: 2465

Answers (2)

Art
Art

Reputation: 5792

This is Oracle version of what in my understanding you are looking for:

SELECT t_stamp
     , MAX(count(t_stamp)) OVER (PARTITION BY t_stamp ORDER BY t_stamp)  hits_per_hr_interval
  FROM
  (
  SELECT name, EXTRACT(hour From Cast(t_stamp as timestamp)) t_stamp
    FROM stack_test
  )
  GROUP BY t_stamp
  /

  T_STAMP    HITS_PER_HR_INTERVAL
  -------------------------------
  12          3
  13          2

OR using row_number(): 

SELECT * FROM
(
 SELECT t_stamp
      , Count(t_stamp) OVER (PARTITION BY t_stamp ORDER BY t_stamp) hits_per_hr_interval 
      , ROW_NUMBER() OVER (PARTITION BY t_stamp ORDER BY t_stamp) hr_seq
   FROM
   (
   SELECT EXTRACT(hour From Cast(t_stamp as timestamp)) t_stamp
     FROM stack_test
   )
  )
  -- WHERE hr_seq = 1  -- optional to see only first row per group as in above example 
  /

 T_STAMP    HITS_PER_HR_INTERVAL    HR_SEQ
 ------------------------------------------
 12         3                       1
 12         3                       2
 12         3                       3
 13         2                       1
 13         2                       2

You can add more filters like where your hours is between 12 and 1 only...

Upvotes: 0

poncha
poncha

Reputation: 7866

This is pretty straightforward...

SELECT HOUR(Time) as Hr,COUNT(*) AS Cnt
FROM MyTable
GROUP BY Hour(Time)
ORDER BY Cnt DESC
LIMIT 1

GROUP BY Hour(Time) - group by hours

ORDER BY Cnt DESC - this makes it sort by the count (in descending order)

LIMIT 1 - as you only need one top result

Upvotes: 3

Related Questions