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

with
tb1 as(
    select user_id, date, dense_rank() over(
        partition by user_id
        order by date
    ) as rk from order_info
    where date > '2025-10-15' and status = 'completed' and product_name in 
    ('C++', 'Java',  'Python')
),
tb2 as(
    select user_id, count(*) as cnt from order_info
    where date > '2025-10-15' and status = 'completed' and product_name in 
    ('C++', 'Java',  'Python')
    group by user_id
    having cnt >= 2
    order by user_id
),
tb3 as(
    select user_id, date as first_buy_date from tb1
    where rk = 1
),
tb4 as(
    select user_id, date as second_buy_date from tb1
    where rk = 2
)
select tb2.user_id, first_buy_date, second_buy_date, cnt from tb2
join tb3 on tb2.user_id = tb3.user_id
join tb4 on tb2.user_id = tb4.user_id