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

with t1 as (
    select user_id, count(*) as cnt from (select * from order_info where
    (product_name = 'C++' OR product_name = 'Java' or product_name = 'Python') and date > '2025-10-15' and status = 'completed') t
    group by user_id
    having count(*) >= 2
),
t2 as (
    select user_id, min(date) as first_buy_date
    from order_info
    where (product_name = 'C++' OR product_name = 'Java' or product_name = 'Python') and date > '2025-10-15' and status = 'completed'
    group by user_id
),
t3 as (
    select user_id, date, row_number() over(partition by user_id order by date asc) as r
    from order_info
    where (product_name = 'C++' OR product_name = 'Java' or product_name = 'Python') and date > '2025-10-15' and status = 'completed'
),
t4 as(
    select user_id, date as second_buy_date from t3 where r = 2
)

select t1.user_id, first_buy_date, second_buy_date, cnt from t1 inner join t2 using(user_id) inner join t4 using(user_id) order by user_id

多搞一个子查询视图，用row_number()得到第二次的，row_number() over()在这道题记得要加partition by user_id，因为是每个用户的第二次