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

-- 链接 LINK: https://www.nowcoder.com/practice/348afda488554ceb922efd2f3effc427?tpId=82&rp=1&ru=%2Fexam%2Foj&qru=%2Fexam%2Foj&sourceUrl=%2Fexam%2Foj%3Fdifficulty%3D5%26page%3D1%26pageSize%3D50%26search%3D%26tab%3DSQL%25E7%25AF%2587%26topicId%3D82&difficulty=5&judgeStatus=&tags=&title=&gioEnter=menu
-- 标题 TITLE: SQL275 牛客的课程订单分析(五)
-- 日期 DATE: 2023-02-25

/* 设计思路

**/

-- 题解 SQL
SELECT user_id,
        MIN(first_buy_date) AS first_buy_date,
        MIN(second_buy_date) AS second_buy_date,
        cnt
FROM (
    SELECT user_id,
        date,
        IF(ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) = 1, date, NULL) AS first_buy_date,
        IF(ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) = 2, date, NULL) AS second_buy_date,
        COUNT(id) OVER(PARTITION BY user_id) AS cnt
    FROM order_info
    WHERE status = 'completed' AND date > '2025-10-15' 
        AND product_name IN ('C++', 'Java', 'Python')
) T
WHERE cnt >= 2
GROUP BY user_id, cnt
ORDER BY user_id

;

-- 数据 DATA ===================================================
drop table if exists order_info;
CREATE TABLE order_info (
id int(4) NOT NULL,
user_id int(11) NOT NULL,
product_name varchar(256) NOT NULL,
status varchar(32) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
PRIMARY KEY (id));

INSERT INTO order_info VALUES
(1,557336,'C++','no_completed',1,'2025-10-10'),
(2,230173543,'Python','completed',2,'2025-10-12'),
(3,57,'JS','completed',3,'2025-10-23'),
(4,57,'C++','completed',3,'2025-10-23'),
(5,557336,'Java','completed',1,'2025-10-23'),
(6,57,'Java','completed',1,'2025-10-24'),
(7,557336,'C++','completed',1,'2025-10-25'),
(8,557336,'Python','completed',1,'2025-10-26');