WITH DEF AS(
WITH ABC AS(
SELECT
    EC.MEMBER_CODE AS "CODE"
    ,ORDER_DATETIME AS "ORDER_DATE2"
    ,OPTOUT_TYPE AS "OPTOUT2"
    ,ROW_NUMBER() OVER (PARTITION BY EC.MEMBER_CODE ORDER BY ORDER_DATETIME) AS "CODE_CNT"
FROM
    EC_ORDERS AS "EC"
INNER JOIN
    MEMBER_MST AS "ME" ON (EC.MEMBER_CODE = ME.MEMBER_CODE)
ORDER BY "CODE", "ORDER_DATE2"
)
SELECT
    "CODE"
    ,MAX(CASE WHEN CODE_CNT = 1 THEN "ORDER_DATE2" ELSE NULL END) AS "1st"
    ,MAX(CASE WHEN CODE_CNT = 2 THEN "ORDER_DATE2" ELSE NULL END) AS "2nd"
    ,MAX(CASE WHEN CODE_CNT = 3 THEN "ORDER_DATE2" ELSE NULL END) AS "3rd"
    ,MAX(CASE "OPTOUT2" WHEN 0 THEN "可" WHEN 1 THEN "不可" ELSE "不明" END) AS "OPTOUT"
FROM
    ABC
GROUP BY
    "CODE"
)
SELECT
    "2nd" AS "ORDER DATE"
    ,"CODE"
    ,"OPTOUT"
FROM
    DEF
WHERE
    (DATE("2nd") between "2023-07-01" and "2023-07-31") and (DATE("1st") < DATE("2nd", '-1 years'))
ORDER BY 
    "2nd" DESC
    ,"CODE" DESC
;