with orders as (
    select
        order_no,
        member_code,
        datetime(order_datetime) as ordered_at,
        lag(datetime(order_datetime)) over (partition by member_code order by datetime(order_datetime)) as prev_ordered_at
    from EC_ORDERS
),

min_orders as (
    select
        member_code,
        min(ordered_at) as ordered_at,
        min(prev_ordered_at) as prev_ordered_at
    from orders
    where ordered_at >= '2023-07-01' and ordered_at < '2023-08-01'
    group by 1
),

period as (
    select
        *,
        cast(julianday(ordered_at) - julianday(prev_ordered_at) as int) as period
    from min_orders
),

joined as (
    select
        p.member_code,
        p.ordered_at,
        p.period,
        case 
         when m.optout_type = 0 then '可'
         when m.optout_type = 1 then '不可'
        else '不明' end as optout
    from period p
    inner join member_mst m on p.member_code = m.member_code
)

select
    ordered_at as ORDER_DATE,
    member_code as CODE,
    optout as OPTOUT
from joined
where period > 365
order by 1 desc, 2 desc;