select 
    min(order_datetime) as ORDER_DATE,
    member_code
from ec_orders
where date(order_datetime) between '2023-07-01' and '2023-07-31'
group by member_code;

select
    max(order_datetime) as max_orderdate,
    member_code
from ec_orders
where order_datetime<'2023-07-01'
group by member_code;

SELECT julianday('2023-07-15') - julianday('2022-07-14');

with mino as (
    select 
        min(order_datetime) as ORDER_DATE,
        member_code
    from ec_orders as o
    where date(order_datetime) between '2023-07-01' and '2023-07-31'
    group by member_code 
),
maxo as (select
        max(order_datetime) as max_orderdate,
        member_code
    from ec_orders
    where date(order_datetime)<'2023-07-01'
    group by member_code
)
select 
    ORDER_DATE,
    mino.member_code as CODE,
    case optout_type
        when 0 then '可'
        when 1 then '不可'
    else '不明'
    end as OPTOUT
from mino
inner join maxo
on mino.member_code=maxo.member_code
inner join member_mst as mem
on mino.member_code=mem.member_code
where julianday(date(order_date))-julianday(date(max_orderdate))>365
order by order_date desc,code desc;