MySQL 牛客的课程订单分析

79、81、83重点看

77. 请你写出一个sql语句查询在2025-10-15以后状态为购买成功的C++课程或者Java课程或者Python的订单，并且按照order_info的id升序排序:

select * from order_info
where datediff(date,"2025-10-15")>0
    and status = "completed"
    and (product_name = 'C++' or product_name = 'Java' or product_name = 'Python')
    -- product_name in ('C++','Java','Python')
order by id

78. 请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id，并且按照user_id升序排序

select user_id from order_info
where datediff(date,'2025-10-15')>0 and status = 'completed' 
	and product_name in ('C++','Java','Python')
group by user_id
having count(product_name) >= 2
order by user_id

79.请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单信息，并且按照order_info的id升序排序，

select oi.*
from order_info oi
inner join
(
    select id,count(id) over(partition by user_id) cnt
    from order_info 
    where datediff(date,'2025-10-15')>0 and status = 'completed' 
    and product_name in ('C++','Java','Python')
)a
on oi.id = a.id 
where a.cnt >= 2
order by id

select oi.* from order_info oi inner join
(
    select user_id from order_info
    where datediff(date,'2025-10-15')>0 and status = 'completed' 
        and product_name in ('C++','Java','Python')
    group by user_id
    having count(product_name) >= 2
)a 
on oi.user_id = a.user_id
where datediff(date,'2025-10-15')>0 and status = 'completed' 
        and product_name in ('C++','Java','Python')
order by oi.id

80. 请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序，

select user_id,min(date) first_buy_date,count(product_name) cnt 
from order_info
where datediff(date,'2025-10-15')>0 and status = 'completed' 
and product_name in ('C++','Java','Python')
group by user_id
having count(product_name) >= 2
order by user_id

81. 请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序

select a.user_id,min(date) first_buy_date,max(date) second_buy_date,a.cnt
from
(
    select user_id,date,
    dense_rank() over(partition by user_id order by date) rk,
    count(product_name) over(partition by user_id) cnt 
    from order_info
    where datediff(date,'2025-10-15')>0 and status = 'completed' 
    and product_name in ('C++','Java','Python')
)a
where a.rk <= 2
group by user_id
having cnt >= 2
order by user_id


WITH order_new AS(
    SELECT *
    FROM order_info o
    WHERE DATEDIFF(date,'2025-10-15') > 0
    AND product_name IN ('C++', 'Java', 'Python')
    AND status = 'completed'
)
SELECT 
    user_id, 
    MIN(date) AS first_buy_date,
    (SELECT date FROM order_new
     WHERE user_id = o.user_id
     ORDER BY date LIMIT 1, 1) AS second_buy_date,
    COUNT(id) AS cnt
FROM order_new o
GROUP BY user_id
HAVING COUNT(id) >= 2
ORDER BY user_id;

82. 请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单id，是否拼团以及客户端名字信息，最后一列如果是非拼团订单，则显示对应客户端名字，如果是拼团订单，则显示NULL，并且按照order_info的id升序排序

select oi.id,oi.is_group_buy,
case when oi.is_group_buy = 'Yes' then NULL else client.name end client_name
from order_info oi
inner join
(
    select id,count(id) over(partition by user_id) cnt
    from order_info 
    where datediff(date,'2025-10-15')>0 and status = 'completed' 
    and product_name in ('C++','Java','Python')
)a
on oi.id = a.id 
left join client on oi.client_id = client.id
where a.cnt >= 2
order by id

83. 请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，最后结果按照第一列(source)升序排序

select 
case when oi.is_group_buy = 'Yes' then 'GroupBuy' else client.name end source,
count(oi.id)
from order_info oi
inner join
(
    select id,count(id) over(partition by user_id) cnt
    from order_info 
    where datediff(date,'2025-10-15')>0 and status = 'completed' 
    and product_name in ('C++','Java','Python')
)a
on oi.id = a.id 
left join client on oi.client_id = client.id
where a.cnt >= 2
group by source
order by source