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
    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