题解 | #牛客的课程订单分析(五)#
牛客的课程订单分析(五)
http://www.nowcoder.com/practice/348afda488554ceb922efd2f3effc427
用case when新生成两列
后面随便用个聚合函数聚合起来
select user_id, min(first_buy_date), min(second_buy_date), cnt from (select user_id, (case when d_rank=1 then date end) first_buy_date, (case when d_rank=2 then date end) second_buy_date, cnt from (select user_id, date, dense_rank() over(partition by user_id order by date ASC) as d_rank, count(id) over(partition by user_id) as cnt from order_info where date>'2025-10-15' and product_name in ('C++','Python', 'Java') and status='completed') tmp ) tmp2 group by user_id,cnt having min(second_buy_date) is not null order by user_id;