with order7 as (
SELECT
    t1.MEMBER_CODE,min(ORDER_DATETIME) as F_ORDER_DATETIME
FROM
    EC_ORDERS t1
WHERE
    t1.ORDER_DATETIME between '2023-07-01 00:00:00' and '2023-07-31 23:59:59'
group by t1.MEMBER_CODE 
),
memberPerOrder as (
SELECT
    distinct t1.MEMBER_CODE,t1.ORDER_DATETIME
FROM
    EC_ORDERS t1
order by t1.MEMBER_CODE,t1.ORDER_DATETIME desc
)

select
    distinct *
From(
    select
        t1.F_ORDER_DATETIME as ORDER_DATE
        ,t1.member_code as CODE
        ,case 
            when OPTOUT_TYPE = '0' then
                '可'
            when OPTOUT_TYPE = '1' then
                '不可'
            else
                '不明'
        end as OPTOUT
    from
        order7 t1
    inner join memberPerOrder t2
    on t1.member_code = t2.member_code
    and t2.ORDER_DATETIME < t1.F_ORDER_DATETIME
    and t2.ORDER_DATETIME < t1.F_ORDER_DATETIME
    and strftime('%Y%m%d',t1.F_ORDER_DATETIME) - strftime('%Y%m%d',t2.ORDER_DATETIME) > 10000
    inner join MEMBER_MST t3
    on t3.member_code = t1.member_code
    order by t1.F_ORDER_DATETIME desc,t1.member_code desc
)T