Update column in table A based off columns in table B which have to meet a condition

Question

Hi I have 2 tables to model a vacation request and the approvers who will approve the request. A request can have several approvers.

When an approver approves, the approver row has its approved_at column set to the current date.

The request table also has an approved_at column. This is set when ALL the approvers have approved and it is set to the most recent approver's approved_at date.

I need to backfill the requests table's approved at column with the most recent approver's approved_at time but only if all the approvers have approved.

I have solved it using a CTE with window functions but I'm wondering what other ways are there to solve this? I'd prefer a solution compliant with postgres.

Here's my solution

create table reqs (
  id serial primary key,
  approved_at date
);

create table req_approvers (
  id serial primary key,
  req_id int,
  approved_at date
);

insert into reqs (id) values (1),(2),(3);

insert into req_approvers (req_id, approved_at)
values
(1, '2022-01-01'),
(1, '2021-01-01'),
(1, NULL),
(1, '2020-01-01'),
(2, '1999-01-01'),
(2, '1979-01-01');

-- View the data by joining the two tables on the req_id 

select 
reqs.id as req_id, 
reqs.approved_at as req_approved_at, 
req_approvers.id as req_apvr_id,
req_approvers.req_id as req_apvr_req_id,
req_approvers.approved_at as req_apvr_approved_at
from reqs
left outer join req_approvers on req_approvers.req_id = reqs.id;


/*
 req_id | req_approved_at | req_apvr_id | req_apvr_req_id | req_apvr_approved_at
--------+-----------------+-------------+-----------------+----------------------
      1 |                 |           1 |               1 | 2022-01-01
      1 |                 |           2 |               1 | 2021-01-01
      1 |                 |           3 |               1 |
      1 |                 |           4 |               1 | 2020-01-01
      2 |                 |           5 |               2 | 1999-01-01
      2 |                 |           6 |               2 | 1979-01-01
      3 |                 |             |                 |
*/

-- From the above only req 2 has been fully approved so only its approved_at column should be set to the date 1999-01-01


WITH req_approval_stats AS (
    SELECT
            DISTINCT reqs.id
            -- get the most recent approvers approval date
            ,MAX (req_approvers.approved_at) OVER (
                PARTITION BY reqs.id
            ) AS approved_at
            -- get the total number of approvers for this request
            ,COUNT (req_approvers.id) OVER (
                PARTITION BY reqs.id
            ) AS total_approvers
            -- get the total number of approvers who have approved for this request
            ,COUNT (req_approvers.approved_at) OVER (
                PARTITION BY reqs.id
            ) AS total_approved
        FROM
            reqs INNER JOIN req_approvers
                ON reqs.id = req_approvers.req_id
) UPDATE
        reqs
    SET
        approved_at = (
            SELECT
                    req_approval_stats.approved_at
                FROM
                    req_approval_stats
                WHERE
                    req_approval_stats.total_approvers = req_approval_stats.total_approved
                    AND req_approval_stats.id = reqs.id LIMIT 1
        )


-- view the data again and its correct 
 
/*
 req_id | req_approved_at | req_apvr_id | req_apvr_req_id | req_apvr_approved_at
--------+-----------------+-------------+-----------------+----------------------
      1 |                 |           1 |               1 | 2022-01-01
      1 |                 |           2 |               1 | 2021-01-01
      1 |                 |           3 |               1 |
      1 |                 |           4 |               1 | 2020-01-01
      2 | 1999-01-01      |           5 |               2 | 1999-01-01
      2 | 1999-01-01      |           6 |               2 | 1979-01-01
      3 |                 |             |                 |
*/