WITH MAX_DATE AS (
    SELECT
         MEMBER_CODE
        ,MAX(ORDER_DATETIME) AS ORDER_DATETIME
    FROM EC_ORDERS
    GROUP BY MEMBER_CODE
),
MIN_7_DATE AS (
    SELECT
         MEMBER_CODE
        ,MIN(ORDER_DATETIME) AS ORDER_DATETIME
    FROM EC_ORDERS
    WHERE DATE(ORDER_DATETIME) BETWEEN DATE('2023-07-01')
                                   AND DATE('2023-07-31')
    GROUP BY MEMBER_CODE
)
SELECT
     EC_ORDERS.ORDER_DATETIME AS ORDER_DATE
    ,EC_ORDERS.MEMBER_CODE    AS CODE
    ,CASE MEMBER_MST.OPTOUT_TYPE
         WHEN '0' THEN '可'
         WHEN '1' THEN '不可'
                  ELSE '不明'
     END                      AS OPTOUT
FROM EC_ORDERS
     INNER JOIN MAX_DATE
         ON EC_ORDERS.MEMBER_CODE          = MAX_DATE.MEMBER_CODE
        AND DATE(EC_ORDERS.ORDER_DATETIME) > DATE(MAX_DATE.ORDER_DATETIME, '-1 year')
     INNER JOIN MEMBER_MST
         ON EC_ORDERS.MEMBER_CODE = MEMBER_MST.MEMBER_CODE
     INNER JOIN MIN_7_DATE
         ON EC_ORDERS.MEMBER_CODE    = MIN_7_DATE.MEMBER_CODE
        AND EC_ORDERS.ORDER_DATETIME = MIN_7_DATE.ORDER_DATETIME
ORDER BY EC_ORDERS.ORDER_DATETIME DESC, EC_ORDERS.MEMBER_CODE DESC
;