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

sql script

-- user buy more than 2
select user_id, count(product_name) 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 count(product_name) >= 2
order by user_id

-- user first day buy with condition
select user_id, row_number() over(partition by user_id order by date) as d_rank, date
from order_info
where date >= '2025-10-15'
and status='completed'
and product_name in ('C++', 'Java', 'Python')



select tmp1.user_id, tmp2.date as first_buy_date, tmp3.date as second_buy_date, tmp1.cnt
from (select user_id, count(product_name) 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 count(product_name) >= 2
      order by user_id) tmp1, 
      (select user_id, date from (select user_id, row_number() over(partition by user_id order by date) as d_rank, date
                                  from order_info
                                  where date >= '2025-10-15'
                                  and status='completed'
                                  and product_name in ('C++', 'Java', 'Python')) a where d_rank=1) tmp2,
      (select user_id, date from (select user_id, row_number() over(partition by user_id order by date) as d_rank, date
                                  from order_info
                                  where date >= '2025-10-15'
                                  and status='completed'
                                  and product_name in ('C++', 'Java', 'Python')) b where d_rank=2) tmp3
where tmp1.user_id=tmp2.user_id
and tmp1.user_id=tmp3.user_id