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

这是个不开窗的解题思路，因此过程比较比较繁琐。

第一步：找出first_buy_date的信息以及临时表 first_buy (SELECT t1.user_id, MIN(t1.date) as first_buy_date, COUNT(t1.user_id) as cnt FROM (SELECT o.* FROM order_info o JOIN (SELECT user_id FROM order_info WHERE date > '2025-10-15' AND product_name in ('C++', 'Java', 'Python') AND status = 'completed' GROUP BY user_id HAVING COUNT(user_id)>=2 ORDER BY user_id) t WHERE o.user_id = t.user_id AND product_name in ('C++', 'Java', 'Python') AND status = 'completed' AND date > '2025-10-15') t1 GROUP BY t1.user_id ORDER BY t1.user_id) first_buy

    table: first buy
    +---------+----------------+-----+
    | user_id | first_buy_date | cnt |
    +---------+----------------+-----+
    |      57 | 2025-10-23     |   2 |
    |  557336 | 2025-10-23     |   3 |
    +---------+----------------+-----+
   

第二步：找出second_buy_date的信息以及临时表 second_buy (SELECT t1.user_id, MIN(t1.date) as second_buy_date FROM (SELECT o.* FROM order_info o JOIN (SELECT user_id FROM order_info WHERE date > '2025-10-15' AND product_name in ('C++', 'Java', 'Python') AND status = 'completed' GROUP BY user_id HAVING COUNT(user_id)>=2 ORDER BY user_id) t WHERE o.user_id = t.user_id AND product_name in ('C++', 'Java', 'Python') AND status = 'completed' AND date > '2025-10-15') t1 WHERE t1.date NOT IN (SELECT MIN(date) FROM order_info WHERE date > '2025-10-15' AND product_name in ('C++', 'Java', 'Python') GROUP BY user_id HAVING COUNT(user_id)>=2) GROUP BY t1.user_id) second_buy

table second_buy:
+---------+-----------------+
| user_id | second_buy_date |
+---------+-----------------+
|      57 | 2025-10-24      |
|  557336 | 2025-10-25      |
+---------+-----------------+

第三步：联结临时表first_buy和second_buy获得最终答案：
SELECT first_buy.user_id, first_buy.first_buy_date, second_buy.second_buy_date, first_buy.cnt
FROM
(SELECT t1.user_id, MIN(t1.date) as first_buy_date, COUNT(t1.user_id) as cnt
    FROM (SELECT o.*
        FROM order_info o
        JOIN (SELECT user_id
            FROM order_info
            WHERE date > '2025-10-15'
            AND product_name in ('C++', 'Java', 'Python')
            AND status = 'completed'
            GROUP BY user_id
            HAVING COUNT(user_id)>=2 ORDER BY user_id) t
    WHERE o.user_id = t.user_id
    AND product_name in ('C++', 'Java', 'Python')
    AND status = 'completed'
    AND date > '2025-10-15') t1
    GROUP BY t1.user_id
    ORDER BY t1.user_id) first_buy
JOIN
(SELECT t1.user_id, MIN(t1.date) as second_buy_date
FROM (SELECT o.*
    FROM order_info o
    JOIN (SELECT user_id
        FROM order_info
        WHERE date > '2025-10-15'
        AND product_name in ('C++', 'Java', 'Python')
        AND status = 'completed'
        GROUP BY user_id
        HAVING COUNT(user_id)>=2 ORDER BY user_id) t
    WHERE o.user_id = t.user_id
    AND product_name in ('C++', 'Java', 'Python')
    AND status = 'completed'
    AND date > '2025-10-15') t1
WHERE t1.date NOT IN (SELECT MIN(date)
                        FROM order_info
                        WHERE date > '2025-10-15'
                        AND product_name in ('C++', 'Java', 'Python')
                        GROUP BY user_id
                        HAVING COUNT(user_id)>=2)
                        GROUP BY t1.user_id) second_buy
ON first_buy.user_id = second_buy.user_id
ORDER BY first_buy.user_id;