select
  min ORDER_DATE,
  t1.member_code CODE,
  case OPTOUT_TYPE when 0 then '可' when 1 then '不可' else '不明' end OPTOUT
from
  (
    select 
      min,
      max(order_datetime) before,
      ec.member_code
    from
      (select min(order_datetime) min, member_code from ec_orders where order_datetime >= '2023-07-01 00:00:00' group by member_code) t
      left join ec_orders ec
      on ec.member_code = t.member_code
    where
      ec.order_datetime < min
    group by
      ec.member_code, min
  ) t1
  left join MEMBER_MST t2 on t1.member_code = t2.member_code
where
  min >= datetime(before, '+1 year')
order by
  ORDER_DATE desc,
  t1.MEMBER_CODE desc