WITH Order7 AS (
    SELECT
         MEMBER_CODE
        ,MIN(ORDER_DATETIME) AS MIN_ORDER_DATE
    FROM 
        EC_ORDERS
    WHERE
        strftime('%Y-%m-%d', Order_DateTime) BETWEEN '2023-07-01' AND '2023-07-31'
    GROUP BY    
        MEMBER_CODE
),
 LastOrders AS (
    SELECT
         MEMBER_CODE
        ,MAX(ORDER_DATETIME) AS LAST_ORDER_DATE
    FROM 
        EC_ORDERS
    WHERE
        strftime('%Y-%m-%d', Order_DateTime) < '2023-07-01' 
    GROUP BY    
        MEMBER_CODE
)
SELECT
     O7.MIN_Order_Date 'ORDER_DATE'
    ,M.Member_Code 'CODE'
    ,CASE M.OPTOUT_TYPE
        WHEN 0 THEN '可'
        WHEN 1 THEN '不可'
        ELSE '不明'
    END 'OPTOUT'
FROM
    Member_Mst M
INNER JOIN
    Order7 O7
ON  
    M.Member_Code = O7.Member_Code
INNER JOIN
    LastOrders LO
ON
    M.Member_Code = LO.Member_Code
where
    julianday(O7.MIN_ORDER_DATE) - julianday(LO.LAST_ORDER_DATE) >= 366
order by
     1 desc
    ,2 desc
;