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

with order_info_2 AS (
    SELECT * FROM order_info
WHERE user_id IN (
    SELECT a.user_id
    FROM (
        SELECT *
        FROM order_info
        WHERE date > '2025-10-15' AND status = 'completed' AND product_name IN ('C++', 'Python','Java')
    ) a
    GROUP BY a.user_id
    HAVING COUNT(*) >= 2
)
AND date > '2025-10-15' AND status = 'completed' AND product_name IN ('C++', 'Python','Java')
),
order_info_3 as (
    select user_id ,min(date) as first_buy_date ,count(user_id) as cnt from order_info_2 
    group by user_id 
),
order_info_4 as (
    select a.user_id, a.date as second_buy_date from (
    SELECT *,
	DENSE_RANK() OVER (PARTITION BY user_id ORDER BY date) AS date_rank
    FROM order_info_2
) a where a.date_rank = 2
)
select o3.user_id,o3.first_buy_date,o4.second_buy_date ,o3.cnt 
from order_info_3 o3,order_info_4 o4 where o3.user_id=o4.user_id
order by o3.user_id