WITH DEF AS(
WITH ABC AS(
SELECT
    EC.MEMBER_CODE AS "CODE"
    ,ORDER_DATETIME AS "ORDER_DATE"
    ,OPTOUT_TYPE AS "OPTOUT"
    ,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_DATE"
)
SELECT
    "CODE"
    ,MAX(CASE WHEN CODE_CNT = 1 THEN "ORDER_DATE" ELSE NULL END) AS "1st"
    ,MAX(CASE WHEN CODE_CNT = 2 THEN "ORDER_DATE" ELSE NULL END) AS "2nd"
    ,MAX(CASE WHEN CODE_CNT = 3 THEN "ORDER_DATE" ELSE NULL END) AS "3rd"
    ,"OPTOUT"
FROM
    ABC
GROUP BY
    "CODE"
)
SELECT
    "2nd"
    ,"CODE"
    ,"OPTOUT"
FROM
    DEF
WHERE
    (DATE("2nd") between "2023-07-01" and "2023-07-31") and (DATE("1st") < DATE("2nd", '-1 years')) and ("3rd" IS null)
;