By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table domain_lifecycle (
domain varchar,
timestamp timestamp,
lifecycle varchar);
insert into domain_lifecycle values
('example_1.com', '2020-01-01 01:01:00', 'start'),
('example_1.com', '2020-01-01 01:01:05', 'end'),
('example_2.com', '2020-01-01 01:01:07', 'start'),
('example_2.com', '2020-01-01 01:01:17', 'end'),
('example_3.com', '2020-01-01 01:01:20', 'start'),
('example_1.com', '2020-01-01 01:01:25', 'start'),
('example_3.com', '2020-01-01 01:01:23', 'end'),
('example_1.com', '2020-01-01 01:01:38', 'end');
8 rows affected
select distinct domain from
(select *,
lag(timestamp) over(partition by domain order by timestamp) as pre_timestamp
from domain_lifecycle) t
where lifecycle = 'end'
and extract(epoch from (timestamp - pre_timestamp)) >= 5;
| domain |
|---|
| example_1.com |
| example_2.com |
select distinct dl.domain
from domain_lifecycle dl
where dl.lifecycle = 'end' and
exists (select 1
from domain_lifecycle dl2
where dl2.domain = dl.domain and
dl2.timestamp < dl.timestamp and
extract(epoch from (dl.timestamp - dl2.timestamp)) >= 5
);
| domain |
|---|
| example_1.com |
| example_2.com |
select dl.domain
from domain_lifecycle dl
group by dl.domain
having extract(epoch from (max(dl.timestamp) - min(dl.timestamp))) >= 5
| domain |
|---|
| example_1.com |
| example_2.com |
select dl.domain
from domain_lifecycle dl
group by dl.domain
having max(dl.timestamp) >= min(dl.timestamp) + interval '5 second'
| domain |
|---|
| example_1.com |
| example_2.com |