您可以使用分析函数查找最新的行，并检查之前是否有S活动，以便您只查询一次表：

SELECT id, activity, dt
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt DESC) AS rn,
         FIRST_VALUE(CASE activity WHEN 'S' THEN 'S' END IGNORE NULLS) OVER (
             PARTITION BY id ORDER BY dt DESC
             ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
           ) AS has_activity_s
  FROM   table_name t
)
WHERE rn = 1
AND   has_activity_s IS NULL;

其中，对于样本数据：

CREATE TABLE table_name (id, activity, dt) AS
  SELECT 1111, 'S', DATE '2023-04-10' FROM DUAL UNION ALL
  SELECT 2222, 'M', DATE '2023-04-11' FROM DUAL UNION ALL
  SELECT 3333, 'S', DATE '2023-04-10' FROM DUAL UNION ALL
  SELECT 1111, 'M', DATE '2023-04-11' FROM DUAL UNION ALL
  SELECT 3333, 'M', DATE '2023-04-11' FROM DUAL UNION ALL
  SELECT 4444, 'S', DATE '2023-04-11' FROM DUAL UNION ALL
  SELECT 5555, 'M', DATE '2023-04-10' FROM DUAL UNION ALL
  SELECT 5555, 'M', DATE '2023-04-11' FROM DUAL;

输出：
| ID|活动|DT|
| --------------|--------------|--------------|
| 二二二二|M|2019 -04-11 00：00：00|
| 五五五五|M|2019 -04-11 00：00：00|
fiddle

这里有一个选择
(Just设置日期格式，知道什么是什么;从您发布的数据中不清楚，例如2023/04/10可以是4月10日以及10月4日）。

SQL> alter session set nls_date_format = 'yyyy/mm/dd';

Session altered.

样本数据：

SQL> with test (id, activity, datum) as
  2    (select 1111, 'S', date '2023-04-10' from dual union all
  3     select 2222, 'M', date '2023-04-11' from dual union all
  4     select 3333, 'S', date '2023-04-10' from dual union all
  5     select 1111, 'M', date '2023-04-11' from dual union all
  6     select 3333, 'M', date '2023-04-11' from dual union all
  7     select 4444, 'S', date '2023-04-11' from dual union all
  8     select 5555, 'M', date '2023-04-10' from dual union all
  9     select 5555, 'M', date '2023-04-11' from dual
 10    )

查询从这里开始;temp CTE按datum的每一个ID对所有行（对于没有活动S的ID）进行降序排序：

11  temp as
 12    (select a.id, a.activity, a.datum,
 13       row_number() over (partition by a.id order by a.datum desc) rn
 14     from test a
 15     where not exists (select null
 16                       from test b
 17                       where b.id = a.id
 18                         and b.activity = 'S'
 19                      )
 20    )

最后，返回排名最高的行：

21  select id, datum
 22  from temp
 23  where rn = 1
 24  order by id;

        ID DATUM
---------- ----------
      2222 2023/04/11
      5555 2023/04/11

SQL>

假设'M'〈'S'

with mx as(
  SELECT tbl.*, MAX(Activity) over (partition by ID) ma, max(Dt) over (partition by ID) mdt
  FROM tbl
) 
select ID, Activity, Dt
from mx
where ma ='M' and dt = mdt

还有一个：

select * from data d1
where d1.Activity = 'M' and not exists(
    select 1 from data d2
    where (d1.id = d2.id) and (
        (d2.dat <= d1.dat and d2.Activity = 'S')
        or (d2.dat > d1.dat and d2.Activity = 'M')
    )
)