WITH SUB1 AS(
    SELECT
        MEMBER_CODE,
        ORDER_DATETIME AS OD,
        LAG(ORDER_DATETIME) OVER(
            PARTITION BY MEMBER_CODE
            ORDER BY ORDER_DATETIME
            ) AS PRE_OD
    FROM
        EC_ORDERS
    ),
SUB2 AS(
    SELECT
        MEMBER_CODE,
        OD,
        ROW_NUMBER() OVER(PARTITION BY MEMBER_CODE ORDER BY OD) AS NUM,
        PRE_OD
    FROM
        SUB1
    WHERE
        DATE(OD) BETWEEN '2023-07-01' AND '2023-07-31'
    )
SELECT
    OD AS ORDER_DATE,
    SUB2.MEMBER_CODE AS CODE,
    CASE
        WHEN OPTOUT_TYPE=0 THEN '可'
        WHEN OPTOUT_TYPE=1 THEN '不可'
        ELSE '不明'
    END AS OPTOUT
FROM
    SUB2
    INNER JOIN MEMBER_MST AS MM
        ON MM.MEMBER_CODE=SUB2.MEMBER_CODE
WHERE
    NUM=1
    AND DATE(PRE_OD) < DATE(DATE(OD),'-1 years')
ORDER BY
    ORDER_DATE DESC,
    CODE DESC
;