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

WITH T AS 
(
    SELECT 
        OI.id
        ,OI.is_group_buy
        ,D.name client_name
    FROM (
        SELECT 
            id
            ,client_id
            ,is_group_buy
            ,COUNT(id) OVER (PARTITION BY user_id) AS CNT
        FROM order_info 
        WHERE DATEDIFF(date,'2025-10-15') > 0
            AND status = 'completed'
            AND product_name IN ('C++','Python','Java')
    )  OI 
    LEFT JOIN client D ON OI.client_id = D.id
    WHERE OI.CNT >= 2
)
SELECT 
     CASE WHEN T.client_name IS NOT NULL THEN T.client_name ELSE 'GroupBuy' END source
    ,COUNT(1) CNT
FROM T
GROUP BY T.client_name
ORDER BY source
;