WITH MAX_DATE AS (
    SELECT
         MEMBER_CODE
        ,MAX(ORDER_DATETIME) AS ORDER_DATETIME
    FROM EC_ORDERS
    WHERE DATE(ORDER_DATETIME) < DATE('2023-07-01')
    GROUP BY MEMBER_CODE
),MIN_7_DATE AS (
    SELECT
         MEMBER_CODE
        ,MIN(DATETIME(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 DATETIME(EC_ORDERS.ORDER_DATETIME) = MIN_7_DATE.ORDER_DATETIME
ORDER BY EC_ORDERS.ORDER_DATETIME DESC, EC_ORDERS.MEMBER_CODE DESC
;