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

# 比较复杂，用了好几个子查询
# select d.user_id, first_buy_date, second_buy_date, cnt
# from
#  (
#     select distinct b.user_id, first_buy_date, cnt
#     from
#         (
#             select  *,
#             min(date)over(partition by user_id) first_buy_date,
#             count(user_id)over(partition by user_id) cnt
#             from order_info
#             where 
#             date >= '2025-10-15'
#             and status = 'completed'
#             and product_name in ('C++','Java','Python')
#         ) b
#   ) d
# ,
# (
#     select distinct c.user_id, date second_buy_date
#     from
#         (
#         select user_id,date,
#         rank()over(partition by user_id order by date) rankdate
#         from order_info
#         where 
#         date >= '2025-10-15'
#         and status = 'completed'
#         and product_name in ('C++','Java','Python')
#         ) c
#     where rankdate = 2
# ) e

# where cnt >=2 and d.user_id = e.user_id
# order by d.user_id

select user_id,
    min(date) as first_buy_date,
    min(下一个日期) as second_buy_date,
    max(次数) as cnt
from (
    select *,
        row_number() over(partition by user_id order by date) as 次数,
        lead(date,1) over(partition by user_id order by date) as 下一个日期
    from order_info
    where date>'2025-10-15'
    and status='completed'
    and product_name in ('Python','Java','C++')
    order by user_id) as t
group by user_id
having count(*)>=2
order by user_id
#优雅解法