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

with t1 as (
select user_id,date second_buy_date
from
    (select user_id,date,dense_rank() over(partition by user_id order by date) rk
    from order_info
    where  user_id in 
            (select user_id
            from order_info
            where datediff(date,"2025-10-15")>0
            and status ="completed"
            and product_name in("C++","Java","Python")
            group by user_id
            having count(*)>=2)
    and datediff(date,"2025-10-15")>0
    and status ="completed"
    and product_name in("C++","Java","Python")) t
where rk=2
),
t2 as(
select user_id,min(date) first_buy_date, count(*) cnt
from order_info
where  user_id in 
        (select user_id
        from order_info
        where datediff(date,"2025-10-15")>0
        and status ="completed"
        and product_name in("C++","Java","Python")
        group by user_id
        having count(*)>=2)
  and datediff(date,"2025-10-15")>0
  and status ="completed"
  and product_name in("C++","Java","Python")
group by user_id
order by user_id
)
select t2.user_id,first_buy_date,t1.second_buy_date,cnt
from t2 
left join t1
on t1.user_id=t2.user_id