with t as(
select
    distinct
    MEMBER_CODE
    ,ORDER_DATETIME as ORDER_DATE
from
    EC_ORDERS
)
,t_with_previous as(
select
    *
    ,lag(ORDER_DATE)over(partition by MEMBER_CODE order by ORDER_DATE)as previsous_day
from
    t
)
select
    min(order_date) as order_date
    ,tp.member_CODE as code
    ,min(case m.optout_type
        when 0 then '可'
        when 1 then '不可'
        else '不明'
    end) as OPTOUT
from
    t_with_previous as tp
    join MEMBER_MST as m
    on tp.member_code=m.member_code
    
where
    strftime('%Y',order_date)-strftime('%Y',previsous_day)>=1
    and
    
    date(ORDER_DATE) between '2023-07-01' and '2023-07-31'
group by
    tp.member_CODE
order by
    ORDER_DATE desc
    ,code desc