WITH FirstOrder AS (
    SELECT 
        MEMBER_CODE,
        MIN(ORDER_DATETIME) AS FIRST_ORDER_DATE
    FROM 
        EC_ORDERS
    WHERE 
        ORDER_DATETIME >= '2023-07-01' AND 
        ORDER_DATETIME < '2023-08-01'
    GROUP BY 
        MEMBER_CODE
),
PreviousOrder AS (
    SELECT 
        o.MEMBER_CODE,
        MAX(o.ORDER_DATETIME) AS PREVIOUS_ORDER_DATE
    FROM 
        EC_ORDERS o
    JOIN 
        FirstOrder f ON o.MEMBER_CODE = f.MEMBER_CODE
    WHERE 
        o.ORDER_DATETIME < f.FIRST_ORDER_DATE
    GROUP BY 
        o.MEMBER_CODE
),
EligibleMembers AS (
    SELECT 
        f.FIRST_ORDER_DATE,
        p.PREVIOUS_ORDER_DATE,
        f.MEMBER_CODE
    FROM 
        FirstOrder f
    JOIN 
        PreviousOrder p ON f.MEMBER_CODE = p.MEMBER_CODE
    WHERE 
        (julianday(f.FIRST_ORDER_DATE) - julianday(p.PREVIOUS_ORDER_DATE)) > 365
)
SELECT 
    em.FIRST_ORDER_DATE AS ORDER_DATE,
    em.MEMBER_CODE AS CODE,
    CASE 
        WHEN m.OPTOUT_TYPE = 0 THEN '可'
        WHEN m.OPTOUT_TYPE = 1 THEN '不可'
        ELSE '不明'
    END AS OPTOUT
FROM 
    EligibleMembers em
JOIN 
    MEMBER_MST m ON em.MEMBER_CODE = m.MEMBER_CODE
ORDER BY 
    em.FIRST_ORDER_DATE DESC,
    em.MEMBER_CODE DESC;