WITH SUB AS(
    SELECT
        MEMBER_CODE
        ,FIRST_NAME
        ,LAST_NAME
        ,MIN(ORDER_DATETIME) AS ORDER_DATETIME
        ,MIN(DATE(ORDER_DATETIME)) AS M_DATE
    FROM
        EC_ORDERS
    WHERE
        DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
    GROUP BY
        MEMBER_CODE
)
SELECT
    SUB.ORDER_DATETIME AS ORDER_DATE
    ,SUB.MEMBER_CODE AS CODE
    ,CASE MM.OPTOUT_TYPE
        WHEN 0 THEN '可'
        WHEN 1 THEN '不可'
        ELSE '不明'
        END AS OPTOUT
FROM
    SUB
        INNER JOIN MEMBER_MST AS MM
            ON SUB.MEMBER_CODE == MM.MEMBER_CODE
WHERE
    EXISTS(
        SELECT
            ORDER_NO
        FROM
            EC_ORDERS
        WHERE
            SUB.MEMBER_CODE = MEMBER_CODE
            AND DATE(ORDER_DATETIME) < DATE(SUB.M_DATE, '-1 year')
    )
    AND NOT EXISTS(
        SELECT
            ORDER_NO
        FROM
            EC_ORDERS
        WHERE
            SUB.MEMBER_CODE = MEMBER_CODE
            AND DATE(ORDER_DATE) BETWEEN DATE(SUB.M_DATE, '-1 year') AND DATE(SUB.M_DATE, '-1 day')
    )
ORDER BY
    SUB.ORDER_DATETIME DESC
    ,SUB.MEMBER_CODE DESC