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

-- SQL281 牛客的课程订单分析(六)
WITH t1 AS (
	SELECT 
    	id,
        user_id,
    	client_id,
    	is_group_buy
    FROM order_info
    WHERE date > '2025-10-15' 
    AND status = 'completed'
    AND product_name IN ('C++','Java','Python')
),
t2 AS (
	SELECT 
    	user_id,
    	COUNT(user_id) AS num
    FROM t1
    GROUP BY user_id
    HAVING COUNT(user_id) >= 2
),
t3 AS (
	SELECT 
    	t1.id,
    	t1.is_group_buy,
    	IF(t1.is_group_buy = 'No',c.name,NULL) AS client_name
    FROM t1
    LEFT JOIN client c
    ON t1.client_id = c.id
    WHERE t1.user_id IN (SELECT user_id FROM t2)
    ORDER BY id ASC
)
SELECT * FROM t3;
# 记得筛选 user_id 大于等于 2 的数据！