WITH t AS(
SELECT
    MEMBER_CODE AS CODE
    ,MIN(SUBSTR(ORDER_DATETIME,1,10)) AS ORDER_DATETIME
    ,ORDER_DATETIME AS ORDER_DATE
FROM
    EC_ORDERS
WHERE
    SUBSTR(ORDER_DATETIME,1,7) = '2023-07'
GROUP BY
    MEMBER_CODE
),t1 AS(
SELECT
    t.CODE
    ,t.ORDER_DATETIME
    ,t.ORDER_DATE
    ,MAX(SUBSTR(t1.ORDER_DATETIME,1,10)) AS di
FROM
    EC_ORDERS as t1
LEFT JOIN t
    ON t.code = t1.MEMBER_CODE
WHERE
    julianday(t.ORDER_DATETIME) > julianday(SUBSTR(t1.ORDER_DATETIME,1,10))
    AND
    t.ORDER_DATETIME IS NOT NULL
GROUP BY
    t1.MEMBER_CODE
)
SELECT
    t1.ORDER_DATE
    ,t1.CODE
    ,CASE
        WHEN m.OPTOUT_TYPE = 0 THEN '可'
        WHEN m.OPTOUT_TYPE = 1 THEN '不可'
        ELSE '不可'
    END AS OPTOUT
FROM t1
INNER JOIN
    MEMBER_MST AS m
ON
    t1.CODE = m.MEMBER_CODE
WHERE
    julianday(t1.ORDER_DATETIME) - julianday(t1.di) >= 365
ORDER BY
    t1.ORDER_DATE DESC
    ,t1.CODE DESC