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

-- 第一步，拥有一个满足条件且包含第一次购买时间的sql
select user_id, min(date) first_buy_date, count(id) cnt
from order_info
where (product_name = 'Java' or product_name = 'C++' or product_name = 'Python')
  and date > '2025-10-15'
  and status = 'completed'
group by user_id
having count(id) > 1;
-- 第二步，给所有订单按照用户分组排序
select user_id,
       date,
       dense_rank() over (partition by user_id order by date asc) ran
from order_info
where (product_name = 'Java' or product_name = 'C++' or product_name = 'Python')
  and date > '2025-10-15'
  and status = 'completed';
-- 第三步，为他加个子查询，加上第二次购买的时间
select o.user_id,min(o.date) first_buy_date,(

    select date
    from (select user_id,
                 date,
                 dense_rank() over (partition by user_id order by date asc) ran
          from order_info
          where (product_name = 'Java' or product_name = 'C++' or product_name = 'Python')
            and date > '2025-10-15'
            and status = 'completed') tmp
            where tmp.user_id = o.user_id
    and tmp.ran=2

    ),count(id) cnt from order_info o where
    (o.product_name = 'Java' or o.product_name = 'C++' or o.product_name = 'Python')
                                                                        and o.date > '2025-10-15'
                                                                        and o.status='completed'
group by o.user_id
having count(o.id)>1
order by user_id