with simple as (
    select
        MEMBER_CODE
        , ORDER_DATETIME
    from
        EC_ORDERS
), first_orders_7 as (
    select
        MEMBER_CODE
        , FIRST_VALUE(ORDER_DATETIME) over(partition by MEMBER_CODE order by ORDER_DATETIME asc) as 'FIRST_ORDER_7'
    from
        simple
    where
        ORDER_DATETIME between '2023-07-01 00:00:00' and '2023-07-31 23:59:59'
), past_orders as (
    select
        MEMBER_CODE
        ,FIRST_VALUE(ORDER_DATETIME) over(partition by MEMBER_CODE order by ORDER_DATETIME desc) as 'LAST_ORDER'
    from
        simple
    where
        ORDER_DATETIME < (select FIRST_ORDER_7 from first_orders_7 f inner join simple s on f.MEMBER_CODE = s.MEMBER_CODE)
), target as (
    select
        distinct f.MEMBER_CODE
        , FIRST_ORDER_7
        , LAST_ORDER
    from
        first_orders_7 f
        inner join past_orders p on f.MEMBER_CODE = p.MEMBER_CODE
    where
        LAST_ORDER < datetime(FIRST_ORDER_7, '-1 years')
)
select
    FIRST_ORDER_7 as 'ORDER_DATE'
    , t.MEMBER_CODE as 'CODE'
    , case OPTOUT_TYPE 
        when 0 then '可'
        when 1 then '不可'
        else '不明'
    end as 'OPTOUT'
from 
    target t
    inner join MEMBER_MST m on t.MEMBER_CODE = m.MEMBER_CODE
order by
    FIRST_ORDER_7 desc,
    t.MEMBER_CODE desc;