首页 > 试题广场 >

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

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

热度指数：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

算法知识视频讲解

牛客452218034号

--用sql sever ，写的，不兼容sqlite

with

T1 as --提前user_id,和计数
        (
        select o.user_id,COUNT(product_name) as 计数
        from order_info o
        where o.date>'2025-10-15' and o.status='completed' and o.product_name in ('C++','Java','Python')
        group by o.user_id
        ),
--select * from T1

T2 as --再T1的基础上,加上date
        (
        select t1.*,o.date,row_number()over(partition by T1.user_id order by date ) rand
        from T1
        left join order_info o on T1.user_id=o.user_id and o.date>'2025-10-15' and o.product_name in ('C++','Java','Python')
        ),
T3 as --第一次购买记录
        (
        select t2.user_id,t2.计数,MIN(date) as 第一次
        from T2
        group by t2.user_id,t2.计数
        ),

T31 as --第二次购买记录
        (
        select *
        from T2
        where t2.rand=2
        )
select t3.user_id,t3.第一次,t31.date,t3.计数
from T31
left join T3 on t31.user_id=t3.user_id

发表于 2021-06-24 11:19:18 回复(0)

牛客421553514号

select user_id, min(date),(select date from (select *, count(user_id) over(partition by user_id)cnt,
      rank() over(partition by user_id order by date) ranking
from order_info
where status = 'completed'
and date > '2025-10-15'
and product_name in ('Python', 'Java', 'C++')) nt where ranking = 2 and nt1.user_id = nt.user_id), cnt
from (select *, count(user_id) over(partition by user_id)cnt,
      rank() over(partition by user_id order by date) ranking
from order_info
where status = 'completed'
and date > '2025-10-15'
and product_name in ('Python', 'Java', 'C++')) nt1
where cnt >= 2
group by user_id
order by user_id

分享个麻烦的方法，也可以换个思路吧

发表于 2021-03-09 21:26:54 回复(0)

Coca9527

-- 再上一题的基础上，结合窗口函数排名，用join表连接即可
select t.user_id, t.first_buy_date,s.second_buy_date,t.cnt
from ( select user_id,
                                    min(date) as first_buy_date,
                                    count(*) as cnt
                        from order_info
                     where date > '2025-10-15' and product_name in ('Java', 'Python', 'C++') and status = 'completed'
                     group by user_id
                     having count(*) > 1) t left join ( select user_id, date as second_buy_date
                                                                                                 from ( select *, row_number() over (partition by user_id order by date asc) as ranking
                                                                                                                    from order_info
                                                                                                                 where date > '2025-10-15' and product_name in ('Java', 'Python', 'C++') and status = 'completed') t2
                                                                                                where t2.ranking = 2) s on t.user_id =s.user_id
order by t.user_id;

发表于 2021-03-08 13:13:02 回复(0)

qstalking

#表1：把符合条件的user_id查询出来

#表2：把符合条件的user_id及其对应的前两次购买日期查询出来，需要用到排序函数

#表2与order_info表进行左关联，查出user_id，第一次购买时间（即表2中该user_id的最小日期），第二次购买时间（即表2中该user_id的最大日期），order_info表中符合条件的订单数量

#按照user_id升序排序

select t2.user_id
	  ,min(t2.date) as first_buy_date 
	  ,max(t2.date) as second_buy_date
	  ,count(distinct oi.id) as cnt
from 
(select user_id
       ,date
from 
(select user_id
       ,date
       ,row_number()over(partition by user_id order by date) as rank1
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(id)>=2 )
and product_name in ('C++','Java','Python')
and status='completed'
and date >'2025-10-15')t1
where rank1<=2)t2
left join order_info oi on t2.user_id=oi.user_id
where oi.product_name in ('C++','Java','Python')
and oi.status='completed'
and oi.date>'2025-10-15'
group by t2.user_id
order by t2.user_id;

编辑于 2021-03-02 21:38:42 回复(2)

😜20190924211806

可考虑使用lead函数，该函数作用是：获取某顺序字段当前记录的下一条记录；具体用法是：lead(date,1,0)over(partition by user_id order by date) as second_date 这样便可以得到第二次购买日期字段

select user_id,min(date) as first_buy_date,
second_buy_date,
count(*)as cnt 
from
(select *,count(*)over(partition by user_id)as cnt,
 lead(date,1,0)over(partition by user_id order by date) as second_buy_date
 from order_info 
 where product_name in('Java','C++','Python') 
  and status='completed'
  and date>'2025-10-15')t
where t.cnt>1 group by user_id order by user_id

编辑于 2021-03-02 17:08:49 回复(13)

Mysql

Qiuxj

SELECT a.user_id,
min(a.date) first_buy_date,
max(a.date) second_buy_date,
a.cnt
FROM(select user_id, date,
     count(*) over (partition by user_id) cnt,
     row_number() over (partition by user_id order by date) bd_rk
     from order_info
     where date>'2025-10-15'
     and status='completed'
     and product_name IN ('C++','Java','Python')) a
WHERE a.cnt>=2 and a.bd_rk<=2
GROUP BY a.user_id
ORDER BY a.user_id;

发表于 2021-10-19 19:17:40 回复(7)

曾永铭

-- 学术菜鸡第一次分享代码，请各位大佬指正
with t as (
select
    *,
    rank() over(partition by user_id order by date asc) as r,
    count(*) over(partition by user_id) as c
from order_info oi
where date > '2025-10-15'
and product_name in ('C++', 'Java', 'Python')
and status = 'completed'
)

select
    t.user_id,
    min(t.date) as first_buy_date,
    s.date,
    t.c
from t
left join (select user_id, date from t where t.r = 2) as s
    on t.user_id = s.user_id
where t.c >= 2
group by user_id
-- 可能有点冗杂但是可能比较好理解？ 还可以加上第三、第四次购买记录等等等等

发表于 2021-04-08 15:13:14 回复(1)

yiiiiiyang

select user_id
      ,min(case when r = 1 then date end) as first_buy_date
      ,min(case when r = 2 then date end) as second_buy_date
      ,count(1)                           as cnt
from ( 
    select *
          ,rank() over(partition by user_id order by date) as r
    from order_info
    where 1=1
    and status = 'completed'
    and date > '2025-10-15'
    and product_name in ('C++', 'Python', 'Java'))t
group by user_id
having count(1) >= 2

发表于 2021-03-04 14:45:56 回复(14)

Mysql

晓_

看看什么叫最长解，哈哈，其实重复查了3次，没必要

SELECT
    t1.user_id,t2.date,t3.date,max(t1.r)
FROM
(SELECT
    *,row_number() over(partition by user_id order by date) as r
FROM
    order_info
where
    date>='2025-10-15'
and
    status = 'completed'
and
    product_name in ('C++','Java','Python')) t1
LEFT JOIN
    (SELECT
    *,row_number() over(partition by user_id order by date) as r
FROM
    order_info
where
    date>='2025-10-15'
and
    status = 'completed'
and
    product_name in ('C++','Java','Python')) t2
ON
    t1.user_id=t2.user_id and t2.r=1
LEFT JOIN
    (SELECT
    *,row_number() over(partition by user_id order by date) as r
FROM
    order_info
where
    date>='2025-10-15'
and
    status = 'completed'
and
    product_name in ('C++','Java','Python')) t3
ON
    t1.user_id=t3.user_id and t3.r=2
group BY
    t1.user_id
HAVING
    max(t1.r)>=2

再给个短版，max函数配合if完美

SELECT
    user_id,max(if(r=1,date,null)),max(if(r=2,date,null)),max(r)
FROM
    (SELECT
        *,row_number() over(partition by user_id order by date) as r
    FROM
        order_info
    where
        date>='2025-10-15'
    and
        status = 'completed'
    and
        product_name in ('C++','Java','Python')) t
group BY
    user_id
HAVING
    max(r)>=2
order BY
    user_id

编辑于 2021-07-19 20:44:48 回复(1)

低调小学生来了

我尽力了，第二就是MIN(data > MIN(data))

SELECT t.user_id, t.first_buy_date, MIN(date) AS second_buy_date, t.cnt FROM order_info AS o
INNER JOIN

(SELECT user_id, MIN(date) AS first_buy_date, 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
HAVING COUNT(user_id) >= 2)t

ON (o.date > t.first_buy_date)
AND (o.date > "2025-10-15")
AND (o.status = "completed")
AND (o.product_name IN ("C++", "Java", "Python"))
AND (o.user_id = t.user_id)
GROUP BY o.user_id
ORDER BY t.user_id ASC;

发表于 2021-03-20 11:16:18 回复(1)

牛客923860633号

select t.user_id as user_id, min(t.date) as first_buy_date,
    max(t.date) as second_buy_date, t.co as cn
from (
    select *,
        count(*) over(partition by user_id) co,
        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.co >= 2
and t.rn <= 2
group by t.user_id;

co用于判断每个用户购买的课程数
rn用于对日期排序，提取前两个日期

发表于 2021-10-02 16:20:04 回复(0)

Mysql

学电脑来新华

SELECT user_id,
max(case when rk=1 then date end),
max(case when rk=2 then date end),
max(rk) 
FROM (
SELECT 
    user_id,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 date>'2025-10-15' ) AS t  
GROUP BY t.user_id having count(t.user_id)>=2;

发表于 2021-09-10 10:54:43 回复(2)

Mysql

有滋有味小熊鞋

with n as (
select*,rank() over(partition by user_id order by date asc) as dnum,count(*) 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'
)
select user_id,min(date),max(date),cnt
from n
where cnt >= 2 and dnum <=2
group by user_id,cnt
order by user_id asc;

发表于 2021-07-26 18:09:41 回复(1)

小兔兔。

SELECT a.user_id , a.first_buy_date , c.date , a.cnt
FROM
(SELECT user_id , MIN(date) first_buy_date , COUNT(*) 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(*) >= 2) a
JOIN 
(SELECT user_id , date
FROM
(SELECT user_id , date , RANK() OVER(PARTITION BY user_id ORDER BY date) rnk
FROM order_info
WHERE date > '2025-10-15'
AND status = 'completed'
AND product_name IN ('C++' , 'Java' , 'Python')) b
WHERE b.rnk = 2) c
ON a.user_id = c.user_id
ORDER BY a.user_id;

发表于 2021-03-01 23:14:03 回复(0)

Mysql

Hikvision_boy

with q1 as
(select * ,row_number()over(partition by user_id order by date) px,count(status)over(partition by user_id) bb
from order_info where date >'2025-10-15' and status='completed' and
product_name in ('C++','Java','Python')), //展示源表+每个人根据日期的倒叙的排名+每个人的购买次数
q2 as (select * from q1 where px =1), //求出排日期拍第一的作为一张表
q3 as (select * from q1 where px =2) //求出日期排第二的作为一张表，然后两表进行 join
select q2.user_id,q2.date,q3.date,q2.bb from q3 join q2 on q2.user_id=q3.user_id order by user_id

发表于 2022-02-22 23:02:39 回复(1)

Mysql

wspjh

在四的基础上加上select子查询即可

select user_id,min(date) first_buy_date,
(select min(date) from order_info where  date> min(b.date) and user_id=b.user_id
 and status='completed' and product_name in ('C++','Java','Python') ) as second_buy_date
,count(1) cnt from order_info b 
where b.date>'2025-10-15' and b.status='completed' and b.product_name in ('C++','Java','Python')
group by user_id
having count(1)>=2
order by user_id

发表于 2022-01-25 10:07:30 回复(1)

Mysql

Queen_D

一、计算排名和总数

select user_id,date,
       row_number() over(partition by user_id order by date) as ranking,
       count(*) over(partition by user_id) as cnt
from order_info
where date>'2025-10-15' and product_name in ('C++','Python','Java')
and status='completed'

1	57\|2025-10-23\|1\|1
2	557336\|2025-10-16\|1\|2
3	557336\|2025-10-23\|2\|2
4	230173543\|2025-10-16\|1\|1

二、筛选出前两行以及过滤掉只有一条记录的user

select *
from
(select user_id,date,
       row_number() over(partition by user_id order by date) as ranking,
       count(*) over(partition by user_id) as cnt
from order_info
where date>'2025-10-15' and product_name in ('C++','Python','Java')
and status='completed') as t
where cnt>=2 and ranking<=2

1	557336\|2025-10-16\|1\|2
2	557336\|2025-10-23\|2\|2

三、输出最终结果

select user_id,min(date) as first_buy_date,max(date) as second_buy_date,cnt
from
(select *
from
(select user_id,date,
       row_number() over(partition by user_id order by date) as ranking,
       count(*) over(partition by user_id) as cnt
from order_info
where date>'2025-10-15' and product_name in ('C++','Python','Java')
and status='completed') as t
where cnt>=2 and ranking<=2) as t1
group by user_id
order by user_id

发表于 2022-01-20 09:53:02 回复(0)

Mysql

Mozart-k626

#①查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id#

这个跟前面一样,先用窗口函数筛选出每个用户的最大购买数，再用子查询限定2个及两个以上

（这里代码只是为了演示怎么筛选第①个条件）

select
tt.user_id
from
(
SELECT
*
,count(*)over(partition by user_id ) order2
from order_info
where date > '2025-10-15'
and product_name in('C++','Java','Python')
and status = 'completed'
) tt
where tt.order2 > 1

#②第一次购买first_buy_date#

- first_buy_date，指2025-10-15号之后，第一次购买，所以是①的基础上，选每个用户的最小日期，但这里选择用窗口函数排序，不直接用min(date),在③中会解释

#③第二次购买second_buy_date#

- second_buy_date，这里要注意，用户第二次购买，不一定就是第一次购买的下一日，可能同一天买了2次，可能隔了几天才买，所以想筛选不同用户情况时，要用窗口函数进行排序，排名第1，就是第一次购买，排名第2的，是第二次购买

/ /不适用的窗口函数1：rank()，理由：如果用户第二次购买跟第一次在同一天，用rank排序结果→ 1，1，3，4

（第一次跟第二次排名一样，无法筛选出第二次）

/ /不适用的窗口函数2：dense_rank()，理由：跟前面一样，一、二次购买排名相同，排序结果→1，1，2，3

（这里筛选排名第2，出来的结果实际上是第3 or 第4次+购买）

/ /适用的窗口函数：row_number()，理由：不管第二次购买跟第一次购买是否同一天，或隔几天，排序结果都是→ 1，2，3，4

（这里筛选排名第2的，就一定是第二次购买）

PS：可以仔细看一下三个窗口函数的不同用法文章

#④购买成功的C++课程或Java课程或Python课程的次数cnt#

这里就是按照user_id进行分区，然后count（）每个用户的行数，这里也是用count()over()窗口函数，理由下面会解释

#⑤如何把以上数据全部综合在一起#

先上最终代码

SELECT
tt.user_id
,min(case when tt.buy_day = '1' then tt.date end) first_buy_date
,max(case when tt.buy_day = '2' then tt.date end) second_buy_date
,tt.tj cnt
from 
    (
        SELECT
        *
        ,count(*)over(partition by user_id ) order2
        ,row_number()over(partition by user_id order by date) buy_day /*这里是对购买记录进行排名
        ,count(*)over(partition by user_id ) tj /*这里是对购买次数进行统计
        from order_info
        where date > '2025-10-15'
        and product_name in('C++','Java','Python')
        and status = 'completed'
    ) tt
where tt.order2 > 1
and tt.buy_day <= 2 /*这里限定了，只有前2次购买被筛选出来
GROUP by tt.user_id
order by tt.user_id;

注意点1：要把第一次、第二次购买日期组合起来，因为不同的日期对应不同的课程类别，购买设备等，因此想通过一个日期 = 另个日期这种方式，会影响其他列的值

解决思路：用条件判断case when，它只对满足条件的值，返回一个数据，不会影响其他列的值（类似于备注的感觉）

注意点2：case函数的外层，要嵌套min() ＆Max()函数，用来筛选第一次，第二次购买

- 为什么max()可以筛选第二次？不会筛选到第3、第4次购买？

- 理由：在前一步，对购买次数进行排序后，这里的where 条件筛选排名 <= 2，意思是只选前2次的购买记录，因此出来的排名只有1＆2，因此最大值max（）= 2

注意点3：最后要求对购买的次数进行统计，用count窗口函数，且不能order by date，原因是对日期排序后，窗口函数会对当前日＆当前日之前的记录进行累计，这样在顶部①那里筛选购买2次及以上时，会把这里count() = 1的记录给剔除，最后显示结果会有缺失

例如：

未order by date

日期列购买次数列窗口函数 count 统计列

10月5日 1次 3

10月6日 2次 3

order by date

日期列购买次数列窗口函数 count 统计列

10月5日 1次 1

10月6日 2次 3

发表于 2022-01-13 15:16:04 回复(0)

Sqlite

ValueatRisk

与之前的题不同的是，本题需要知道第二次购买的时间，那么我们除了COUNT()窗口函数外，还需要一个排序的窗口函数，新的子表函数

SELECT *,
       ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) AS rn,
       COUNT(*) OVER(PARTITION BY user_id) AS cnt
FROM order_info
WHERE product_name IN ('C++', 'Java', 'Python')
      AND status = 'completed'
      AND date > '2025-10-15'

MIN(date)可以筛选出首次购买，第二次购买即窗口排序rn=2的日期，通过CASE WHEN筛选即可，完整代码

SELECT user_id,
       MIN(date) AS first_buy_date,
       MIN(CASE WHEN rn = 2 THEN date ELSE '9999-12-31' END
       ) AS second_buy_date,
       cnt
FROM (SELECT *,
             ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) AS rn,
             COUNT(*) OVER(PARTITION BY user_id) AS cnt
     FROM order_info
     WHERE product_name IN ('C++', 'Java', 'Python')
          AND status = 'completed'
          AND date > '2025-10-15'
     )
WHERE cnt >= 2
GROUP BY user_id
ORDER BY user_id

发表于 2021-10-26 22:21:47 回复(0)

Mysql

牛客128654040号

-- 方案一
with aa as (
    select user_id, 
    date, 
    min(date) over(partition by user_id order by date) as first_buy_date, 
    row_number() over(partition by user_id order by date) as rn, 
    count(1) over(partition by user_id) as cnt
    from order_info
    where date > '2025-10-15' and 
        product_name in ('Python', 'C++', 'Java') and 
        status = 'completed'
)
select distinct aa.user_id, aa.first_buy_date, a2.date as second_buy_date, aa.cnt
from aa 
inner join aa a2 on aa.user_id = a2.user_id and a2.rn = 2
order by aa.user_id;


-- 方案二 
with aa as (
    select user_id, 
    row_number() over(partition by user_id order by date) as rn, 
    min(date) over(partition by user_id order by date) as first_buy_date, 
    lead(date,1,0)over(partition by user_id order by date) as second_buy_date,
    count(1) over(partition by user_id) as cnt
    from order_info
    where date>'2025-10-15' and 
        product_name in ('Python', 'C++', 'Java') and 
        status = 'completed'
)
select user_id, first_buy_date, second_buy_date, cnt
from aa where cnt > 1 and rn = 1
order by user_id;

发表于 2021-10-13 18:13:14 回复(0)