Postgres 14

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

select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE status_table(
   name       text NOT NULL PRIMARY KEY
  ,processed  BOOLEAN  NOT NULL
  ,processing BOOLEAN  NOT NULL
  ,updated    INTEGER  NOT NULL
  ,ref_time   INTEGER  NOT NULL
);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('abc','t','f',27794395,27794160);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('def','f','f',27794395,27793440);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('ghi','t','f',27794395,27793440);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('jkl','t','f',27794395,27794160);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('mno','t','f',27794395,27793440);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('pqr','t','t',27794395,27794160);

INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('stu','t','t',27794395,27794160);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('vwx','t','t',27794395,27794160);

INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('cba','t','f',27794395,27794170);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('fed','f','f',27794395,27793450);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('ihg','t','f',27794395,27793450);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('lkj','t','f',27794395,27794170);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('onm','t','f',27794395,27793450);
INSERT INTO status_table(name,processed,processing,updated,ref_time) VALUES ('rqp','t','t',27794395,27794170);

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select * from status_table order by ref_time desc;


name
processed
processing
updated
ref_time


lkj
t
f
27794395
27794170

rqp
t
t
27794395
27794170

cba
t
f
27794395
27794170

stu
t
t
27794395
27794160

abc
t
f
27794395
27794160

vwx
t
t
27794395
27794160

jkl
t
f
27794395
27794160

pqr
t
t
27794395
27794160

fed
f
f
27794395
27793450

ihg
t
f
27794395
27793450

onm
t
f
27794395
27793450

mno
t
f
27794395
27793440

ghi
t
f
27794395
27793440

def
f
f
27794395
27793440



SELECT 14


select ref_time, 
    count(*) cnt_total,
    count(*) filter(where processed) cnt_processed,
    round(avg(processed::int),2) ratio_processed
from status_table
group by ref_time order by ratio_processed desc, ref_time desc;


ref_time
cnt_total
cnt_processed
ratio_processed


27794170
3
3
1.00

27794160
5
5
1.00

27793450
3
2
0.67

27793440
3
2
0.67



SELECT 4


with 
    ref as (
        select ref_time 
        from status_table 
        group by ref_time 
        having bool_and(processed)
        order by ref_time desc
        offset 1
    )
delete from status_table s
using ref r
where s.ref_time = r.ref_time

DELETE 5


select ref_time, 
    count(*) cnt_total,
    count(*) filter(where processed) cnt_processed,
    round(avg(processed::int),2) ratio_processed
from status_table
group by ref_time order by ratio_processed desc, ref_time desc;


ref_time
cnt_total
cnt_processed
ratio_processed


27794170
3
3
1.00

27793450
3
2
0.67

27793440
3
2
0.67



SELECT 3

name	processed	processing	updated	ref_time
lkj	t	f	27794395	27794170
rqp	t	t	27794395	27794170
cba	t	f	27794395	27794170
stu	t	t	27794395	27794160
abc	t	f	27794395	27794160
vwx	t	t	27794395	27794160
jkl	t	f	27794395	27794160
pqr	t	t	27794395	27794160
fed	f	f	27794395	27793450
ihg	t	f	27794395	27793450
onm	t	f	27794395	27793450
mno	t	f	27794395	27793440
ghi	t	f	27794395	27793440
def	f	f	27794395	27793440