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

SELECT 
    COALESCE(name, 'GroupBuy') AS source
    ,COUNT(*) AS cnt 
FROM 
(
    SELECT 
        order_info.id AS id, 
        order_info.is_group_buy AS is_group_buy,
        client.name AS name,
        COUNT(*) OVER (PARTITION BY order_info.user_id) AS cnt,
        row_number() OVER(PARTITION BY order_info.user_id ORDER BY order_info.date DESC) AS rk
    FROM 
        order_info 
    LEFT OUTER JOIN 
        client 
    ON 
        order_info.client_id = client.id 
    WHERE 
        order_info.date > '2025-10-15'
    AND 
        order_info.status = 'completed'
    AND 
        product_name IN ( 'C++', 'Python', 'Java' )
) AS order_info_ext 
WHERE 
    order_info_ext.cnt >= 2
GROUP BY 
    COALESCE(name, 'GroupBuy')
ORDER BY 
    COALESCE(name, 'GroupBuy') ASC 
;