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

这道题被我写的异常麻烦
先找到符合条件的用户

SELECT user_id
    FROM order_info
    WHERE product_name in('C++','JAVA','Python')
    and status ='completed'
    and date>'2025-10-15'
    GROUP BY user_id
    HAVING count(status)>=2
    order by user_id

生成两个带日期RANK的临时表 t1,t2

(SELECT user_id,date,RANK()OVER(partition by user_id order by date) rk
FROM order_info
WHERE user_id IN (SELECT user_id
    FROM order_info
    WHERE product_name in('C++','JAVA','Python')
    and status ='completed'
    and date>'2025-10-15'
    GROUP BY user_id
    HAVING count(status)>=2
    order by user_id)
AND product_name in('C++','JAVA','Python')
    and status ='completed'
    and date>'2025-10-15') t1
JOIN
(SELECT user_id,date,RANK()OVER(partition by user_id order by date) rk
FROM order_info
WHERE user_id IN (SELECT user_id
    FROM order_info
    WHERE product_name in('C++','JAVA','Python')
    and status ='completed'
    and date>'2025-10-15'
    GROUP BY user_id
    HAVING count(status)>=2
    order by user_id)
AND product_name in('C++','JAVA','Python')
    and status ='completed'
    and date>'2025-10-15') t2
    ON t1.user_id=t2.user_id

小用一下笛卡尔积，t1.rk=1 AND t2.rk=2，生成新的临时表，此时只差最后一列，再做一个包含user_id和cnt的临时表，做内连接

SELECT t11.user_id,t11.fd,t11.sd,t12.cnt
FROM (SELECT t1.user_id,t1.date fd,t2.date sd
FROM (SELECT user_id,date,RANK()OVER(partition by user_id order by date) rk
FROM order_info
WHERE user_id IN (SELECT user_id
    FROM order_info
    WHERE product_name in('C++','JAVA','Python')
    and status ='completed'
    and date>'2025-10-15'
    GROUP BY user_id
    HAVING count(status)>=2
    order by user_id)
AND product_name in('C++','JAVA','Python')
    and status ='completed'
    and date>'2025-10-15') t1
JOIN
(SELECT user_id,date,RANK()OVER(partition by user_id order by date) rk
FROM order_info
WHERE user_id IN (SELECT user_id
    FROM order_info
    WHERE product_name in('C++','JAVA','Python')
    and status ='completed'
    and date>'2025-10-15'
    GROUP BY user_id
    HAVING count(status)>=2
    order by user_id)
AND product_name in('C++','JAVA','Python')
    and status ='completed'
    and date>'2025-10-15') t2
    ON t1.user_id=t2.user_id
WHERE t1.rk=1 AND t2.rk=2) t11
JOIN (SELECT user_id,count(product_name) cnt
FROM order_info
WHERE product_name in('C++','JAVA','Python')
and status ='completed'
and date>'2025-10-15'
GROUP BY user_id
HAVING count(status)>=2
order by user_id) t12
ON t11.user_id=t12.user_id