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

# 先找到满足要求的用户id
with t1 as (
    select user_id, count(id) 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 cnt >= 2
)# 找到没开始的一次购买日期
select t1.user_id, a.first_buy_date, t1.cnt
from t1
left join (
    select user_id, min(date) as first_buy_date
    from order_info
    where user_id in (select user_id from t1)
    and date >= '2025-10-15'
    and status = 'completed'
    and product_name in ("C++", "Java", "Python")
    group by user_id
) as a
on t1.user_id = a.user_id
order by t1.user_id;