WITH RankedLogs AS (
    SELECT
    	MEMBER_CODE
    	,ORDER_DATETIME
		,ROW_NUMBER() OVER (PARTITION BY MEMBER_CODE ORDER BY ORDER_NO) AS rn
	FROM EC_ORDERS
	WHERE ORDER_DATETIME < '2023-08-01 00:00:00'
)
,firstordertable AS (
	SELECT
		MEMBER_CODE 
		,min(ORDER_DATETIME) AS firstorder
	FROM RankedLogs
	WHERE ORDER_DATETIME >= '2023-07-01 00:00:00'
	GROUP BY 1
)
,prevdata AS (
	SELECT
		t0.MEMBER_CODE
		,t0.firstorder
		,t1.rn
		,t2.ORDER_DATETIME AS prevorder
	FROM firstordertable AS t0
	INNER JOIN RankedLogs AS t1
		ON t0.MEMBER_CODE = t1.MEMBER_CODE
			AND t0.firstorder = t1.ORDER_DATETIME
	LEFT OUTER JOIN RankedLogs AS t2
		ON t0.MEMBER_CODE = t2.MEMBER_CODE
			AND t1.rn = t2.rn + 1
)
,oneyear AS (
	SELECT
		MEMBER_CODE
		,firstorder
	FROM prevdata
	WHERE julianday(date(firstorder)) - julianday(date(prevorder)) >= 366
)
SELECT
	t0.firstorder AS ORDER_DATE
	,t0.MEMBER_CODE AS CODE
	,CASE t1.OPTOUT_TYPE
		WHEN '0' THEN '可'
		WHEN '1' THEN '不可'
		ELSE '不明' END AS OPTOUT
FROM oneyear AS t0
INNER JOIN MEMBER_MST AS t1
	ON t0.MEMBER_CODE = t1.MEMBER_CODE
ORDER BY 1 DESC,2 DESC