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

【难点】在于怎么获取第二次购买的时间
【思路】1.首先用group by加where子查询找到满足条件的user_id,first_buy_date,cnt
2.用dense_rank加where子查询找到满足条件的user_id,second_buy_date
3.最后对上述两个表进行连接，再按照user_id进行排序即可。
【代码如下】
select t1.user_id, t1.first_buy_date, t2.second_buy_date, t1.cnt from
(select user_id, min(date) as first_buy_date, count() as cnt from
order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++','Python','Java')
group by user_id
having count() >= 2) as t1
left join
(select t.user_id, t.date as second_buy_date from
(select , dense_rank() over(partition by user_id order by date) as r from
order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++','Python','Java')) as t
where r = 2) as t2
on t1.user_id = t2.user_id
order by t1.user_id
【更简洁的代码】
select a.user_id,
min(date) as first_buy_date, max(date) as second_buy_date, a.cnt
#max(case when a.rank_no=1 then a.date else 0 end) as first_buy_date,
#max(case when a.rank_no=2 then a.date else 0 end) as second_buy_date,
#如果题目是取第一次和第三次购买时间则可以用#后面的语句形式进行修改即可使用
from
(select user_id, date,
dense_rank() over(partition by user_id order by date) as rank_no,
count() over(partition by user_id) as cnt
from order_info
where date >= '2025-10-16'
and status = 'completed'
and product_name in('C++','Java','Python')
) a
where a.rank_no<=2 and a.cnt>=2 #如果题目是取第一次和第三次购买时间,这里的查询条件也要修改
group by a.user_id
order by a.user_id