Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- table for storing data processing status

CREATE TABLE status_table (
  base_name  text      NOT NULL
, version    smallint  NOT NULL
, ref_time   int       NOT NULL
, processed  bool      NOT NULL
, processing bool      NOT NULL
, updated    int       NOT NULL  DEFAULT (extract(epoch from now()) / 60)
, PRIMARY KEY (base_name, version)
);
 
INSERT INTO status_table VALUES 
  ('abc', 1, 27793000, 't', 'f', (extract(epoch from now()) / 60) - 500)
, ('jkl', 1, 27793000, 't', 'f', (extract(epoch from now()) / 60) - 450)
, ('pqr', 1, 27793000, 't', 'f', (extract(epoch from now()) / 60) - 400)
, ('def', 1, 27793300, 't', 'f', (extract(epoch from now()) / 60) - 350)
, ('hft', 1, 27793300, 't', 'f', (extract(epoch from now()) / 60) - 300)
, ('ghi', 1, 27793600, 'f', 't', (extract(epoch from now()) / 60) - 275)
, ('wqt', 1, 27793600, 't', 'f', (extract(epoch from now()) / 60) - 250)
, ('rfe', 1, 27793900, 't', 'f', (extract(epoch from now()) / 60) - 225)
, ('mno', 1, 27793900, 'f', 't', (extract(epoch from now()) / 60) - 175)
, ('dpw', 1, 27793900, 't', 'f', (extract(epoch from now()) / 60) - 150)
, ('eqz', 1, 27794200, 't', 'f', (extract(epoch from now()) / 60) - 125)
, ('fwp', 1, 27794200, 't', 'f', (extract(epoch from now()) / 60) - 100)

CREATE TABLE



INSERT 0 12


SELECT ref_time
  , max(updated) AS max_updated
  , count(*) AS total
  , count(*) FILTER (WHERE processed) AS proc
  , round(count(*) FILTER (WHERE processed) * 100.0 / count(*), 1) AS percent
  , round(round(count(*) FILTER (WHERE processed) * 1.0 / count(*), 1) * 100) AS rounded
  , count(*) FILTER (WHERE processed) = count(*) AS complete
  , max(updated) < (round(extract(epoch from now()) / 60) - 200) AS settled
  , (count(*) FILTER (WHERE processed) = count(*)) AND (max(updated) < (round(extract(epoch from now()) / 60) - 200)) AS ready
FROM status_table
GROUP BY ref_time
ORDER BY ready DESC, rounded DESC, ref_time DESC


ref_time
max_updated
total
proc
percent
rounded
complete
settled
ready


27793300
27808546
2
2
100.0
100
t
t
t

27793000
27808446
3
3
100.0
100
t
t
t

27794200
27808746
2
2
100.0
100
t
f
f

27793900
27808696
3
2
66.7
70
f
f
f

27793600
27808596
2
1
50.0
50
f
t
f



SELECT 5


SELECT ref_time
  , max(updated) AS max_updated
  , count(*) AS total
  , count(*) FILTER (WHERE processed) AS proc
  , round(proc * 100.0 / total, 1) AS percent
  , round(round(proc * 1.0 / total, 1) * 100) AS rounded
  , processed = total AS complete
  , round(extract(epoch from now()) / 60) AS now_mins
  , 200 AS interval_mins
  , max_updated < now_mins - interval_mins AS settled
  , complete AND settled AS ready
FROM status_table
GROUP BY ref_time
ORDER BY ready DESC, rounded DESC, ref_time DESC

ERROR:  column "proc" does not exist
LINE 5:   , round(proc * 100.0 / total, 1) AS percent
                  ^
ref_time	max_updated	total	proc	percent	rounded	complete	settled	ready
27793300	27808546	2	2	100.0	100	t	t	t
27793000	27808446	3	3	100.0	100	t	t	t
27794200	27808746	2	2	100.0	100	t	f	f
27793900	27808696	3	2	66.7	70	f	f	f
27793600	27808596	2	1	50.0	50	f	t	f