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

#创建临时表my
WITH my AS(
SELECT *,COUNT(*)OVER(PARTITION BY user_id) AS cnt
FROM order_info
WHERE status='completed'
AND product_name IN ("C++","Java","Python")
AND date>'2025-10-15')
#根据client_id选出待选信息
SELECT DISTINCT(CASE WHEN is_group_buy='Yes' THEN 'GroupBuy' ELSE c.name END) AS source,
COUNT(*)OVER(PARTITION BY client_id) AS cnt
FROM my LEFT JOIN client c ON my.client_id=c.id
WHERE my.cnt>1
ORDER BY source;