WITH TARGET_MEMBER AS (
    SELECT
        MEMBER_CODE
        ,MIN(ORDER_DATETIME) AS ORDER_DATE
        ,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
),
BEFORE_ORDER AS (
    SELECT
        MEMBER_CODE
        ,MAX(ORDER_DATETIME) AS DIFF_DATE
    FROM
        EC_ORDERS
    WHERE
        DATE(ORDER_DATETIME) < '2023-07-01'
    GROUP BY
        MEMBER_CODE
)
SELECT
    tm.ORDER_DATE AS ORDER_DATE
    ,tm.MEMBER_CODE AS CODE
    ,CASE
        WHEN OPTOUT_TYPE == 0 THEN
            CASE WHEN  DATE(bo.DIFF_DATE) < DATE(tm.ORDER_DATE, '-1 year')
                THEN '可' ELSE '不可' END
        WHEN OPTOUT_TYPE == 1 THEN '不可'
        ELSE '不明'
    END AS OPTOUT
FROM TARGET_MEMBER tm
LEFT OUTER JOIN BEFORE_ORDER bo
    ON tm.MEMBER_CODE = bo.MEMBER_CODE
LEFT OUTER JOIN MEMBER_MST mm
    ON tm.MEMBER_CODE = mm.MEMBER_CODE
WHERE
    DATE(bo.DIFF_DATE) < DATE(tm.ORDER_DATE, '-1 year')
ORDER BY
    tm.ORDER_DATE DESC
    ,tm.MEMBER_CODE DESC;