你可以试试这个：

with cte as (
  select id, p_id, status,
  sum(case when STATUS = 'Completed' then 1 else 0 end) over (partition by ID order by insert_time desc) isCompleted
  from test order by id
),
cte2 as (
    select id, p_id, status, isCompleted,
    row_number() over (partition by ID order by
              case when STATUS = 'Unsuccessful' then 1
                   when STATUS = 'Initiated' then 2
              end ) AS status_rank
    from cte
    where STATUS <> 'Completed' and isCompleted = 1 
)
select id, p_id, status
from cte2
where status_rank = 1
order by id

结果：

ID  P_ID    STATUS
1   A2      Initiated
2   A2      Unsuccessful

首先，cte用于查看每个ID是否有任何已完成的标志，然后cte2用于根据特定顺序按状态对记录进行排序（首先是未成功，然后是已启动），然后我们将获取最新的记录。
当我们必须在条件的基础上对数据进行排序时，可以使用按情况排序，并定义基于特定条件进行排序的标准。
Demo here

是否应该在id上进行分区（而不是p_id）？

SQL>   SELECT id,
  2           p_id,
  3           status,
  4           RANK () OVER (PARTITION BY id ORDER BY insert_time DESC)    rnk
  5      FROM test
  6  ORDER BY id, rnk;

        ID P_ID       STATUS                      RNK
---------- ---------- -------------------- ----------
         1 A2         Initiated                     1   --> this
         1 A1         Completed                     2
         1 A1         Initiated                     3
         2 A2         Unsuccessful                  1   --> this
         2 A1         Completed                     2
         2 A1         Initiated                     3

6 rows selected.

SQL>

现在只返回排名最高的行是一个简单的任务;你已经知道如何通过使用上面的查询作为子查询来做到这一点。

SQL>   SELECT id, p_id, status
  2      FROM (SELECT id,
  3                   p_id,
  4                   status,
  5                   RANK () OVER (PARTITION BY id ORDER BY insert_time DESC)    rnk
  6              FROM test)
  7     WHERE rnk = 1
  8  ORDER BY id;

        ID P_ID       STATUS
---------- ---------- --------------------
         1 A2         Initiated
         2 A2         Unsuccessful

SQL>

据我所知，您希望忽略状态为completed的(id, p_id)。然后对于那些incompleted(id, p_id)，您想知道它们的最新状态。如果这是你想要的，我们可以有一个像下面这样的查询：

select id, p_id, status
from (
    select 
    id, p_id, status, insert_time,
    row_number() over (partition by id, p_id order by insert_time desc) as rank_
    --rank for selecting the lastest status of incompleted (id, p_id)
    from test 
    where (id, p_id) not in ( -- filter all (id, p_id) that are completed
        select id, p_id
        from test
        where status = 'Completed'
        group by id, p_id
    )
) as rank_table
where rank_=1;