with order_7 AS(
    SELECT ORDER_NO,MIN(ORDER_DATETIME) AS ORDER_DATE,MEMBER_CODE
    FROM EC_ORDERS
    WHERE strftime('%Y-%m-%d', date(ORDER_DATETIME)) BETWEEN '2023-07-01' AND '2023-07-31'
    GROUP BY MEMBER_CODE
)
, before_7 AS(
    SELECT ORDER_NO,MAX(ORDER_DATETIME) AS ORDER_DATE,MEMBER_CODE
    FROM EC_ORDERS
    WHERE strftime('%Y-%m-%d', date(ORDER_DATETIME)) < '2023-07-01'
    GROUP BY MEMBER_CODE
)
SELECT order_7.ORDER_DATE,order_7.MEMBER_CODE AS CODE,
   (case 
   when OPTOUT_TYPE = '0' then '可'
   when OPTOUT_TYPE = '1' then '不可'
   else '不明'
   end ) AS OPTOUT
FROM order_7 JOIN before_7 ON(order_7.MEMBER_CODE = before_7.MEMBER_CODE)
     join MEMBER_MST ON(order_7.MEMBER_CODE = MEMBER_MST.MEMBER_CODE)
WHERE date(order_7.ORDER_DATE,'-1 years') > date(before_7.ORDER_DATE)
ORDER BY order_7.ORDER_DATE DESC,order_7.MEMBER_CODE DESC