with first_2023 as(
    select
        MEMBER_CODE, 
        min(ORDER_DATETIME) as first_2023july
    from EC_ORDERS
    where date(ORDER_DATETIME) between '2023-07-01' and '2023-07-31' 
    group by MEMBER_CODE
),
first_buy as (
    select
        MEMBER_CODE, 
        min(ORDER_DATETIME) as first_date
    from EC_ORDERS
    group by MEMBER_CODE
)
select 
    f2.first_2023july as ORDER_DATE, 
    m.MEMBER_CODE as CODE,
    case m.OPTOUT_TYPE 
        when 0 then '可'
        when 1 then '不可'
        else '不明'
    end as OPTOUT
from first_2023 as f2
    inner join first_buy as fb
        on f2.MEMBER_CODE = fb.MEMBER_CODE
    inner join MEMBER_MST as m
        on f2.MEMBER_CODE = m.MEMBER_CODE
where cast(julianday(f2.first_2023july) - julianday(fb.first_date) as integer) > 365
order by ORDER_DATE desc, CODE desc
;