题解 | #牛客的课程订单分析(七)#
牛客的课程订单分析(七)
http://www.nowcoder.com/practice/d6f4a37f966145da8900ba9edcc4c068
先用窗口函数将购买成功次数两次以上的用户先求出来，
select *
from
(select *,count(id) over(partition by user_id) as n
from order_info
where date>'2025-10-15'
and status='completed'
and product_name in ("C++","Java","Python")) a
left join client
on a.client_id=client.id
where a.n>=2
然后将其和客户端的表，进行做链接，由于拼团的是找不到客户端的，所以匹配过去的客户端名称为null值，在用case when 写个条件即可，
select
case when c.name is null then 'GroupBuy' else c.name end as source,
count(c.id)
from
(select *
from
(select *,count(id) over(partition by user_id) as n
from order_info
where date>'2025-10-15'
and status='completed'
and product_name in ("C++","Java","Python")) a
left join client
on a.client_id=client.id
where a.n>=2) c
group by source
order by source;
小天才公司福利 1381人发布