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

WITH qualified AS(
    SELECT user_id, SUM(IF(status='completed', 1, 0)) AS cnt
    FROM order_info
    WHERE (product_name = 'C++'
    OR product_name = 'Java'
    OR product_name = 'Python')
    AND date > '2025-10-15'
    GROUP BY user_id, status
    HAVING cnt >= 2
),
buy_date AS(
    SELECT user_id, o.date, DENSE_RANK() OVER(
        PARTITION BY o.user_id
        ORDER BY o.date
    ) AS date_rank
    FROM order_info AS o
    WHERE status = 'completed'
    AND (product_name = 'C++'
    OR product_name = 'Java'
    OR product_name = 'Python')
    AND date > '2025-10-15'
),
buy_first_date AS(
    SELECT b.user_id, b.date AS first_buy_date
    FROM buy_date AS b
    WHERE date_rank = 1
    AND date > '2025-10-15'
),

buy_second_date AS(
    SELECT b.user_id, b.date AS second_buy_date
    FROM buy_date AS b
    WHERE date_rank = 2
    AND date > '2025-10-15'
)

SELECT q.user_id, bf.first_buy_date, bs.second_buy_date, q.cnt
FROM qualified AS q
INNER JOIN buy_first_date AS bf
ON q.user_id = bf.user_id
INNER JOIN buy_second_date AS bs
ON q.user_id = bs.user_id
ORDER BY q.user_id ASC;