select
    a.order_datetime as ORDER_DATE,
    a.member_code as CODE,
    case
        when c.optout_type = 0 then '可'
        when c.optout_type = 1 then '不可'
        else '不可'
    end as OPTOUT


from(
    select 
        member_code,
        min(order_datetime) as order_datetime
    from ec_orders
    where substr(order_datetime, 1, 10) like '2023-07-%'
    group by 1) as a
inner join (
    select
        member_code,
        max(order_datetime) as order_datetime
    from ec_orders
    where julianday(substr(order_datetime, 1, 10)) < julianday('2023-07-01')
    group by 1
    having julianday(substr(max(order_datetime), 1, 10)) < julianday('2022-08-01')
    order by 1) as b
using(member_code)
left outer join member_mst as c
using(member_code)
where julianday(substr(a.order_datetime, 1, 10)) - julianday(substr(b.order_datetime, 1, 10)) > 365
order by 1 desc, 2 desc;