with member_202307 as (
    select 
        MEMBER_CODE
    from 
        EC_ORDERS
    where
        date(ORDER_DATETIME) between '2023-07-01' and '2023-07-31'
    group by 
        MEMBER_CODE
)
select
    min(ORDER_DATETIME) as ORDER_DATE
    , o.MEMBER_CODE as CODE
    , case
        when OPTOUT_TYPE = 0 then '可'
        when OPTOUT_TYPE = 1 then '不可'
        else '不明' end as OPTOUT
from (
    select
        ORDER_DATETIME
        , lag(ORDER_DATETIME, 1) over(partition by MEMBER_CODE order by ORDER_DATETIME) as ORDER_DATETIME_lag
        , MEMBER_CODE
    from EC_ORDERS
    )o
    inner join
        member_202307 m
        on o.MEMBER_CODE = m.MEMBER_CODE
    left outer join 
        MEMBER_MST mm
        on o.MEMBER_CODE = mm.MEMBER_CODE
where
    ORDER_DATETIME_lag is not null
    and
    date(ORDER_DATETIME, '-1 year') > ORDER_DATETIME_lag
group by 
    o.MEMBER_CODE
order by 
    ORDER_DATETIME desc
    , CODE desc