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
),t2 as(
    select
        order_date as order_date
        ,tp.member_CODE as code
        ,case m.optout_type
            when 0 then '可'
            when 1 then '不可'
            else '不明'
        end as OPTOUT
        ,row_number()over(partition by tp.member_code order by order_date)as rn
    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'
         
    order by
        ORDER_DATE desc
        ,code desc
    )
    
select
    order_date
    ,code
    ,OPTOUT
from
    t2
where
    rn=1
order by
        ORDER_DATE desc
        ,code desc