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

SELECT

DISTINCT

KLL1.user_id,

KLL1.first_buy_date1 AS first_buy_date,

min(date) over(partition by user_id) AS second_buy_date,

KLL1.cnt

FROM(

SELECT

user_id,

date,

min(date) over(partition by user_id) as first_buy_date1,

count(user_id) over(partition by user_id) as cnt

FROM order_info

WHERE date > '2025-10-15'

AND status = 'completed'

AND user_id IN ( SELECT user_id FROM(

SELECT

user_id,

COUNT(product_name) over(partition by user_id) AS DC1

FROM order_info

WHERE date > DATE("2025-10-15")

AND status = 'completed'

AND product_name IN ('C++', 'Java', 'Python')

order by user_id

) AS GJJJ

WHERE GJJJ.DC1 >= 2

)

AND product_name IN ('C++','Java','Python')

group by user_id,date

order by user_id

) AS KLL1

WHERE

KLL1.date != (SELECT DISTINCT GJJJ.MINDATE FROM(

SELECT

user_id,

COUNT(product_name) over(partition by user_id) AS DC1,

min(date) over(partition by user_id) AS MINDATE

FROM order_info

WHERE date > DATE("2025-10-15")

AND status = 'completed'

AND product_name IN ('C++', 'Java', 'Python')

order by user_id

) AS GJJJ

WHERE GJJJ.DC1 >= 2)