牛客的课程订单分析(四)_

Sqlite

在写总结的莲雾很积极

with t as
(
select *,
row_number()over(partition by user_id order by date) as rk,
count(id) over (partition by user_id) as cnt
from order_info
where datediff(date,'2025-10-15')>0
and status='completed'
and product_name in('Java','Python','C++')
)
select
user_id,
min((case when t.rk=1 then date end)) as first_buy_date,
cnt
from t
where t.cnt>=2
group by user_id,t.cnt
order by user_id

发表于 2023-01-06 16:15:36 回复(0)

Sqlite

找人就找Aker

在上一道题的基础上再套一层select即可

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

发表于 2022-06-20 13:53:17 回复(0)

Sqlite

djkfjdxc

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

发表于 2022-04-23 11:55:41 回复(0)

Sqlite

牛客518214421号

select user_id,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

发表于 2022-03-08 17:06:20 回复(1)

Sqlite

牛客455366955号

select user_id,min(date),count(user_id)
from order_info
where status <> 'no_completed'
and product_name in ('C++','Java','Python')
and date >='2025-10-15'
group by user_id
having count(user_id)>=2

发表于 2022-03-07 22:33:06 回复(0)

Sqlite

Bryce230

相当于对前一道题的结果做一个聚合。

SELECT a.user_id, min(a.date) AS first_buy_date, b.cnt AS cnt
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
GROUP BY a.user_id, b.cnt
ORDER BY a.user_id ASC;

发表于 2022-02-09 17:46:21 回复(0)

Sqlite

牛客546720870号

select a.user_id,min(a.date) as first_buy_date,max(a.ranking) as cnt
from
(select *,row_number() over(partition by user_id
order by date DESC) as ranking
from order_info
where date > "2025-10-15" and status = "completed"
and product_name in ("C++","Java","Python")) as a
where a.ranking>=2
group by a.user_id
order by a.user_id asc

发表于 2021-12-17 10:37:41 回复(0)

Sqlite

Tina要成为数据分析师呀

把上道题结果的表格作为中间表，按照用户分类，再用min聚合一下日期

select a.user_id,min(a.date) as first_buy_date,a.c as cnt from
    (select *,
    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')) a
where a.c>=2
group by a.user_id
order by a.user_id

发表于 2021-08-18 19:06:16 回复(0)

Sqlite

如果就这样

SELECT user_id,min(date) as first_buy_date,count(client_id) 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

我以为会错的，没想到成功了

发表于 2021-08-18 12:53:49 回复(0)

Sqlite

GeekGuy

Select user_id, MIN(date)as first_buy_date, count(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 having cnt >= 2 
order by user_id

一开始偷懒直接用 cnt>=2放在where后面，但是会出现misuse of aggregate function COUNT()的错误提示，因为count并没有连接到后面group by而导致错误的出现，所以换成having子句去约束就出来了

发表于 2021-08-17 16:34:27 回复(0)

Sqlite

牛客885212806号

select user_id ,min(date) as first_buy_date,count(user_id)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
order by user_id

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

Sqlite

aimspp

！！！group子句可以搭配多个聚合函数一起使用，比如本例中的count和min

根据Date、Status、Product_name筛除一些行
根据user_id分组统计cnt
min(date)筛选最靠前的日期
having子句筛选满足大于2的行
排序

发表于 2021-08-03 18:34:10 回复(0)

user_id	first_buy_date	cnt
57	2025-10-23	2
557336	2025-10-23	3

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

输入

输出

问题信息

热门推荐

通过挑战的用户

相关试题

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-25