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

SELECT type_buy.source,COUNT(*) cnt
FROM(
    SELECT 
    CASE WHEN a.is_group_buy = 'YES'
    THEN 'GroupBuy'
    ELSE b.name
    END source
    FROM order_info a
    LEFT JOIN client b
    ON a.client_id = b.id
    WHERE a.date > '2025-10-15'
    AND  a.status = 'completed'
    AND a.product_name IN ('Python','C++','Java')
    AND a.user_id IN(
        SELECT  user_id
        FROM order_info
        WHERE date > '2025-10-15'
        AND  status = 'completed'
        AND product_name IN ('Python','C++','Java')
        GROUP BY user_id
        HAVING COUNT(*) > 1
)
 ) type_buy
GROUP BY type_buy.source
ORDER BY type_buy.source