WITH DATA_2307 AS (
SELECT
	MIN(ORDER_DATETIME) ORDER_DATE
	,MEMBER_CODE
FROM 
	EC_ORDERS
WHERE date(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
GROUP BY MEMBER_CODE
)
,BEFORE_2306 AS (
SELECT
	MAX(ORDER_DATETIME) ORDER_DATE
	,MEMBER_CODE
FROM 
	EC_ORDERS
WHERE date(ORDER_DATETIME) < '2023-07-01'
GROUP BY MEMBER_CODE
)
SELECT B.ORDER_DATE 
	,B.MEMBER_CODE CODE
	,CASE WHEN OPTOUT_TYPE = 0 THEN '可'
		  WHEN OPTOUT_TYPE = 1 THEN '不可'
		  ELSE '不明'
	END OPTOUT
FROM
	BEFORE_2306 A
	,DATA_2307 B
	,MEMBER_MST C
WHERE A.MEMBER_CODE = B.MEMBER_CODE
AND date(datetime(B.ORDER_DATE, '-1 years')) > date(A.ORDER_DATE)
AND A.MEMBER_CODE = C.MEMBER_CODE
ORDER BY B.ORDER_DATE DESC,B.MEMBER_CODE DESC