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

疑惑：
不加这个min，计算出来的结果停止在满足第一个CASE WHEN的条件

SELECT t1.user_id,
min(case when t1.rk=1 then t1.date end) as first_buy_date,
min(case when t1.rk=2 then t1.date end) as second_buy_date,cnt
FROM
(select user_id,product_name,date,
cast(ROW_NUMBER() over (partition by user_id order by date) as signed) rk
from order_info
WHERE date>'2025-10-15'
and status = 'completed'
and product_name in ('Python','Java','C++')) t1
LEFT JOIN
(
SELECT user_id,count(*) cnt
FROM order_info
WHERE status = 'completed'
and date>'2025-10-15'
and product_name in ('Python','Java','C++')
group by user_id) t2
on t1.user_id = t2.user_id
where cnt >=2
group by t1.user_id