WITH JULY AS (
    SELECT
        MEMBER_CODE,
        MIN(DATETIME(ORDER_DATETIME)) AS FIRST_ORDER
    FROM EC_ORDERS
    WHERE DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
    GROUP BY 1
), BEFORE AS (
    SELECT
        EC.MEMBER_CODE,
        JULY.FIRST_ORDER,
        MAX(DATETIME(EC.ORDER_DATETIME)) AS BEFORE
    FROM
        EC_ORDERS EC
    INNER JOIN JULY USING(MEMBER_CODE)
    WHERE EC.ORDER_DATETIME < '2023-07-01'
    GROUP BY 1,2
)
SELECT
    FIRST_ORDER AS ORDER_DATE,
    MEMBER_CODE AS CODE,
    CASE
        WHEN OPTOUT_TYPE = 0 THEN '可'
        WHEN OPTOUT_TYPE = 1 THEN '不可'
        ELSE '不明'
    END AS OPTOUT
FROM
    BEFORE
INNER JOIN MEMBER_MST USING(MEMBER_CODE)
WHERE julianday(FIRST_ORDER)-julianday(BEFORE.BEFORE) >= 366
ORDER BY FIRST_ORDER DESC, MEMBER_CODE DESC