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

# 先找到满足条件得记录
with t1 as (
    select user_id, date
    from order_info 
    where date > '2025-10-15'
    and status = 'completed'
    and product_name in ('Python', 'Java', 'C++')
),
t2 as (
    select user_id, min(date) as first_buy_date, count(date) as cnt
    from t1
    group by user_id
    having count(date) >= 2
),
t3 as (
    select user_id, min(date) as second_buy_date
    from t1
    where user_id in (select user_id from t2)
    and date > (
        select first_buy_date
        from t2
        where t2.user_id = t1.user_id
    )
    group by user_id
)
select t2.user_id, first_buy_date, t3.second_buy_date, cnt
from t2, t3
where t2.user_id = t3.user_id
order by user_id;