PostgreSQL – How to Fetch Records with Consecutive Associated Records

postgresql

I have two tables:

books

+----+---------+
| id | title   |
+----+---------+
| 1  | Title 1 |
+----+---------+
| 2  | Title 2 |
+----+---------+

events

+----+----------+---------+-------------------------------------------+
| id | title_id | name    | datetime                                  |
+----+----------+---------+-------------------------------------------+
| 1  | 1        | event 1 | current_timestamp                         |
+----+----------+---------+-------------------------------------------+
| 2  | 1        | event 1 | current_timestamp - interval '30 minutes' |
+----+----------+---------+-------------------------------------------+
| 3  | 2        | event 2 | current_timestamp                         |
+----+----------+---------+-------------------------------------------+
| 4  | 2        | event 2 | current_timestamp - interval '5 minutes'  |
+----+----------+---------+-------------------------------------------+
| 5  | 2        | event 3 | current_timestamp                         |
+----+----------+---------+-------------------------------------------+

I want to produce a table which fetches books which have two or more consecutive events of a given name within a given time period.

With the above data, if I were to say the I was looking for 2 consecutive events of name "event 2" in the last 5 minutes, that would produce:

+----+---------+
| id | title   |
+----+---------+
| 2  | Title 2 |
+----+---------+

Books with 2 or more consecutive events of name "event 1" within the last 10 minutes would produce nothing, etc.

Would appreciate any insight. I've been trying with lateral queries, but cannot figure out the consecutive part of the query.

Best Answer

You can use ROW_NUMBER() or RANK() setting a partition by name and ordering by datetime.

Then fetch these rows with a row number >= 2.

create table books (id int, title text);
insert into books values (1, 'title1'), (2, 'title2');

create table events(id int, title_id int, name text, datetime timestamp);
insert into events values
(1, 1, 'event1', current_timestamp),
(2, 1, 'event1', current_timestamp - interval '30 minutes'),
(3, 2, 'event2', current_timestamp),
(4, 2, 'event2', current_timestamp - interval '5 minutes'),
(5, 2, 'event3', current_timestamp);

with x as
(
    select id, title_id, name, datetime,
           row_number() over (partition by name order by datetime) rn
    from   events 
    where  datetime >= current_timestamp - interval '5 minutes'
)
select   max(rn) as id, name
from     x
where    rn >= 2
group by name;

id | name  
-: | :-----
 2 | event2

dbfiddle here

Related Question