题解 | #牛客的课程订单分析(七)#
牛客的课程订单分析(七)
http://www.nowcoder.com/practice/d6f4a37f966145da8900ba9edcc4c068
采取union
将groupbuy和其他渠道的分开统计
采用窗口函数
select "GroupBuy" as source, sum(case when is_group_buy="Yes" then 1 else 0 end) as cnt from (select user_id,product_name,is_group_buy,client_id,count(product_name)over(partition by user_id) as rk from order_info where status="completed" and date>"2025-10-15" and product_name in ("Python","Java","C++"))t1 where rk>=2
union
select c.name as source,count(*) as cnt from (select user_id,product_name,is_group_buy,client_id,count(product_name)over(partition by user_id) as rk from order_info where status="completed" and date>"2025-10-15" and product_name in ("Python","Java","C++"))t1 join client c on c.id=t1.client_id where t1.rk>=2 group by c.name;
