题解 | #考试分数(五)#
牛客的课程订单分析(五)
http://www.nowcoder.com/practice/348afda488554ceb922efd2f3effc427
本题思路：
- 找出2025-10-15后购买"C++", "Java", "Python"课程且完成订单的用户信息，通过窗口函数计算出，每个用户购买的记录流水
select
user_id,
date,
ROW_NUMBER() over (
partition by user_id
order by
date asc
) as num
from
order_info
where
product_name in ("C++", "Java", "Python")
AND date > '2025-10-15'
AND status = 'completed'
- 在1的基础上取出第一二次购买的课程记录流水
select
a.user_id as user_id,
min(a.date) as first_buy_date,
max(a.date) as second_buy_date,
count(*) as num
from
(
select
user_id,
date,
ROW_NUMBER() over (
partition by user_id
order by
date asc
) as num
from
order_info
where
product_name in ("C++", "Java", "Python")
AND date > '2025-10-15'
AND status = 'completed'
) a
where
a.num <= 2
group by
user_id
- 聚合出每个用户的购买的记录数
select
user_id,
count(*) as cnt
from
order_info
where
product_name in ("C++", "Java", "Python")
AND date > '2025-10-15'
AND status = 'completed'
group by
user_id
- join2和3中的表，取出2中记录数大于等于2的，并按照user_id升序，完整代码
select
a.user_id as user_id,
a.first_buy_date as first_buy_date,
a.second_buy_date as second_buy_date,
b.cnt as cnt
from
(
select
a.user_id as user_id,
min(a.date) as first_buy_date,
max(a.date) as second_buy_date,
count(*) as num
from
(
select
user_id,
date,
ROW_NUMBER() over (
partition by user_id
order by
date asc
) as num
from
order_info
where
product_name in ("C++", "Java", "Python")
AND date > '2025-10-15'
AND status = 'completed'
) a
where
a.num <= 2
group by
user_id
) a
left join (
select
user_id,
count(*) as cnt
from
order_info
where
product_name in ("C++", "Java", "Python")
AND date > '2025-10-15'
AND status = 'completed'
group by
user_id
) b on a.user_id = b.user_id
and a.num >= 2
where
b.cnt is not null
order by
user_id;