with first_order AS(
    select
        MEMBER_CODE
        ,MIN(datetime(ORDER_DATETIME)) AS first_order
    from
        EC_ORDERS
    where
        date(ORDER_DATETIME) between date('2023-07-01') and date('2023-07-31')
    group by
        MEMBER_CODE
),
before_first_order AS(
    select
        e.MEMBER_CODE
        ,MAX(datetime(e.ORDER_DATETIME)) AS before_order
    from
        EC_ORDERS e
    inner join
        first_order f
    on datetime(e.ORDER_DATETIME) < f.first_order
    and e.MEMBER_CODE = f.MEMBER_CODE
    group by
        e.MEMBER_CODE
),
target AS(
    select
        f.MEMBER_CODE
        ,b.before_order
        ,f.first_order
        ,cast(julianday(f.first_order) - julianday(b.before_order) as integer)
    from
        first_order f
    inner join
        before_first_order b
    on f.MEMBER_CODE = b.MEMBER_CODE
    where
        cast(julianday(first_order) - julianday(before_order) as integer) > 365
)
select
    t.first_order AS ORDER_DATE
    ,t.MEMBER_CODE AS CODE
    ,CASE m.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 ORDER_DATE desc, CODE desc
;