How to make a selection by interval and condition?

Question

PostgreSQL

There is a field events table:
id, operation_id, type, date

1, 100, open, 2021-02-03 07:36:55
2, 100, open_page_1, 2021-02-03 07:36:55
3, 100, close_page_1, 2021-02-03 07: 36:56
4, 200, open, 2021-02-03 07:36:55
5, 200, open_page_2, 2021-02-03 07:36:55

We need to take all the fields for the last hour and only those with open types , open_page_1 and this operation_id has no other events

Answer 1

Use for health:

select * 
from events
where 
  date > current_timestamp - interval '1 hour'
  and type in ('open', 'open_page_1')
  and not exists (
    select type from events e 
    	where e.operation_id = events.operation_id
    		and type not in ('open', 'open_page_1')
  )
;

Share SQL code

Answer 2

About filtering by types

select operation_id 
from <table>
group by operation_id 
having sum(case when type in ('open', 'open_page_1') then 1 else 3 end) = 2

Answer 3

What's stopping you?
https://www.postgresql.org/docs/current/functions-...