题解 | 牛客的课程订单分析(四)

SELECT
    A.user_id,A.first_buy_date,B.cnt 
FROM
    (
        SELECT
            user_id,
            min(order_info.date) first_buy_date
        FROM
            order_info
        WHERE
            user_id in (
                SELECT
                    user_id
                FROM
                    order_info
                WHERE
                    status = 'completed'
                    AND product_name in ('C++', 'Python', 'Java')
                    AND order_info.date > '2025-10-15'
                GROUP BY
                    user_id
                HAVING
                    COUNT(1) >= 2
            )
            AND status = 'completed'
            AND product_name in ('C++', 'Python', 'Java')
            AND order_info.date > '2025-10-15'
        GROUP BY
            user_id
    ) A
INNER JOIN 
(SELECT
    user_id,COUNT(1) cnt 
FROM
    order_info
WHERE
    status = 'completed'
    AND product_name in ('C++', 'Python', 'Java')
    AND order_info.date > '2025-10-15'
GROUP BY
    user_id
HAVING
    COUNT(1) >= 2) B 
ON A.user_id = B.user_id
ORDER BY 
A.user_id;