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

/*
SELECT user_id
FROM order_info
WHERE date > '2025-10-15'
and product_name in ('Java', 'C++', 'Python')
and status = 'completed'
GROUP BY user_id
HAVING COUNT(*) >= 2
*/
SELECT (case when info.client_id = 0
        then 'GroupBuy'
        WHEN info.client_id = 1 then 'PC'
        WHEN info.client_id = 2 then 'Android'
        WHEN info.client_id = 3 then 'IOS'
        else 'H5' end) as source, count(*) as cnt 
FROM order_info as info
WHERE user_id in (SELECT user_id
                  FROM order_info
                  WHERE date > '2025-10-15'
                  and product_name in ('Java', 'C++', 'Python')
                  and status = 'completed'
                  GROUP BY user_id
                  HAVING COUNT(*) >= 2)
and date > '2025-10-15'
and product_name in ('Java', 'C++', 'Python')
and status = 'completed'
group by info.client_id
order BY source;