with FIRST_ORDER as (
    select
        MEMBER_CODE,
        min(ORDER_DATETIME) as FIRST_ORDER_DATETIME
    from
        EC_ORDERS
    where
        date(ORDER_DATETIME) between "2023-07-01" and "2023-07-31"
    group by
        MEMBER_CODE
),
NOT_FIRST_ORDER as (
    select distinct
        e.MEMBER_CODE,
        e.ORDER_DATETIME as ORDER_DATETIME
    from
        EC_ORDERS e
    join
        FIRST_ORDER f
    on  e.MEMBER_CODE = f.MEMBER_CODE
    where
        ORDER_DATETIME < FIRST_ORDER_DATETIME
),
SECOND_ORDER as (
    select
        MEMBER_CODE,
        min(ORDER_DATETIME) as SECOND_ORDER_DATETIME
    from
        EC_ORDERS
    group by
        MEMBER_CODE
)

select
    FIRST_ORDER_DATETIME as ORDER_DATE,
    f.MEMBER_CODE        as CODE,
    case
        when OPTOUT_TYPE = 0 then "可"
        when OPTOUT_TYPE = 1 then "不可"
        else                      "不明"
    end                  as OPTOUT
from
    FIRST_ORDER f
join
    SECOND_ORDER s
on  f.MEMBER_CODE = s.MEMBER_CODE
join
    MEMBER_MST m
on  f.MEMBER_CODE = m.MEMBER_CODE
where
    datetime(SECOND_ORDER_DATETIME, '+1 year') < datetime(FIRST_ORDER_DATETIME)
order by
    FIRST_ORDER_DATETIME desc,
    f.MEMBER_CODE desc