首页 > 试题广场 >

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

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

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

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

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

id	user_id	product_name	status	client_id	date	is_group_buy
1	557336	C++	no_completed	1	2025-10-10	No
2	230173543	Python	completed	2	2025-10-12	No
3	57	JS	completed	0	2025-10-23	Yes
4	57	C++	completed	3	2025-10-23	No
5	557336	Java	completed	0	2025-10-23	Yes
6	57	Java	completed	1	2025-10-24	No
7	557336	C++	completed	0	2025-10-25	Yes

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

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

。。。

最后1行表示user_id为557336的用户在2025-10-25的时候使用了下了C++课程的拼团(is_group_buy为Yes)订单，拼团不统计客户端，所以client_id所以为0，状态为购买成功。

有一个客户端表(client)，简况如下:

id	name
1	PC
2	Android
3	IOS
4	H5

请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，最后结果按照第一列(source)升序排序，以上例子查询结果如下:

source	cnt
GroupBuy	2
IOS	1
PC	1

解析:

id为4，6的订单满足以上条件，且因为4是通过IOS下单的非拼团订单，则记: IOS 1

，6是通过PC下单的非拼团订单，则记: PC 1;

id为5，7的订单满足以上条件，且因为5与7都是拼团订单，则记: GroupBuy 2;

最后按照source升序排序。

示例1

输入

drop table if exists order_info;
drop table if exists client;
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,
is_group_buy varchar(32) NOT NULL,
PRIMARY KEY (id));

CREATE TABLE client(
id int(4) NOT NULL,
name varchar(32) NOT NULL,
PRIMARY KEY (id)
);

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

INSERT INTO client VALUES
(1,'PC'),
(2,'Android'),
(3,'IOS'),
(4,'H5');

输出

GroupBuy|2
IOS|1
PC|1

算法知识视频讲解

是咸蛋黄呀

依旧先连接再筛选

select source, 
    count(*) as cnt
from (
    select a.*, 
        count(*) over(partition by user_id) as num,
        case when is_group_buy = 'Yes' then 'GroupBuy' else b.name end as source
    from order_info a left join client b
    on a.client_id = b.id
    where status = 'completed'
    and date > '2025-10-15'
    and product_name in('Python','Java','C++')
    ) a
where num >= 2
group by source
order by source
;

发表于 2021-05-23 12:37:18 回复(2)

奥里给给

代码如下：

select Ifnull(l.name,'GroupBuy') as source,k.cnt

from
(select client_id,count(is_group_buy) as cnt
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++','java','python')
group by user_id
having count(status) >= 2)
and date > '2025-10-15' and status = 'completed' and product_name in ('c++','java','python')
group by client_id) as k
left outer join
client as l
on k.client_id = l.id
order by source

发表于 2021-05-22 21:38:57 回复(0)

Toto厉害

select ifnull(min(client.name), 'GroupBuy')as source, count(*) as cnt
from
(
select id, user_id, client_id, date, count(*)over(partition by user_id) as n
from order_info
where date > '2025-10-15'
and product_name in ('C++', 'Java', 'Python')
and status = 'completed'
)a
left join client
on a.client_id = client.id
where a.n >= 2
group by a.client_id
order by source

发表于 2021-03-11 17:22:59 回复(0)

BWTNjIrMFoan

根据上一题的思路，在加个分组及count函数即可

select ifnull(T2.name, 'GroupBuy') source, count(*)cnt
from (
    select *, count(*) over (partition by user_id) ucnt
    from order_info 
    where date > '2025-10-15'
    and product_name in ('C++', 'Python', 'Java')
    and status = 'completed'
) T
left join client T2 on T2.id = T.client_id
where ucnt > 1
group by source
order by source

发表于 2021-04-10 11:42:55 回复(12)

牛客421553514号

select (case when client_id=0 then 'GroupBuy' else (select name from client c where c.id = nt.client_id ) end) source,count(*)
from(select *, count(id) over(partition by user_id) cnt
from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('Java', 'Python', 'C++')) nt
where cnt >= 2
group by source
order by source

发表于 2021-03-10 20:05:54 回复(1)

Mysql

冷千千

对于group by和as问题的回答：

Mysql中，group by中可以使用别名，order by中可以使用别名，where中不能使用别名。

计算的执行顺序的确是select晚于group by，但实际上后台会先走一遍select先将表头字段生成，group by 时可调用这个缓存。

对于订单分析7道题目的总结：

题号	字段1	字段2	字段3	字段4	字段5	考察重点
1	满足特定条件的订单信息id	user_id	product_name	client_id	date	order by
2	下单数量大于1的用户信息					见下面的2种写法
3	下单数量大于1的订单信息					2种写法中窗口函数的那种才使用
4	用户user_id	首次购买日期first_buy_date	用户购买数量cnt			1.first_value()over 2.count()over
5	用户user_id	首次购买日期first_buy_date	二次购买日期second_buy_date	用户购买数量cnt		1.first_value()over 2.rank()0ver113 3.dense_rank()over112此处用这个最合适 4.row_nmber()over 4.sum(case when)
6	订单id	是否拼团is_group_buy	客户端信息client_name			1.count()over在不改变结构的情况下把购买大于两次的订单信息筛选出来。如果只需要用户信息就用group by和having，如果需要订单id等就用窗口函数。
7	客户端名字source	该客户端订单量cnt				1.left join 2.count()over根据不同条件（客户id和客户端id）partition by

下单数量大于1的客户id第一种写法，用聚合函数count()over和where：

select
distinct a.user_id 
from
        (select user_id
             ,count(id)over(partition by user_id) buy
            from order_info
            where date>'2025-10-15'
                and product_name in ('C++','Java','Python')
                and status='completed'
           )a 
where buy>1

下单数量大于1的客户id第2种写法，用count和having：

select a.user_id 
from
        (select user_id,count(id) buy

        from order_info
        where date>'2025-10-15'
            and product_name in ('C++','Java','Python')
            and status='completed'
        group by user_id
        having buy>1
        order by user_id)a

编辑于 2022-02-01 13:54:59 回复(0)

eggman

先筛选条件并选出大于等于2的订单，再分组，修改值

select 
    if(name is null,'GroupBuy',name) as source,count(1) as cnt 
        ##  修改null值为GroupBuy
from(
    select  
        user_id,is_group_buy,name,count(user_id) over(partition by user_id) as cnt 
    FROM 
        `order_info` 
    left join client  
    on 
        order_info.client_id = client.id
    where 
        date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')
) test 
    where cnt>1     
        ## 筛选出大于等于2的
    group by name
        ## 分组
    order by source
        ## 排序

编辑于 2021-03-09 21:16:26 回复(0)

Mysql

SandyFlyHigh

GroupBuy的订单，无设备ID，需要用LEFT JOIN。

SELECT
    source,
    COUNT(*) AS cnt
FROM
    (SELECT
        order_info.*,
        COUNT(*)OVER(PARTITION BY user_id) AS fz,
        IF(is_group_buy = 'Yes', 'GroupBuy', name) AS source
    FROM order_info LEFT JOIN client ON client_id = client.id
    WHERE
        date > '2025-10-15'
        AND
        status = 'completed'
        AND
        product_name IN ('C++','Java','Python')) AS t
WHERE fz >= 2
GROUP BY source
ORDER BY source;

发表于 2022-01-25 16:31:40 回复(1)

林冲吃牛肉

解法

with t1 as (
    select 
        (
            case o.is_group_buy
                when 'Yes' then
                    'GroupBuy'
                else
                    c.name
            end
        ) as source,
        count(o.id) over(partition by user_id) as cnt
    from order_info o
    left join client c
    on o.client_id = c.id
    where o.date > '2025-10-15'
    and o.status = 'completed'
    and o.product_name in ('C++', 'Java', 'Python')
)
select source, count(*) from t1 where cnt >= 2
group by source
order by source

发表于 2021-05-27 01:28:00 回复(0)

在敲键盘的修勾很超脱

很简单的一种解法

select (case

when t.client_id=0 then "GroupBuy"

else c.name

end) as source,

count(*) cnt

from (select *,

count(*) over(partition by user_id) con

from order_info

where date>='2025-10-15' and status='completed' and

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

) t

left join client c on c.id=t.client_id

where date>='2025-10-15' and status='completed' and

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

and t.con>=2

group by client_id

order by source;

# 执行顺序：from、where、group by、select、order by

发表于 2023-08-08 15:07:46 回复(0)

Mysql

Leila0328

select a.source,count(*)
from
(select o.*,
(case when client_id =0 then 'GroupBuy' else c.name end) as source
 ,count(*) over (partition by user_id) as c
from order_info o
left join client c
on o.client_id=c.id
where status='completed'
and product_name in ('C++','Java','Python')
and datediff(date,'2025-10-15')>0) as a
where a.c>=2
group by a.source
order by a.source

发表于 2021-09-06 15:54:36 回复(1)

Mysql

牛客923860633号

解法一

不使用窗口函数

select (
    case 
    when t.is_group_buy = 'Yes' then 'GroupBuy' 
    else c.name 
    end
) as source, count(*) cnt
from (
    select *
    from order_info
    where date > '2025-10-15'
    and status = 'completed'
    and product_name in ('C++', 'Java', 'Python')
    and user_id in (select user_id
                   from order_info
                   where date > '2025-10-15'
                   and status = 'completed'
                   and product_name in ('C++', 'Java', 'Python')
                   group by user_id
                   having count(distinct product_name) >= 2
                  )
    order by id
) as t
left join client c
on t.client_id = c.id
group by t.client_id
order by source;

select (
    case 
    when t.is_group_buy = 'Yes' then 'GroupBuy' 
    else c.name 
    end
) as source, count(*) cnt
from (
    select *
    from order_info
    where date > '2025-10-15'
    and status = 'completed'
    and product_name in ('C++', 'Java', 'Python')
    and user_id in (select user_id
                   from order_info
                   where date > '2025-10-15'
                   and status = 'completed'
                   and product_name in ('C++', 'Java', 'Python')
                   group by user_id
                   having count(distinct product_name) >= 2
                  )
    order by id
) as t
left join client c
on t.client_id = c.id
group by t.client_id
order by source;

与上题基本相同
- 需要客户端名字和数量，可以使用上一题连接得到的表
- 客户端名字上题已经有了，就差将NULL转换为GroupBuy（通过case）
- cnt只要按客户端名分组直接计数就可以得到

解法二

使用窗口函数

select ifnull(c.name, "GroupBuy") source, count(*) cnt
from (
    select *,
        count(*) over(partition by user_id) co
    from order_info
    where date > '2025-10-15'
    and status = 'completed'
    and product_name in ('C++', 'Java', 'Python')
) t 
left join client c
on t.client_id = c.id
where t.co >= 2
group by source
order by source;

编辑于 2021-10-02 16:47:43 回复(0)

小青年_

select if(o.client_id = 0, 'GroupBuy', c.name) as source, count(*) as cnt
from order_info as o
join (
    select user_id
    from order_info as o
    where o.status = 'completed'
      and o.date >= '2025-10-15'
      and o.product_name in ('C++', 'Java', 'Python')
    group by user_id
    having count(*) > 1
) as temp on temp.user_id = o.user_id
left join client c on o.client_id = c.id
where o.status = 'completed'
      and o.date >= '2025-10-15'
      and o.product_name in ('C++', 'Java', 'Python')
group by o.client_id
order by source

发表于 2021-04-19 15:47:14 回复(0)

Followyoyrheart

select ifnull(c.name,'GroupBuy') as source,count(1) as cnt
from (select client_id,count(1) over(partition by user_id) as num
from order_info
where product_name in ('C++','Python','Java') and date > '2025-10-15' and status = 'completed') t
left join client c
on t.client_id = c.id
where t.num >= 2
group by source
order by source

发表于 2021-04-08 15:04:50 回复(0)

XVenlenter

有上一题做基础，就很简单了。

1、先查出满足条件的记录

select o1.id,o1.is_group_buy,
IFNULL(c.name, 'GroupBuy') clientName
from order_info o1
LEFT JOIN client c on o1.client_id=c.id
where o1.date>'2025-10-15' and o1.status='completed' and o1.product_name in ('C++','Java','Python')
and o1.user_id in (
select user_id
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
having count(o.id)>=2)

得到结果

2、再对上述数据进行分组查询就可以了

select x.clientName as source,
count(x.id) as cnt
from 
(select o1.id,o1.is_group_buy,
IFNULL(c.name, 'GroupBuy') clientName
from order_info o1
LEFT JOIN client c on o1.client_id=c.id
where o1.date>'2025-10-15' and o1.status='completed' and o1.product_name in ('C++','Java','Python')
and o1.user_id in (
select user_id
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
having count(o.id)>=2)) x
group by x.clientName
order by x.source asc;

发表于 2021-03-29 17:14:23 回复(1)

Kalina2023

我的思路

第一步：先求出满足条件的订单信息

(SELECT *
FROM order_info
WHERE 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(*) >= 2)
AND date > '2025-10-15'
AND product_name IN ('C++','Java','Python')
AND status = 'completed') oi

第二步：在此基础上构建一个表，和client连接，将client_id换成client_name，将is_group_by是yES的client_name换为GroupBuy

(
    SELECT tmp.id,
    CASE WHEN tmp.is_group_buy = 'Yes' THEN 'GroupBuy' ELSE c.name END
    AS name
    FROM
    (
        SELECT *
        FROM order_info
        WHERE 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(*) >= 2)
        AND date > '2025-10-15'
        AND product_name IN ('C++','Java','Python')
        AND status = 'completed'
    ) tmp
    LEFT JOIN client c 
    ON tmp.client_id = c.id
)oi

第三步：按照client_name聚集，再COUNT

SELECT oi.name AS source, COUNT(oi.id) AS cnt
FROM 
(
    SELECT tmp.id,
    CASE WHEN tmp.is_group_buy = 'Yes' THEN 'GroupBuy' ELSE c.name END
    AS name
    FROM
    (
        SELECT *
        FROM order_info
        WHERE 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(*) >= 2)
        AND date > '2025-10-15'
        AND product_name IN ('C++','Java','Python')
        AND status = 'completed'
    ) tmp
    LEFT JOIN client c 
    ON tmp.client_id = c.id
)oi
GROUP BY oi.name
ORDER BY oi.name ASC

发表于 2021-03-25 11:24:11 回复(2)

嘿嘿哲哲

SELECT CASE WHEN client_id = 0 THEN 'GroupBuy' ELSE C.name END source, COUNT(*) cnt
FROM order_info OI
LEFT JOIN client C
ON OI.client_id = C.id
WHERE user_id IN(
    SELECT user_id
    FROM order_info
    WHERE product_name IN ('C++', 'Python', 'Java') AND date > '2025-10-15' AND status = 'completed'
    GROUP BY user_id
    HAVING COUNT(*) >= 2
) AND product_name IN ('C++', 'Python', 'Java') AND date > '2025-10-15' AND status = 'completed'
GROUP BY CASE WHEN client_id = 0 THEN 'GroupBuy' ELSE C.name END
ORDER BY source

子查询找出购买2次以上的用户，CASE表达式区分购买渠道

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

Akira_aaa

select ifnull(c.name,'GroupBuy') source, count(t0.id) cnt2
from
(
    select *, count(id) over(partition by user_id) cnt1
    from order_info
    where date > '2025-10-15' AND product_name in ('C++','Java','Python') AND status = 'completed'
) t0 left join client c on t0.client_id = c.id
where cnt1 >= 2
group by t0.client_id
order by source

发表于 2025-02-28 22:45:55 回复(0)

Mysql

sjd_

with source_cnt as (
    select c.name,
    count(*)over(partition by o.client_id) client_cnt,
    count(*)over(partition by o.user_id) user_cnt
    from order_info o left join client c
    on o.client_id = c.id
    where product_name in ('C++','Java','Python')
    and status = 'completed'
    and date > '2025-10-15'
)

select distinct ifnull(name,'GroupBuy') as source, client_cnt as cnt
from source_cnt
where user_cnt >= 2
order by source

发表于 2024-11-10 10:47:07 回复(0)

kevinz_

select ifnull(c.name,'GroupBuy') source
,count(*) cnt
from
(
    select id
    ,is_group_buy
    ,client_id
    from
    (
        select user_id
        ,client_id
        ,id
        ,is_group_buy
        ,count(*)over(partition by user_id) ct
        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')
    ) rt1
    where rt1.ct>=2
    order by id
) rt2 left join client c on rt2.client_id=c.id
group by 1
order by source

发表于 2024-05-18 18:32:59 回复(0)