不完全清楚，但我希望下面的答案给你一个提示，如果不是解决方案。

select  no,
       `group`,
        case when flag='Y' then mx_dt else `date` end as new_date,
        value,
        flag
from (    select no,
                 `group`,
                  value,
                  `date`,
                  flag ,
                  row_number() over(partition by `group` order by `date` asc ) as rn,
                  max(`date`) over (partition by `group`,(case when flag  <> 'Y' then `date`  end)   ) mx_dt
          from raw_data
    ) as tbl
where rn=1;

如果标志=Y，则上述代码将选择每组的最大值（日期），否则将采用每行的日期。
https://dbfiddle.uk/JhRUti2h

解决方案是自联接源表并选择正确的字段，优先考虑最新日期。
下面是一个工作查询：

WITH source_data AS (
SELECT 1 AS no_,  'a' AS group_, CAST('2022-10-13' AS DATE) AS date, 'old' AS value, 'y' AS flag
UNION ALL
SELECT 2, 'a', CAST('2022-10-15' AS DATE), 'new', 'y'
UNION ALL
SELECT 3, 'b', CAST('2022-01-01' AS DATE), 'old', 'n'
UNION ALL
SELECT 4, 'b', CAST('2022-01-03' AS DATE), 'new', 'n')

SELECT no_, group_, COALESCE(new_date, date), value, flag
FROM 
(SELECT * FROM source_data WHERE value = 'old') old_values
LEFT JOIN (SELECT group_ AS new_group, date AS new_date FROM source_data WHERE value = 'new' AND flag='y') new_values
ON old_values.group_ = new_values.new_group

结果如您所料：

no_ group_  f0_ value   flag
1   a   2022-10-15  old y
3   b   2022-01-01  old n