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
           having mindt is not null
              and maxdt is not null
              and DATE(maxdt) < DATE(mindt, '-1 years'))

select a.mindt ORDER_DATE
     , a.MEMBER_CODE as CODE
     , case ifnull(OPTOUT_TYPE, 3)
           when 0 then '可'
           when 1 then '不可'
           else '不明' end as OPTOUT
from a
     join MEMBER_MST me on a.MEMBER_CODE = me.MEMBER_CODE
order by 1 desc, 2 desc