with JUL2023 as (
select
    member_code
    ,min(order_datetime) as OD
from
    EC_ORDERS
where
    date(order_datetime) between '2023-07-01' and '2023-07-31'
group by 1
)


,BEFORE as(
select
    E.member_code
    ,E.order_datetime
    ,J.OD
from
    EC_ORDERS E 
    left outer join 
        JUL2023 J 
        on 
            E.member_code=J.member_code
            and E.order_datetime < J.OD
where
    J.OD is not null
)

,TEMP as (
select
    member_code
    ,datetime(OD) as OD
    ,min(date(order_datetime)) as OD_LAST
from
    BEFORE
group by 1,2
)


select
    OD as ORDER_DATE
    ,T.member_code as CODE
    ,case
        when OPTOUT_TYPE=0 then '可'
        when OPTOUT_TYPE=1 then '不可'
        else '不明'
        end as OPTOUT
from TEMP T 
    left outer join
        MEMBER_MST M
        on 
            T.member_code = M.member_code
where
    (strftime('%s', date(OD)) - strftime('%s', OD_LAST)) > 31536000
order by 1 desc, 2 desc