SELECT
    ORDER_DATETIME AS ORDER_DATE,
    MEMBER_CODE AS CODE,
    CASE
        OPTOUT_TYPE
        WHEN 0 THEN '可'
        WHEN 1 THEN '不可'
        ELSE '不明'
    END AS OPTOUT
FROM
    (
        SELECT
            ORDER_NO,
            MEMBER_CODE,
            ORDER_DATETIME,
            LAG (ORDER_DATETIME) OVER(
                PARTITION BY MEMBER_CODE
                ORDER BY
                    ORDER_DATETIME ASC
            ) AS PRE_ORDER_DATETIME
        FROM
            EC_ORDERS
    ) AS Q
    INNER JOIN MEMBER_MST AS A USING(MEMBER_CODE)
WHERE
    '2023-07-01' <= ORDER_DATETIME
    AND ORDER_DATETIME < '2023-08-01'
    AND DATE(PRE_ORDER_DATETIME, '+1 YEAR') < DATE(ORDER_DATETIME)
ORDER BY
    ORDER_DATETIME DESC,
    MEMBER_CODE DESC;