WITH MAX_DATE AS (
    SELECT
         MEMBER_CODE
        ,MAX(ORDER_DATETIME) AS ORDER_DATETIME
    FROM EC_ORDERS
    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
WHERE DATE(EC_ORDERS.ORDER_DATETIME) BETWEEN DATE('2023-07-01')
                                         AND DATE('2023-07-31')
ORDER BY EC_ORDERS.ORDER_DATETIME DESC, EC_ORDERS.MEMBER_CODE DESC
;