SELECT
    ECO1_ORDER_DATETIME AS ORDER_DATE
    ,OPTOUT_TABLE.MEMBER_CODE AS CODE
    ,OPTOUT
FROM (
    SELECT
        MEMBER_CODE
        ,CASE
            WHEN OPTOUT_TYPE = 0 THEN '可'
            WHEN OPTOUT_TYPE = 1 THEN '不可'
            ELSE '不明'
            END AS OPTOUT
    FROM
        MEMBER_MST
) OPTOUT_TABLE
INNER JOIN (
    SELECT
        MEMBER_CODE
        , MIN(ORDER_DATETIME) AS ECO1_ORDER_DATETIME
    FROM
        EC_ORDERS
    WHERE
        DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
    GROUP BY
        MEMBER_CODE
) ECO1
ON
    OPTOUT_TABLE.MEMBER_CODE = ECO1.MEMBER_CODE
INNER JOIN (
    SELECT
        MEMBER_CODE
        , MAX(ORDER_DATETIME) AS ECO2_ORDER_DATETIME
    FROM
        EC_ORDERS
    WHERE
        DATE(ORDER_DATETIME) < '2023-07-01'
    GROUP BY
        MEMBER_CODE
) ECO2
ON
    ECO1.MEMBER_CODE = ECO2.MEMBER_CODE
    AND JULIANDAY(ECO1_ORDER_DATETIME) - JULIANDAY(ECO2_ORDER_DATETIME) >= 366
ORDER BY
    ECO1_ORDER_DATETIME DESC,
    CODE DESC
;