WITH RankedLogs AS (
        SELECT
        	MEMBER_CODE
        	,ORDER_DATETIME
    		,ROW_NUMBER() OVER (PARTITION BY MEMBER_CODE ORDER BY ORDER_DATETIME) AS rn
    	FROM EC_ORDERS
    )
    ,firstordertable AS (
    	SELECT
    		MEMBER_CODE 
    		,min(ORDER_DATETIME) AS firstorder
    	FROM RankedLogs
    	WHERE ORDER_DATETIME BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59'
    	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(firstorder) - julianday(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