WITH FirstOrder AS (
select MIN(ORDER_DATETIME) as first_order,MEMBER_CODE
from EC_ORDERS
where  strftime('%Y-%m', ORDER_DATETIME) = '2023-07'
group by MEMBER_CODE
order by ORDER_DATETIME)

SELECT fo.first_order as ORDER_DATE,fo.MEMBER_CODE as CODE,
    CASE 
        WHEN MEMBER_MST.OPTOUT_TYPE = 0 THEN '可'
        WHEN MEMBER_MST.OPTOUT_TYPE = 1 THEN '不可'
        ELSE '不明'
    END AS OPTOUT
FROM FirstOrder as fo
left join MEMBER_MST on fo.MEMBER_CODE=MEMBER_MST.MEMBER_CODE
WHERE EXISTS (
    SELECT 1
    FROM EC_ORDERS ot
    WHERE ot.MEMBER_CODE = fo.MEMBER_CODE
    AND ot.ORDER_DATETIME < DATE(fo.first_order, '-1 year')
)order by ORDER_DATE desc,CODE desc;