首页 > 试题广场 >

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

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

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

算法知识视频讲解

Sqlite

牛客894036218号

select user_id,
min(case when num=1 then date end) as first_buy_date,
min(case when num=2 then date end) as second_buy_date,
cnt
from
(select user_id,date,
dense_rank() over(partition by user_id order by date) as num,
count(1) 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') t
where cnt>=2
group by user_id
order by user_id

发表于 2022-04-24 18:55:48 回复(0)

Sqlite

牛客518214421号

select t_1.user_id,t_1.first_buy_date,t_2.second_buy_date,t_1.cnt
from
(select user_id,date as first_buy_date,cnt
from
(select *,count(*) over(partition by user_id) as cnt,
row_number(*) over(partition by user_id order by date asc) as c_r
from order_info
where status='completed'
and product_name in ('C++','Java','Python')
and date>'2025-10-15') as t
where cnt>1
and c_r=1
order by user_id asc) as t_1
join
(select user_id,date as second_buy_date,cnt
from
(select *,count(*) over(partition by user_id) as cnt,
row_number(*) over(partition by user_id order by date asc) as c_r
from order_info
where status='completed'
and product_name in ('C++','Java','Python')
and date>'2025-10-15') as t
where cnt>1
and c_r=2
order by user_id asc) as t_2
on t_1.user_id=t_2.user_id

发表于 2022-03-08 17:17:50 回复(3)

Sqlite

大肥闻

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

发表于 2022-03-04 20:54:08 回复(0)

Sqlite

Bryce230

这道题是第四题基础上的加深，考查的是行转列问题，我的思路是建立一个临时表，然后基于该表建立一个第一次购买和一个第二次购买的信息表，之后关联聚合即可。

WITH tmp AS
(
SELECT a.user_id, a.date, b.cnt AS cnt, 
       ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY a.date ASC) AS rn
FROM order_info a,
    (SELECT id, 
           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') b
WHERE a.id = b.id AND b.cnt >= 2
)
SELECT t1.user_id, t1.date AS first_buy_date, t2.date AS second_buy_date, t1.cnt
FROM (SELECT * FROM tmp WHERE rn=1) t1
INNER JOIN (SELECT * FROM tmp WHERE rn=2) t2 ON t1.user_id = t2.user_id
GROUP BY t1.user_id, t1.cnt
ORDER BY t1.user_id ASC;

看了下讨论区大家的解法，总结一下：

1）用if函数和聚合函数一起使用，学到了，原来SQL也有if函数。

2）用case when和聚合函数一起使用，原理同1)。

3）row_number(）窗口函数后限制序号为1和2，然后取最小和最大即可，妙呀！

4）lead(date,1,0) 窗口函数，获取某顺序字段的下一条记录。（lead(col,n,default) 用于统计窗口内往下第n个值）

发表于 2022-02-10 10:12:27 回复(0)

Sqlite

不停感叹的老林

SELECT
    user_id,
    min(date) AS first_buy_date,
    min(
        CASE
            WHEN ranking > 1 THEN date
            ELSE NULL
        END
    ) AS second_buy_date,
    count(*) AS cnt
FROM
    (
        SELECT
            *,
            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++', 'Python', 'Java')
    ) AS o1
GROUP BY
    user_id
HAVING
    count(*) >= 2
ORDER BY
    user_id;

发表于 2022-01-09 22:58:33 回复(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)

Sqlite

牛客230245638号

一干到底：

select t2.user_id,t2.md,t.date,t2.cnt

from (
select c.user_id,c.date,c.rn,c.cnt from
(select b.*,row_number()over(partition by user_id order by date) rn
from
( select * from
(select a.*,
min(date)over(partition by user_id,product_name) md,
count(1) over(partition by user_id) cnt
from order_info a
where product_name in ('C++','Java','Python') and status='completed' and date >'2025-10-15')
where cnt > 1
) b ) c
where c.rn=2
) t

left join

(select user_id ,min(date) md ,count(1) 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(1) >1) t2
on t.user_id=t2.user_id

发表于 2021-09-14 00:17:35 回复(0)

Sqlite

牛客939036156号

lead函数

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

发表于 2021-08-24 10:05:59 回复(0)

Sqlite

Tina要成为数据分析师呀

在上题的基础上，对符合条件的日期进行排序，使用case when 筛选出符合条件的日期

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

发表于 2021-08-18 19:56:14 回复(0)

Sqlite

JodieMJ

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

发表于 2021-08-12 16:25:46 回复(0)