2020-03-20 FT 笔试

今天考了一场笔试 一共100分钟
30道选择
2道SQL

选择考了: ML, 概率, 排列组合, 数据结构(完全不会), 老是考我编程的复杂程度, 行测

SQL 明明很简单, 却用了一个小时才勉强AC
我觉得原因如下:

1. 窗口函数掌握不够全面(应该倾向于用窗口函数解题, 简化code)
2. 小错误不断, 应该换一个本地编辑器, 可以自动查询错误的那种. 安装MYSQL在本地跑数!! ---- 0321 已安装, 要创建一个专门用来跑笔试代码的database的文件
3. 在sublime里面编辑时, 要先将建表代码复制在上面, 用联想写字段/表名, 减少手打错误
4. 不要因为想节约时间而忽略格式
5. 可以适当牛客刷题, 熟悉环境

题目: 牛客的课程订单分析(三); 牛客的课程订单分析(五)

一直尝试一直报错TAT, 艰辛…

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



select t0.*
from order_info as t0 

left join 
	(select user_id, count(id) as num
	from order_info
	where date > '2025-10-15' 
	 and status = 'completed' 
	 and  (product_name  =  'C++' or product_name = 'Python'
	or product_name = 'Java')

	group by user_id) as t1 
	on t0.user_id = t1.user_id

where num >= 2
order by id 

-----------
row_number()over(partition by user_id order by date) as rk


select user_id, first_buy_date, second_buy_date, cnt
from (select user_id, date as first_buy_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  =  'C++' or product_name = 'Python'
	or product_name = 'Java')
) t0 left join (select user_id, date as second_buy_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  =  'C++' or product_name = 'Python'
	or product_name = 'Java')
) t1 on  t0.user_id = t1.user_id
(select user_id, count(distinct id) as cnt
	,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  =  'C++' or product_name = 'Python'
	or product_name = 'Java')
	 group by user_id
) t2 on t0.user_id = t2.user_id
where rk >= 2 and t0.rk + 1 = t1.rk

----
select user_id
, max(if(date_rk = 1, date, null)) as first_buy_date
, max(if(date_rk=2, date,null)) as second_buy_date
, max(cnt) as cnt
from (
select user_id, date
, rank()over(partition by user_id order by date) date_rk
, count(1) over(partition by user_id) cnt
from order_info
where date > '2025-10-15' 
and product_name in ('C++', 'Python','Java') 
and status = 'completed') t1
where cnt >=2
group by user_id
order by user_id



----


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

----
with t as (select user_id 
		from order_info 
		where date > '2025-10-15' 
	and product_name in ('C++', 'Python','Java') 
	and status = 'completed'
	group by user_id
	having count(id) >= 2

)
select * 
from order_info 
where date > '2025-10-15' 
	and product_name in ('C++', 'Python','Java') 
	and status = 'completed'
and user_id in  (select * from t1)
order by id

最后AC

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

-----

select user_id
, max(if(date_rk = 1, date, null)) as first_buy_date
, max(if(date_rk=2, date,null)) as second_buy_date
, max(cnt) as cnt
from (
select user_id, date
, rank()over(partition by user_id order by date) date_rk
, count(1) over(partition by user_id) cnt
from order_info
where date > '2025-10-15' 
and product_name in ('C++', 'Python','Java') 
and status = 'completed') t1
where cnt >=2
group by user_id
order by user_id
----


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