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

-- 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
),
t4 AS (
    SELECT
    	'GroupBuy' AS source,
    	count(is_group_buy) AS cnt
    FROM t3
    WHERE is_group_buy = 'Yes'
),
t5 AS (
	SELECT
    	client_name AS source,
    	COUNT(client_name) AS cnt
    FROM t3
    WHERE client_name IS NOT NULL
    GROUP BY client_name
),
t6 AS (
	SELECT *
    FROM t4
    UNION ALL
    SELECT *
    FROM t5
    ORDER BY source ASC
)
SELECT * FROM t6;
# 注意筛选掉为空值的课程name。