WITH JULY_ORDERS AS (
SELECT
*
FROM EC_ORDERS
WHERE
   ORDER_DATETIME BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59' 
), JULY_FIRST_ORDERS AS (
SELECT *
FROM JULY_ORDERS A
WHERE NOT EXISTS (
SELECT 1 FROM JULY_ORDERS B
WHERE
A.MEMBER_CODE = B.MEMBER_CODE
AND A.ORDER_DATETIME > B.ORDER_DATETIME
)
), PREVIOUS_ORDERS AS (
SELECT *
FROM EC_ORDERS A
WHERE EXISTS (
SELECT 1 FROM JULY_FIRST_ORDERS B
WHERE
A.MEMBER_CODE = B.MEMBER_CODE
AND A.ORDER_DATETIME < B.ORDER_DATETIME
)
), PREVIOUS_LAST_ORDERS AS (
SELECT *
FROM PREVIOUS_ORDERS A
WHERE NOT EXISTS (
SELECT 1 FROM PREVIOUS_ORDERS B
WHERE
A.MEMBER_CODE = B.MEMBER_CODE
AND A.ORDER_DATETIME < B.ORDER_DATETIME
)
), TARGET_ORDERS (
SELECT A.ORDER_DATETIME, A.MEMBER_CODE
FROM JULY_FIRST_ORDERS A
INNER JOIN PREVIOUS_LAST_ORDERS B
ON
A.MEMBER_CODE = B.MEMBER_CODE
AND date(A.ORDER_DATETIME), date('now', 'localtime', '-1 year')
)
SELECT * FROM PREVIOUS_LAST_ORDERS