WITH MEMBER_ORDERED_IN_202307 AS
(
  SELECT
    MEMBER_CODE,
    strftime('%Y-%m-%d', date(MIN(ORDER_DATETIME))) AS FIRST_ORDER_IN_202307,
    ORDER_DATETIME
  FROM
    EC_ORDERS
  WHERE
    ORDER_DATETIME BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59'
  GROUP BY
    MEMBER_CODE
)
SELECT
  MO_202307.ORDER_DATETIME,
  MO_202307.MEMBER_CODE,
  CASE MM.OPTOUT_TYPE
    WHEN 0 THEN '可'
    WHEN 1 THEN '不可'
    ELSE '不明'
  END OPTOUT
FROM
  (
    MEMBER_ORDERED_IN_202307 AS MO_202307
    LEFT OUTER JOIN
      EC_ORDERS AS EO
    ON
      MO_202307.MEMBER_CODE = EO.MEMBER_CODE
  )
  INNER JOIN
    MEMBER_MST AS MM
  ON
    MO_202307.MEMBER_CODE = MM.MEMBER_CODE
WHERE
  date(EO.ORDER_DATETIME) < date(MO_202307.FIRST_ORDER_IN_202307, '-1 year')
ORDER BY
  MO_202307.ORDER_DATETIME DESC,
  MO_202307.MEMBER_CODE DESC