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

/*
#第二次购买日期
select t.user_id as user_id, 1, t.date as second_buy_date, max(t.r) as cnt
from(SELECT *, rank()over(PARTITION by user_id order by date) as r
     FROM order_info as info
     WHERE date > '2025-10-15' and product_name in ('C++', 'Java', 'Python')
     and status = 'completed') as t
where t.r = 2
GROUP by t.user_id;

#是第一次购买日期
select user_id, min(date) as first_buy_date, COUNT(*) as cnt
from order_info as info
WHERE date > '2025-10-15' and product_name in ('C++', 'Java', 'Python')
and status = 'completed'
group by user_id
having count(*) >= 2
ORDER BY user_id;
*/
SELECT t1.user_id, t1.first_buy_date as first_buy_date, t2.second_buy_date as second_buy_date, t1.cnt as cnt
FROM (select t.user_id as user_id, 1, t.date as second_buy_date, max(t.r) as cnt
from(SELECT *, rank()over(PARTITION by user_id order by date) as r
     FROM order_info as info
     WHERE date > '2025-10-15' and product_name in ('C++', 'Java', 'Python')
     and status = 'completed') as t
where t.r = 2
GROUP by t.user_id)as t2
join (select user_id, min(date) as first_buy_date, COUNT(*) as cnt
from order_info as info
WHERE date > '2025-10-15' and product_name in ('C++', 'Java', 'Python')
and status = 'completed'
group by user_id
having count(*) >= 2) as t1
on t1.user_id = t2.user_id
order by user_id;