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

-- 创建临时表 eligible_users，存储满足条件的用户 id
with eligible_users as (
    select distinct user_id
    from order_info
    where date > '2025-10-15'
          and status = 'completed'
          and product_name in ('c++', 'java', 'python')
    group by user_id
    having count(user_id) >= 2
)
-- 主查询，从 order_info 表中选择满足条件的用户的相关信息
select user_id, first_buy_date, second_buy_date, cnt
from (
         -- 子查询，计算每个用户的相关信息
         select user_id,
                date as first_buy_date,
                lead(date, 1, 0) over (partition by user_id order by date) as second_buy_date,
                count(*) over (partition by user_id) as cnt,
                row_number() over (partition by user_id order by date) r
         from order_info
         where user_id in (select user_id from eligible_users)
           and date > '2025-10-15'
           and status = 'completed'
           and product_name in ('c++', 'java', 'python')
     ) subquery
-- 筛选出第一个购买记录
where r = 1
-- 按照用户 id 升序排序结果
order by user_id;