Oracle 21c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;
10 rows affected

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


DUE_DATE
STATUS
ACTIVITY_NAME
COMPLETED_DATE


2023-04-09 00:00:00
Completed
Maintenance (ADV)
2023-02-23 00:00:00

2023-04-09 00:00:00
Completed
Monitor Compliance
2023-02-14 00:00:00

2023-04-09 00:00:00
Completed
Monitor Customer
2023-02-23 00:00:00

2023-04-16 00:00:00
Completed
Records Management Standards
2023-04-10 00:00:00

DUE_DATE	STATUS	ACTIVITY_NAME	COMPLETED_DATE
2023-04-09 00:00:00	Completed	Maintenance (ADV)	2023-02-23 00:00:00
2023-04-09 00:00:00	Completed	Monitor Compliance	2023-02-14 00:00:00
2023-04-09 00:00:00	Completed	Monitor Customer	2023-02-23 00:00:00
2023-04-16 00:00:00	Completed	Records Management Standards	2023-04-10 00:00:00