with tmp as 
(
  select 
    member_code 
    ,order_datetime
    ,lag(order_datetime) over (partition by member_code order by order_datetime) as previous_order_datetime 
  from ec_orders 
)
select 
  order_datetime as ORDER_DATE
  ,tmp.member_code as CODE
  ,previous_order_datetime 
  ,case m.optout_type 
    when 0 then '可'
    when 1 then '不可'
    else '不明'
  end as OPTOUT
from tmp 
inner join member_mst as m 
  on tmp.member_code = m.member_code 
where order_datetime >= '2023-07-01'
  and JULIANDAY(order_datetime) - JULIANDAY(previous_order_datetime) > 365
order by ORDER_DATE desc, CODE desc 
;