select
  t.ORDER_DATETIME as "ORDER_DATE"
 ,t.MEMBER_CODE as "CODE"
 ,(case t.OPTOUT_TYPE when 0 then '可' when 1 then '不可' else '不明' end) as "OPTOUT"
from
 (
  select
    e.MEMBER_CODE as "MEMBER_CODE"
   ,e.ORDER_DATETIME as "ORDER_DATETIME"
   ,m.OPTOUT_TYPE as "OPTOUT_TYPE"
  from
   MEMBER_MST m
  join
   EC_ORDERS e
  on
   m.MEMBER_CODE = e.MEMBER_CODE
  where
   e.ORDER_DATETIME like '2023-07%'
  group by
   e.MEMBER_CODE
  having
   min(datetime(LOGIN_DATETIME))
 ) t
join
  (
  select
    e.MEMBER_CODE as "MEMBER_CODE"
   ,e.ORDER_DATETIME as "ORDER_DATETIME"
   ,m.OPTOUT_TYPE as "OPTOUT_TYPE"
  from
   MEMBER_MST m
  join
   EC_ORDERS e
  on
   m.MEMBER_CODE = e.MEMBER_CODE
  where
   date(datetime(e.ORDER_DATETIME)) < '2023-07-01'
  group by
   e.MEMBER_CODE
  having
   max(datetime(LOGIN_DATETIME))
 ) k
on
 t.MEMBER_CODE = k.MEMBER_CODE
where
 cast(julianday(date(datetime(t.ORDER_DATETIME))) - julianday(date(datetime(k.ORDER_DATETIME))) as integer) >= 365
order by
 "ORDER_DATE" desc, "CODE" desc
;