with seven as (
select
  MEMBER_CODE
 ,min(main.ORDER_DATETIME) as ORDER_DATETIME
from
    EC_ORDERS as main
where
  main.ORDER_DATETIME between '2023-07-01 00:00:00' and '2023-07-31 99:99:99'
group by
  MEMBER_CODE
)
, izen as (
select
  MEMBER_CODE
 ,max(main.ORDER_DATETIME) as ORDER_DATETIME
from
    EC_ORDERS as main
where
  main.ORDER_DATETIME < '2023-06-99 99:99:99'
group by
  MEMBER_CODE
)
, keisan as (
select
  seven.MEMBER_CODE
 ,seven.ORDER_DATETIME
from
  seven
inner join izen on
  seven.MEMBER_CODE = izen.MEMBER_CODE
where
    (julianday(substr(seven.ORDER_DATETIME, 1, 10)) - julianday(substr(izen.ORDER_DATETIME, 1, 10))) > 365
)
select
  main.ORDER_DATETIME as ORDER_DATE
 ,main.MEMBER_CODE as CODE
,case mst.OPTOUT_TYPE
  when 0 then '可'
  when 1 then '不可'
  else '不明'
end
  as OPTOUT
from
keisan as main
left outer join MEMBER_MST as mst on
  mst.MEMBER_CODE = main.MEMBER_CODE
order by
  ORDER_DATE desc
 ,CODE desc