create temp table candidates as
select id, pull_id, last_updated, body
from pullrequest
where pull_id in ( select pull_id from pullrequest group by pull_id having count(*) > 1 )
;

create temp table keepers as
select id, pull_id, last_updated, body
from candidates p1
where (
        (
                last_updated is null
                and body like '%Reviewable%'
                and not exists (
                        select id from candidates p2
                        where p2.id in (select id from candidates p3 where p3.pull_id in (select pull_id from candidates))
                        and p2.pull_id = p1.pull_id and p2.id != p1.id
                        and p2.body like '%Reviewable%'
                        and ( p2.last_updated is not null or (p2.last_updated is null and p2.id > p1.id ) )
                )
        )
        or
        (
                p1.last_updated is not null
                and p1.body like '%Reviewable%'
                and not exists (
                        select id from candidates p4
                        where p4.id in (select id from candidates p5 where p5.pull_id in (select pull_id from candidates))
                        and p4.pull_id = p1.pull_id and p4.id != p1.id
                        and p4.last_updated is not null and p4.body like '%Reviewable%'
                        and p4.last_updated > p1.last_updated
                )
        )
)
order by 2,1;

select pull_id as "triplets or more" from candidates group by 1 having count(*) > 2;
select 'delete these' as "delete these",* from candidates where id not in (select id from keepers);
select 'keep these' as "keep these",* from candidates where id in (select id from keepers);

delete from pullrequeststatus
where pull_request_id in (select id from candidates)
and pull_request_id not in (select id from keepers);

delete from pull_request_labels
where pull_request_id in (select id from candidates)
and pull_request_id not in (select id from keepers);

delete from mergebatch_pullrequests
where pull_id in (select id from candidates)
and pull_id not in (select id from keepers);

delete from pullrequest
where id in (select id from candidates)
and id not in (select id from keepers);

drop table candidates;
drop table keepers;
