MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.6.44


create table tab( id int, post_id int, status varchar(10), date date );
insert into tab values
 (1,1,'draft','2019-03-25'),
 (2,1,'public','2019-04-02'),
 (3,1,'draft','2019-05-25'),
 (4,2,'draft','2019-03-10'),
 (5,2,'public','2019-04-01'),
 (6,2,'draft','2019-06-01');
select distinct t1.post_id
  from tab t1
where t1.post_id not in
    (
     select distinct t1.post_id
       from tab t1
       join
       (
        select post_id, max(date) as date
          from tab 
         where '2019-05-01'> date
         group by post_id ) t2
         on t1.post_id = t2.post_id 
      where t1.status != 'public' 
        and t1.date < '2019-06-01' 
        and t1.date > '2019-04-30'
)
 


post_id


2