Postgres 15

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

--https://stackoverflow.com/q/74612647/5298879
--https://stackoverflow.com/a/74613093/5298879
CREATE TABLE publish_queue 
(   id	            text,
    team_member_id	text,
    message_id	    text,
    campaign_id	    text,
    react_to_publish_queue_id	text
);

INSERT INTO publish_queue 
(id, team_member_id, message_id, campaign_id, react_to_publish_queue_id) 
  VALUES 
('1859001', '8566', '1171', '51543', '205193'),
('1859002', '8566', '1172', '51543', '205193'),
('1859003', '8566', '1173', '51543', '205193'),
('1859004', '8566', '1174', '51543', '205193');

CREATE TABLE campaign 
(   id	             text,
    team_member_id	 text,
    message_id	     text,
    publish_start_at timestamp,
    publish_end_at	 timestamp,
    react_to_publish_queue_id	text,
    reshare_publish_queue_id	text
);

INSERT INTO campaign 
(id, team_member_id, message_id, publish_start_at, publish_end_at, react_to_publish_queue_id) 
  VALUES 
('51543', '8566', '1171', '2022-11-29 10:59:07',     NULL, '1859001'),
('51544', '8566', '1171', '2022-11-29 10:59:07',     NULL, '1859001'),
('51545', '8566', '1171', now()-'1 month'::interval, NULL, '1859001'),--should be status `published` (start more than 1 minute ago, null end)
('51546', '8566', '1171', now()-'2h'::interval,      now()-'30s'::interval, '1859001'),--should be status `published` (start more than 1 minute ago, end known and passed)
('51547', '8566', '1171', now()-'2min'::interval,    now()+'2H'::interval, '1859001'),--`running`(start more than 1 minute ago, but end is known and wasn't reached yet)
('51558', '8566', '1171', now(),                     NULL, '1859001'), --`running`(start less than 1 minute ago, null end)
('51559', '8566', '1171', now()-'30s'::interval,     now()-'15s'::interval, '1859001'); --`published`(start less than 1 minute ago, but end is known and already passed within the 1 minute)

CREATE TABLE

INSERT 0 4

CREATE TABLE

INSERT 0 7

SELECT
    publish_queue.id,
    CASE
        WHEN publish_queue.message_id IS NOT NULL
        THEN
            (
             SELECT ARRAY_AGG(id) FROM campaign WHERE react_to_publish_queue_id = publish_queue.id
             AND (   (    publish_end_at IS NULL
                      AND publish_start_at BETWEEN CURRENT_TIMESTAMP - INTERVAL '1 minute' AND CURRENT_TIMESTAMP
                     )
                  OR (    publish_end_at IS NOT NULL
                      AND CURRENT_TIMESTAMP BETWEEN publish_start_at AND publish_end_at
                     )
             )
        )
        ELSE '{}'
    END AS running,
    CASE
        WHEN publish_queue.message_id IS NOT NULL
        THEN
            (SELECT ARRAY_AGG(id) FROM campaign WHERE react_to_publish_queue_id = publish_queue.id
            AND (   (    publish_end_at IS NULL
                     AND publish_start_at < CURRENT_TIMESTAMP - INTERVAL '1 minute'
                    )
                 OR (    publish_end_at IS NOT NULL
                     AND CURRENT_TIMESTAMP > publish_end_at
                    )
             )
        )
        ELSE '{}'
    END AS published
FROM publish_queue
WHERE publish_queue.id = '1859001';

id	running	published
1859001	{51547,51558}	{51543,51544,51545,51546,51559}

SELECT 1