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

select
    user_id,
    first_buy_date,
    second_buy_date,
    count(1)
from
(select
    o.user_id user_id,
    first_value(o.date) over(partition by o.user_id order by o.date) as first_buy_date,
    NTH_value(o.date,2) over(partition by o.user_id order by o.date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as second_buy_date
from
    order_info o
where 
    o.status = "completed"
    and o.date > "2025-10-15"
    and (o.product_name = "C++"
    or o.product_name = "Java"
    or o.product_name = "Python")
    and exists(
        select
            oi.user_id
        from
            order_info oi
        where
            oi.status = "completed"
        and oi.date > "2025-10-15"
        and (oi.product_name = "C++"
        or oi.product_name = "Java"
        or oi.product_name = "Python")
        and oi.user_id = o.user_id
        group by oi.user_id
        having count(oi.user_id) >= 2
    )) t
group by user_id, first_buy_date, second_buy_date
order by user_id