题解 | #考试分数(五)#

本题思路：

1. 找出2025-10-15后购买"C++", "Java", "Python"课程且完成订单的用户信息，通过窗口函数计算出，每个用户购买的记录流水

        select
          user_id,
          date,
          ROW_NUMBER() over (
            partition by user_id
            order by
              date asc
          ) as num
        from
          order_info
        where
          product_name in ("C++", "Java", "Python")
          AND date > '2025-10-15'
          AND status = 'completed'

2. 在1的基础上取出第一二次购买的课程记录流水

    select
      a.user_id as user_id,
      min(a.date) as first_buy_date,
      max(a.date) as second_buy_date,
      count(*) as num
    from
      (
        select
          user_id,
          date,
          ROW_NUMBER() over (
            partition by user_id
            order by
              date asc
          ) as num
        from
          order_info
        where
          product_name in ("C++", "Java", "Python")
          AND date > '2025-10-15'
          AND status = 'completed'
      ) a
    where
      a.num <= 2
    group by
      user_id

3. 聚合出每个用户的购买的记录数

    select
      user_id,
      count(*) as cnt
    from
      order_info
    where
      product_name in ("C++", "Java", "Python")
      AND date > '2025-10-15'
      AND status = 'completed'
    group by
      user_id

4. join2和3中的表，取出2中记录数大于等于2的，并按照user_id升序，完整代码

select
  a.user_id as user_id,
  a.first_buy_date as first_buy_date,
  a.second_buy_date as second_buy_date,
  b.cnt as cnt
from
  (
    select
      a.user_id as user_id,
      min(a.date) as first_buy_date,
      max(a.date) as second_buy_date,
      count(*) as num
    from
      (
        select
          user_id,
          date,
          ROW_NUMBER() over (
            partition by user_id
            order by
              date asc
          ) as num
        from
          order_info
        where
          product_name in ("C++", "Java", "Python")
          AND date > '2025-10-15'
          AND status = 'completed'
      ) a
    where
      a.num <= 2
    group by
      user_id
  ) a
  left join (
    select
      user_id,
      count(*) as cnt
    from
      order_info
    where
      product_name in ("C++", "Java", "Python")
      AND date > '2025-10-15'
      AND status = 'completed'
    group by
      user_id
  ) b on a.user_id = b.user_id
  and a.num >= 2
where
  b.cnt is not null
order by
  user_id;