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

SELECT CASE WHEN c.name IS NULL THEN 'GroupBuy' ELSE c.name END source,
    COUNT(*) cnt

FROM(
    SELECT id, user_id, product_name, status, client_id, date, is_group_buy,
    count(*) OVER(PARTITION BY user_id) cnt
    FROM order_info
    WHERE date >= '2025-10-15' AND status = 'completed' 
        AND product_name IN('Python','Java','C++')
) co
LEFT JOIN client c ON c.id = co.client_id
WHERE co.cnt >= 2
GROUP BY c.name
ORDER BY source