with t as(
select
    distinct
    MEMBER_CODE
    ,ORDER_DATETIME as ORDER_DATE
from
    EC_ORDERS
)
,t_with_previous as(
select
    *
    ,lag(ORDER_DATE)over(partition by MEMBER_CODE order by ORDER_DATE)as previous_day
from
    t
)
SELECT
    MIN(ORDER_DATE)AS ORDER_DATE
    ,TP.MEMBER_CODE AS CODE
    
    ,case m.optout_type
        when 0 then '可'
        when 1 then '不可'
        else '不明'
    end as OPTOUT    
FROM
     t_with_previous as tp
    join MEMBER_MST as m
    on tp.member_code=m.member_code
WHERE
    ORDER_DATE BETWEEN '2023-07-01' AND '2023-08-01'
    AND
    julianday(DATE(ORDER_DATE)) - julianday(DATE(PREVIOUS_DAY)) > 365.25
    
GROUP BY
    TP.MEMBER_CODE
order by
        ORDER_DATE desc
        ,code desc