with a as (select MEMBER_CODE
                , min(case
                          when DATE(ORDER_DATETIME) between '2023-07-01' and '2023-08-00' then ORDER_DATETIME
                          else null end) as mindt
                , max(case
                          when DATE(ORDER_DATETIME) < '2023-07-00' then ORDER_DATETIME
                          else null end) as maxdt
           from EC_ORDERS
           group by MEMBER_CODE)

select max(a.mindt) ORDER_DATE
     , me.MEMBER_CODE as CODE
     , max(case ifnull(OPTOUT_TYPE, 3)
               when 0 then '可'
               when 1 then '不可'
               else '不明' end) as OPTOUT


from EC_ORDERS ec
     join MEMBER_MST me on ec.MEMBER_CODE = me.MEMBER_CODE
     join a on a.MEMBER_CODE = ec.MEMBER_CODE
where maxdt is not null
  and DATE(maxdt) < DATE(a.mindt, '-1 years')
  and mindt is not null
group by ec.MEMBER_CODE
order by 1 desc, 2 desc