Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE status_table (
  name       text NOT NULL PRIMARY KEY
, version    int  NOT NULL
, processed  bool NOT NULL
, processing bool NOT NULL
, updated    int  NOT NULL
, ref_time   int  NOT NULL
);

INSERT INTO status_table VALUES 
  ('abc', 1, 't', 'f', 27794395, 27794160)
, ('def', 1, 'f', 'f', 27794395, 27793440)
, ('ghi', 1, 't', 'f', 27794395, 27793440)
, ('jkl', 1, 't', 'f', 27794395, 27794160)
, ('mno', 1, 't', 'f', 27794395, 27793440)
, ('pqr', 1, 't', 't', 27794395, 27794160)
, ('cba', 1, 't', 'f', 27794395, 27794170)
, ('fed', 1, 'f', 'f', 27794395, 27793450)
, ('ihg', 1, 't', 'f', 27794395, 27793450)
, ('lkj', 1, 't', 'f', 27794395, 27794170)
, ('onm', 1, 't', 'f', 27794395, 27793450)
, ('rqp', 1, 't', 't', 27794395, 27794170)
-- one more fully processed to have 4
, ('666', 1, 't', 'f', 27794399, 27794145)
-- vary count
, ('pqg', 1, 't', 't', 27794395, 27794160)

, ('pqh', 1, 't', 't', 27794395, 27794150)
, ('pqi', 1, 't', 't', 27794395, 27794150)
;

CREATE TABLE



INSERT 0 16


SELECT ref_time
     , count(*) AS total
     , count(*) FILTER (WHERE processed) AS processed
     , round(count(*) FILTER (WHERE processed) * 100.0 / count(*), 2) AS percent
FROM   status_table
GROUP  BY 1
ORDER  BY percent DESC, ref_time DESC;


ref_time
total
processed
percent


27794170
3
3
100.00

27794160
4
4
100.00

27794150
2
2
100.00

27794145
1
1
100.00

27793450
3
2
66.67

27793440
3
2
66.67



SELECT 6


-- Demonstrate selection process
SELECT *, count(*) FILTER (WHERE drop) OVER (ORDER  BY ref_time DESC) AS drops
FROM  (
   SELECT ref_time, count(*)
        , lag(count(*)) OVER (ORDER BY ref_time DESC) >= count(*) IS TRUE AS drop
   FROM   status_table
   GROUP  BY ref_time
   HAVING bool_and(processed)
   ORDER  BY ref_time DESC, drop
   ) sub;


ref_time
count
drop
drops


27794170
3
f
0

27794160
4
f
0

27794150
2
t
1

27794145
1
t
2



SELECT 4


WITH sel AS (
   SELECT ref_time
   FROM  (
      SELECT ref_time
           , count(*) FILTER (WHERE drop) OVER (ORDER BY ref_time DESC) AS drops
      FROM  (
         SELECT ref_time
              , lag(count(*)) OVER (ORDER BY ref_time DESC) >= count(*) IS TRUE AS drop
         FROM   status_table
         GROUP  BY ref_time
         HAVING bool_and(processed)
         ) sub1
      ) sub2
   WHERE drops > 0
   )
DELETE FROM status_table d
USING  sel s
WHERE  d.ref_time = s.ref_time;

DELETE 3


SELECT ref_time
     , count(*) AS total
     , count(*) FILTER (WHERE processed) AS processed
     , round(count(*) FILTER (WHERE processed) * 100.0 / count(*), 2) AS percent
FROM   status_table
GROUP  BY 1
ORDER  BY percent DESC, ref_time DESC;


ref_time
total
processed
percent


27794170
3
3
100.00

27794160
4
4
100.00

27793450
3
2
66.67

27793440
3
2
66.67



SELECT 4
ref_time	total	processed	percent
27794170	3	3	100.00
27794160	4	4	100.00
27794150	2	2	100.00
27794145	1	1	100.00
27793450	3	2	66.67
27793440	3	2	66.67