가입은 늘었지만, 우리가 찾던 고객은 아니었습니다

USE bonbon;

SELECT 
	DATE_ADD(NOW(), INTERVAL 9 HOUR) AS query_time, U.phone_no AS phone_number, U.email_addr AS email, CASE WHEN U.with_drawed_at IS NOT NULL THEN TRUE ELSE FALSE END AS Is_withdrawed,
    U.id AS ID,
    U.created_at AS ID_created_at,
    U.name AS Parent_name,
    S.name AS Student_name,
    S.birth_date AS Student_birthdate,
    CASE WHEN U.marketing_argreed_at IS NOT NULL THEN TRUE ELSE FALSE END AS Is_marketing_agreed,
    U.last_logined_at AS Last_logined_at,
    CASE WHEN EXISTS (
        SELECT 1
        FROM bonbon.Coupon C
        WHERE C.user_id = U.id
        AND C.type = 'MBTI_25'
    ) THEN TRUE ELSE FALSE END AS Is_MBTI25,
    CASE WHEN EXISTS (
        SELECT 1
        FROM bonbon.Coupon C
        WHERE C.user_id = U.id
        AND C.type = 'MBTI_25'
        AND C.order_id IS NOT NULL
    ) THEN TRUE ELSE FALSE END AS Is_MBTI25_used,
    O.package_name AS Latest_payment_product,
    O.price AS Latest_payment_price,
    O.created_at AS Latest_payment_time
FROM 
    bonbon.User U
JOIN 
    bonbon.Student S ON S.user_id = U.id
LEFT JOIN 
    bonbon.Order O ON O.student_id = S.id
LEFT JOIN 
    (
        SELECT 
            student_id,
            MAX(created_at) AS latest_order
        FROM 
            bonbon.Order
        GROUP BY 
            student_id
    ) LO ON O.student_id = LO.student_id AND O.created_at = LO.latest_order

ORDER BY 
    U.id;