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

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, min(date) as first_buy_date
# from order_info
# where date >= '2025-10-15'
# and status='completed'
# and product_name in ('C++', 'Java', 'Python')
# group by user_id

select tmp1.user_id, tmp2.first_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, min(date) as first_buy_date
       from order_info
       where date >= '2025-10-15'
       and status='completed'
       and product_name in ('C++', 'Java', 'Python')
       group by user_id) tmp2
where tmp1.user_id=tmp2.user_id