您可以聚合和使用MAX(...) KEEP (DENSE_RANK FIRST...)：

SELECT MAX(due_date) AS due_date,
       MAX(status) KEEP (
         DENSE_RANK FIRST
         ORDER BY CASE status
                  WHEN 'Completed' THEN 1
                  WHEN 'Scheduled' THEN 3
                                   ELSE 2
                  END ASC,
                  COALESCE(due_date, completed_date) DESC
       ) AS status,
       activity_name,
       MAX(completed_date) AS completed_date
FROM   table_name
GROUP BY activity_name

其中，对于样本数据：

CREATE TABLE table_name (Due_Date, Status, Activity_Name, Completed_date) AS
SELECT NULL,              'Completed',  'Maintenance (ADV)',             DATE '2023-02-23' FROM DUAL UNION ALL
SELECT DATE '2023-04-09', 'Assessment', 'Maintenance (ADV)',             NULL FROM DUAL UNION ALL
SELECT NULL,              'Completed',  'Records Management Standards',  DATE '2023-04-10' FROM DUAL UNION ALL
SELECT DATE '2023-04-16', 'Pending',    'Records Management Standards',  NULL FROM DUAL UNION ALL
SELECT DATE '2023-04-16', 'Assessment', 'Records Management Standards',  NULL FROM DUAL UNION ALL
SELECT DATE '2023-03-10', 'Scheduled',  'Records Management Standards',  NULL FROM DUAL UNION ALL
SELECT NULL,              'Completed',  'Monitor Compliance',            DATE '2023-02-14' FROM DUAL UNION ALL
SELECT DATE '2023-04-09', 'Assessment', 'Monitor Compliance',            NULL FROM DUAL UNION ALL
SELECT NULL,              'Completed',  'Monitor Customer',              DATE '2023-02-23' FROM DUAL UNION ALL
SELECT DATE '2023-04-09', 'Pending',    'Monitor Customer',              NULL FROM DUAL;

输出：
| 到期日|状态|活动名称|完成日期|
| --------------|--------------|--------------|--------------|
| 2019 -04-09 00：00：00|已完成|维护（ADV）|2019 -02-23 00：00：00|
| 2019 -04-09 00：00：00|已完成|监查员合规性|2019 -02-14 00：00：00|
| 2019 -04-09 00：00：00|已完成|监控客户|2019 -02-23 00：00：00|
| 2019 -04-16 00：00：00|已完成|档案管理标准|2019 -04-10 00：00：00|
fiddle

使用max函数（对于due_date）的解析形式。
样本数据：

SQL> select * From activity_history order by activity_name, due_date;

DUE_DATE   STATUS     ACTIVITY_NA COMPLETED
---------- ---------- ----------- ----------
09.04.2023 assessment maintenance
           completed  maintenance 23.02.2023
09.04.2023 assessment monitor
           completed  monitor     14.02.2023
10.04.2023 scheduled  records
16.04.2023 assessment records
16.04.2023 pending    records
           completed  records     10.03.2023

8 rows selected.

查询（检查第3行）：

SQL> with temp as (
  2    select a.*,
  3      max(due_date) over (partition by activity_name) max_due_date,
  4      row_number() over (partition by a.activity_name
  5                         order by case when status = 'completed' then 1
  6                                       when status = 'scheduled' then 3
  7                                       else 2
  8                                  end
  9                        ) rn
 10    from activity_history a
 11  )
 12  select max_due_date, status, activity_name, completed
 13  from temp
 14  where rn = 1
 15  order by activity_name;

MAX_DUE_DA STATUS     ACTIVITY_NA COMPLETED
---------- ---------- ----------- ----------
09.04.2023 completed  maintenance 23.02.2023
09.04.2023 completed  monitor     14.02.2023
16.04.2023 completed  records     10.03.2023

SQL>