有时候做出来会觉得自己很牛 | #牛客的课程订单分析(七)#

WITH ranked AS (
    SELECT 
        *, 
        count(*) over (partition by user_id) AS cnt 
    FROM 
        order_info
    WHERE 
        date >= '2025-10-15' AND 
        status = 'completed' AND 
        (product_name = 'Python' OR product_name = 'C++' OR product_name = 'Java')
),

groupbuy AS (
    SELECT
        *,
        count(*) over (partition by user_id) AS cnt 
    FROM
        order_info
    WHERE 
        is_group_buy = 'YES'
)


SELECT 
    case 
        when r.client_id != 0 then c.name
        else 'GroupBuy'
    End AS source,
    count(client_id) AS cnt
FROM 
    ranked r
LEFT JOIN
    client c
ON 
    r.client_id = c.id
WHERE 
    r.cnt >= 2 
GROUP BY
    source, r.client_id
ORDER BY 
    source