首页 > 试题广场 >

牛客的课程订单分析(五)

[编程题]牛客的课程订单分析(五)

热度指数：89575 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

有很多同学在牛客购买课程来学习，购买会产生订单存到数据库里。

有一个订单信息表(order_info)，简况如下:

id	user_id	product_name	status	client_id	date
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

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。

第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单，状态为购买成功。

......

最后1行表示user_id为557336的用户在2025-10-26的时候使用了client_id为1的客户端下了Python课程的订单，状态为购买成功。

请你写出一个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升序排序，以上例子查询结果如下:

user_id	first_buy_date	second_buy_date	cnt
57	2025-10-23	2025-10-24	2
557336	2025-10-23	2025-10-25	3

解析:

id为4，6的订单满足以上条件，输出57，id为4的订单为第一次购买成功，输出first_buy_date为2025-10-23，id为6的订单为第二次购买，输出second_buy_date为2025-10-24，总共成功购买了2次;

id为5，7，8的订单满足以上条件，输出557336，id为5的订单为第一次购买成功，输出first_buy_date为2025-10-23，id为7的订单为第二次购买，输出second_buy_date为2025-10-25，总共成功购买了3次;

示例1

输入

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');

输出

57|2025-10-23|2025-10-24|2
557336|2025-10-23|2025-10-25|3

算法知识视频讲解

Mysql

海边的微风

with t1 as(
select user_id, product_name,date, row_number() over(partition by user_id order by date) as rk
from order_info
where date> '2025-10-15' and status = 'completed' and product_name IN ('C++','Python','Java')
),
t2 as (
select user_id, min(date) as first_buy_date, count(*) as cnt
from t1
group by user_id
having count(*)>=2
order by user_id )

select user_id, first_buy_date,second_buy_date,cnt
from t2
inner join (
select user_id, date as second_buy_date
from t1
where rk = 2 and user_id in (
    select user_id from t2
)
) a using(user_id)
order by user_id

发表于 2025-03-17 11:03:32 回复(0)

Mysql

内向的华夫饼

with t01 as(

select user_id as user_id,

product_name as product_name,

date as date,

DENSE_RANK() over (partition by user_id order by date) as rk

from order_info

where date > '2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')

t02 as (

select count(*) as cnt,

user_id as user_id

from t01

group by user_id

)

select distinct

a.user_id as user_id,

c.first_buy_date as first_buy_date,

d.second_buy_date as second_buy_date,

b.cnt as cnt

from (

select user_id as user_id

from t01

where rk = 1

) a

inner join (

select user_id,

cnt

from t02

where cnt >=2) b

on a.user_id = b.user_id

left join (

select user_id as user_id,

date as first_buy_date

from t01

where rk = 1

) c

on a.user_id = c.user_id

left join (

select user_id as user_id,

date as second_buy_date

from t01

where rk = 2

) d

on a.user_id = d.user_id

order by user_id;

发表于 2025-03-12 23:04:13 回复(0)

Mysql

青衣白马少年时

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

l拉了一坨大的，相比于上一题麻烦在第二个购买时间，在这里表连接，想法是排名选出时间为第二，为了防止出现并列时间，所以用dense_rank()

发表于 2025-01-10 21:10:16 回复(0)

Mysql

刷牛客的coder很爱吃香菜

-- 创建一个临时表，利用窗口函数，查出满足date、product_name、status 的订单信息，包括user_id、date、cnt(购买课程数量)以及给下单日期排序rk
with
    temp1 as (
        select
            user_id,
            date,
            count(product_name) over (
                partition by
                    user_id
            ) as cnt,
            row_number() over (
                partition by
                    user_id
                order by
                    date
            ) rk
        from
            order_info
        where
            date > '2025-10-15'
            and product_name in ('C++', 'Java', 'Python')
            and status = 'completed'
    )
select
    t1.user_id,
    t1.date as fist_buy_date,
    t2.date as second_buy_date,
    t1.cnt
from
-- 第一次购买成功
    (
        select
            *
        from
            temp1
        where
            rk = 1
    ) t1
    inner join 
-- 第二次购买成功
    (
        select
            *
        from
            temp1
        where
            rk = 2
    ) t2 on t1.user_id = t2.user_id;

发表于 2025-01-09 15:22:36 回复(0)

Mysql

650366432

select t.user_id
,date1 first_buy_date
,date2 second_buy_date
,t.cnt cnt
from (select user_id
,date date1
,lead(date,1) over(partition by user_id order by date) date2
,count(user_id) over(partition by user_id ) cnt
,row_number() over(partition by user_id order by date) rn
from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++','Python','Java')) t 
where t.cnt >= 2
and t.rn = 1
order by t.user_id;

发表于 2025-01-07 11:42:05 回复(0)

Mysql

年轻的鲸鱼在春招

select
     t1.user_id,
     t1.first_buy_date,
     t2.second_buy_date,
     t1.cnt
from (
        select
            user_id,
            min(date) first_buy_date,
            count(*) cnt
        from ( 
            #符合条件的用户中 符合条件的数据，并对其日期给予排序
            select
            *,row_number()over(partition by user_id order by date) as date_rank
            from
                order_info
            where
                user_id in (
                    #符合条件的用户
                    select
                        user_id
                    from
                        order_info
                    where
                        date > "2025-10-15"
                        and status = "completed"
                        and product_name in ("C++", "Python", "Java")
                    group by
                        user_id
                    having
                        count(*) > 1
                )
                and date > "2025-10-15"
                and status = "completed"
                and product_name in ("C++", "Python", "Java")
        ) t
        group by user_id
) t1
left join ( 
        select
            user_id,
            date second_buy_date
        from ( 
            #符合条件的用户中 符合条件的数据，并对其日期给予排序
            select
            *,row_number()over(partition by user_id order by date) as date_rank
            from
                order_info
            where
                user_id in (
                    #符合条件的用户
                    select
                        user_id
                    from
                        order_info
                    where
                        date > "2025-10-15"
                        and status = "completed"
                        and product_name in ("C++", "Python", "Java")
                    group by
                        user_id
                    having
                        count(*) > 1
                )
                and date > "2025-10-15"
                and status = "completed"
                and product_name in ("C++", "Python", "Java")
        ) t
        where date_rank =2
       ) t2
    on t1.user_id = t2.user_id
order by t1.user_id
很抱歉，这个想的很复杂

发表于 2024-10-23 15:35:48 回复(0)

Mysql

yyyyyyyyui

select

a.user_id,

a.date as first_buy_date,

b.date as second_buy_date,

a.rk as cnt

from

(

select

count(*) over (

partition by

user_id

) as rk,

dense_rank() over (

partition by

user_id

order by

date

) as rk1

from

order_info

where

status = 'completed'

and date > '2025-10-15'

and product_name in ('C++', 'Java', 'Python')

) a

left join (

select

dense_rank() over (

partition by

user_id

order by

date

) as rk1

from

order_info

where

status = 'completed'

and date > '2025-10-15'

and product_name in ('C++', 'Java', 'Python')

) b on a.user_id = b.user_id

where

a.rk1 = 1

and b.rk1 = 2

and a.rk >= 2

order by

a.user_id

发表于 2024-09-18 14:29:03 回复(0)

Mysql

balanceG

with temp1 as (

SELECT *

FROM order_info

WHERE date > '2025-10-15'

AND status = 'completed'

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

temp2 as (SELECT user_id,date,row_number() over (partition by user_id order by date asc) as rk from temp1),

temp3 as (SELECT user_id,date as first_buy_date from temp2 where rk=1),

temp4 as (SELECT user_id,date as second_buy_date from temp2 where rk=2),

temp5 as (select user_id,count(*) as cnt from temp2

group by user_id having count(*) >=2)

select temp3.user_id,first_buy_date,second_buy_date,cnt

from temp3

join temp4 on temp3.user_id = temp4.user_id -- 确保有第二次购买

join temp5 on temp4.user_id = temp5.user_id # 这一步联结是多对1，所以得用join，不能left join，left join会出现none的情况

order by user_id asc;

发表于 2024-09-17 22:59:53 回复(0)

Mysql

Sunny_G

一个笨方法吧，但是应该很好理解

from (
    -- first_buy_date
    select user_id, min(date) as first_date, count(id) as cnt
    from order_info 
    where 
    product_name in ('C++', 'Java', 'Python')
    and date >= '2025-10-15'
    and status = 'completed'
    and user_id in (
        --  符合条件的user_id
        select user_id
        from order_info 
        where 
        product_name in ('C++', 'Java', 'Python')
        and date >= '2025-10-15'
        and status = 'completed'
        group by user_id
        having count(id) >= 2
    )
    group by user_id
) t1 left join (
    -- second_date
    select user_id, date
    from (
        select *, row_number() over(partition by user_id order by date, id) as rn
        from order_info 
        where 
        product_name in ('C++', 'Java', 'Python')
        and date >= '2025-10-15'
        and status = 'completed'
        and user_id in (
            select user_id
            from order_info 
            where 
            product_name in ('C++', 'Java', 'Python')
            and date >= '2025-10-15'
            and status = 'completed'
            group by user_id
            having count(id) >= 2
        )
    ) t
    where rn = 2
) t2 on t1.user_id = t2.user_id
order by t1.user_id asc

发表于 2024-08-14 11:47:53 回复(0)

Mysql

牛客114955764号

with t as (

select

user_id,

`date`,

row_number() over(partition by user_id order by `date` ) as rk,

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

from order_info

where `date`>'2025-10-15' and product_name in ('C++','Java','Python') and status = 'completed'

group by user_id,`date`

)

t1 as (

select

user_id,

case when t.rk = 1 then `date` else '' end as first_buy_date,

case when t.rk = 2 then `date` else '' end as second_buy_date,

cnt

from t

where t.rk<=2

)

t2 as (

select

user_id,

first_buy_date,

cnt

from t1

where t1.first_buy_date <> ''

)

t3 as (

select

user_id,

second_buy_date,

cnt

from t1

where t1.second_buy_date <> ''

)

select

a.user_id,

a.first_buy_date,

b.second_buy_date,

a.cnt

from t2 a

join t3 b on a.user_id = b.user_id;

裂开

发表于 2024-06-09 14:37:21 回复(0)

Mysql

牛客143367843号

select user_id,date,second_date,cnt
from(
select user_id,date,
dense_rank()over(partition by user_id order by date) r,
lead(date,1)over(partition by user_id order by date) second_date,
count(status)over(partition by user_id) cnt
from order_info
where date>'2025-10-15' and status='completed' 
and product_name in ('C++','Python','Java')
)t1
where cnt>=2 and r=1
order by 1

发表于 2024-05-26 14:54:27 回复(0)

Mysql

注意脚下

select aa1.user_id,bb.h1,bb1.h2,aa1.gg2

from (

select user_id,count(product_name) gg2

from order_info

where date > '2025-10-15'

and status='completed'

and product_name in('C++','Java','Python')

group by 1

) aa1

inner join

(

select aa.user_id,aa.date h1

from(

select id,user_id,product_name,status,client_id,date,

count(product_name)over(partition by user_id order by date ) gg1,

count(product_name)over(partition by user_id ) gg

from order_info

where date > '2025-10-15'

and status='completed'

and product_name in('C++','Java','Python')

) aa

where aa.gg > 1 and aa.gg1=1

) bb

on aa1.user_id=bb.user_id

inner join

(

select aa.user_id,aa.date h2

from(

select id,user_id,product_name,status,client_id,date,

count(product_name)over(partition by user_id order by date ) gg1,

count(product_name)over(partition by user_id ) gg

from order_info

where date > '2025-10-15'

and status='completed'

and product_name in('C++','Java','Python')

) aa

where aa.gg > 1 and aa.gg1=2

) bb1

on aa1.user_id=bb1.user_id

order by aa1.user_id asc

发表于 2024-05-23 22:54:24 回复(0)

Mysql

FMW

借鉴了大神的做法，用聚合函数行转列进行。

SELECT 
    user_id,
    min(CASE WHEN rank_date = 1 THEN date END) AS first_buy_date,
    min(CASE WHEN rank_date = 2 THEN date END) AS second_buy_date,
    count(*) AS cnt
FROM(
    SELECT
        user_id,
        date,
        DENSE_RANK()OVER(PARTITION BY user_id ORDER BY date) AS rank_date
    FROM order_info
    WHERE date > '2025-10-15'
        AND status = 'completed'
        AND product_name IN ('C++', 'Java', 'Python')
) AS o2
GROUP BY user_id
HAVING count(*) >= 2
ORDER BY user_id;

发表于 2024-04-17 11:12:45 回复(0)

Mysql

Mr.小美吖

# 窗口函数求次序
with 
t as (
select user_id,date,dense_rank() over(partition by user_id order by date) as ranking
from order_info
where date >= "2025-10-15" and status = "completed" 
and product_name in ("C++","Java","Python") 
group by user_id,date
),

# 对日期进行行列转换
t1 as(
select user_id,
max(case when ranking = 1 then date else 0 end) as first_buy_date,
max(case when ranking = 2 then date else 0 end) as second_buy_date
from t
group by user_id
),

#求总次数
t2 as (
select user_id,count(*) as cnt from order_info
where date >= "2025-10-15" and status = "completed" 
and product_name in ("C++","Java","Python")
group by user_id
) 

# 完成合并
select t1.user_id,t1.first_buy_date,t1.second_buy_date,t2.cnt from t1
inner join t2
on t1.user_id = t2.user_id
where t1.second_buy_date != 0
order by user_id;

发表于 2024-03-14 17:18:35 回复(0)

Mysql

牛客712664144号

with t2 as(

select user_id,date,rank()over(partition by user_id order by date) rk,count(user_id) over(partition by user_id) cnt

from order_info o

where date>'2025-10-15'

and product_name in ('C++','Java','Python')

and status ='completed'

and user_id in(

select user_id from order_info

where date>'2025-10-15'

and product_name in ('C++','Java','Python')

and status ='completed'

group by user_id

having count(*)>1

)

select a.user_id, a.date first_buy_date, b.date secound_buy_date,a.cnt

from t2 a left join t2 b

on a.user_id=b.user_id and b.date-a.date>0

where a.rk=1 and b.rk=2

order by user_id

发表于 2024-03-06 14:14:46 回复(0)

Mysql

牛客605111887号

select s.uid,f.fst,s.date,f.c from
(select user_id as uid,min(date) as fst,count(user_id) as c from order_info
where status='completed'
and product_name in ('C++','Java','Python')
and datediff(date,'2025-10-15')>0
group by uid
having count(uid)>1) as f #购买2次以上的第一天购买日期表和计数表
inner join 
(select user_id as uid,date,rank() over(partition by user_id order by date) as rk
from order_info
where status='completed'
and product_name in ('C++','Java','Python')
and datediff(date,'2025-10-15')>0) as s #窗口函数给购买日期编号
on f.uid=s.uid
where s.rk =2;

	
	
		#两表合起来，同时只选购买日期编号为2的记录

发表于 2024-01-13 18:22:30 回复(0)

Mysql

War张

with t as (
    select distinct
        user_id,
        min(date) over(partition by user_id) first_buy_date,
        nth_value(date,2) over(partition by user_id order by date rows between unbounded preceding and unbounded following) second_buy_date,
        count(id) over(partition by user_id) cnt
    from order_info
    where date > '2025-10-15'
    and status = 'completed'
    and product_name in ('C++','Python','Java')
)
select * from t where cnt >= 2 order by user_id;

发表于 2023-11-14 10:48:40 回复(0)

Mysql

一定要拿下好offer

select * ,
count(1) over(PARTITION BY user_id) as 次数
from order_info
where
    status = 'completed'
    and date > '2025-10-15'
    and product_name in('C++', 'Python', 'Java')
    ORDER BY user_id, date)
    ,
t2 as(
select  user_id,
NTH_VALUE(date,1) over(PARTITION BY user_id) as first_buy_date ,
NTH_VALUE(date,2) over(PARTITION BY user_id) as second_buy_date,
次数
from t1
where 次数>1)
select DISTINCT * from t2

构建临时表1的时候，开窗获取次数，并且对user_id,date进行order by。

构建临时表2的时候，使用固定窗口加nth_value()函数，获取时期，然后去重。

发表于 2023-11-09 13:21:24 回复(0)