How to find the maximum number of entries in an indefinite hour?

Question

SQL

Good evening. They set me a task - in the "posts" table with fields usrid | data | post | postid and a large number of entries over 4 years, find N users who posted most often in an indefinite period of time of 60 minutes. Those. If you put it on your fingers, Vasya posts from 20 to 100 messages a day, and the maximum that he got was 10 posts per hour. At the same time, Misha and Kolya post once a day, 30 and 40 posts per hour, respectively. But sometimes they post 5 posts an hour, and sometimes they don't post at all. I need to display in the table, for example, Kolya and Misha, because they managed to post 30 and 40 posts per hour. Everything would be fine, but the concept of an hour is not defined by any time frame. It's just 60 minutes of life - maybe from 15:48 to 16:48, or maybe from 19:02 to 20:02...
I began to select all records from the end and compare them with the previous ones, and discard those that have more than an hour of time between them. But then I realized that this is nonsense, because you need to compare each post with all the previous ones in an hour, so with a shift of -1 until I reach the beginning of the records. With 4,000 users and over 80,000 records, I'm afraid to present this dataset. Can someone tell me how to implement it easier? And is there a solution to such a problem if the time frame of the hour is not defined?

Answer 1

here under oracle: sqlfiddle.com/#!4/1a2436/1

CREATE TABLE T1
    ("IID" int, "DDT" date)
;

INSERT ALL 
    INTO T1 ("IID", "DDT")
         VALUES (2, to_date('2003-05-03 12:12', 'yyyy-mm-dd hh24:mi'))
    INTO T1 ("IID", "DDT")
         VALUES (1, to_date('2003-05-03 12:22', 'yyyy-mm-dd hh24:mi'))
    INTO T1 ("IID", "DDT")
         VALUES (1, to_date('2003-05-03 12:32', 'yyyy-mm-dd hh24:mi'))
    INTO T1 ("IID", "DDT")
         VALUES (2, to_date('2003-05-03 12:42', 'yyyy-mm-dd hh24:mi'))
    INTO T1 ("IID", "DDT")
         VALUES (1, to_date('2003-05-03 12:52', 'yyyy-mm-dd hh24:mi'))
    INTO T1 ("IID", "DDT")
         VALUES (2, to_date('2003-05-03 13:13', 'yyyy-mm-dd hh24:mi'))
    INTO T1 ("IID", "DDT")
         VALUES (2, to_date('2003-05-03 13:14', 'yyyy-mm-dd hh24:mi'))
    INTO T1 ("IID", "DDT")
         VALUES (1, to_date('2003-05-03 13:21', 'yyyy-mm-dd hh24:mi'))
    INTO T1 ("IID", "DDT")
         VALUES (2, to_date('2003-05-03 14:24', 'yyyy-mm-dd hh24:mi'))
SELECT * FROM dual
;

SELECT IID, MAX(ids) AS max_id
  FROM (
        SELECT IID , 
               COUNT(*) OVER (PARTITION BY IID 
                              ORDER BY DDT
                              RANGE INTERVAL '60' MINUTE PRECEDING) AS ids
          FROM T1
        )
 GROUP BY IID;

stackoverflow.com/questions/14434032/get-the-maxim...