WITH JULY_FIRST_ORDER AS (
    SELECT
        MEMBER_CODE
        , MIN(ORDER_DATETIME) AS ORDER_DATETIME
    FROM
        EC_ORDERS
    WHERE
        ORDER_DATETIME BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59'
    GROUP BY
        MEMBER_CODE
)

SELECT
    JULY_FIRST_DATE AS ORDER_DATE
    , sub.MEMBER_CODE AS CODE
    , CASE
        WHEN MEMBER_MST.OPTOUT_TYPE = 0 THEN '可'
        WHEN MEMBER_MST.OPTOUT_TYPE = 1 THEN '不可'
        ELSE '不明'
        END AS OPTOUT
FROM (
        SELECT
            EC.MEMBER_CODE
            , MAX(EC.ORDER_DATETIME) AS BEFORE_LAST_DATE
            , MAX(JFO.ORDER_DATETIME) AS JULY_FIRST_DATE
        FROM
            EC_ORDERS AS EC
        INNER JOIN
            JULY_FIRST_ORDER AS JFO ON EC.MEMBER_CODE = JFO.MEMBER_CODE
        WHERE
            EC.ORDER_DATETIME < '2023-07-01 00:00:00'
        GROUP BY
            EC.MEMBER_CODE
    ) AS sub
INNER JOIN
    MEMBER_MST ON sub.MEMBER_CODE = MEMBER_MST.MEMBER_CODE
WHERE
    BEFORE_LAST_DATE < date(JULY_FIRST_DATE, '-1 years') = 1
ORDER BY
    ORDER_DATE DESC
    , CODE DESC
;