排名

用户解题统计

过去一年提交了

勋章 ①金银铜:在竞赛中获得第一二三名;②好习惯:自然月10天提交;③里程碑:解决1/2/5/10/20/50/100/200题;④每周打卡挑战:完成每周5题,每年1月1日清零。

收藏

收藏日期 题目名称 解决状态
2025-01-01 10月1日后再也没活跃过的用户  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-01-10 快手面试真题(3)同时在线人数峰值时点 
改了一下,还是提交不对,麻烦看看了
with b as (/*对数据打标签*/
	select usr_id,live_id,enter_time as event_time,1 as flag
  	from ks_live_t1
  	union all
  	select usr_id,live_id,leave_time as event_time,-1 as flag
  	from ks_live_t1
),
b1 as (/*窗口函数累积求和得到每个直播间每个时刻的在线人数*/
	select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
  	from b
),
b2 as (/*利用窗口函数得到在线人数峰值*/
	select live_id,event_time,online_users,max(online_users)over(partition by live_id) as  max_online_users
  	from b1
),
b3 as (/*筛选峰值时点,GROUP BY之后使用max min函数得到第一次和最后一次峰值*/
	select live_id,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
  	from b2
  	WHERE online_users = max_online_users
  	group by live_id,max_online_users
),
b4 as (/*连接得直播间名字*/
	select b3.live_id,t2.live_nm,max_online_users,first_peak_time,last_peak_time
  	from b3
  	left join ks_live_t2 t2 on b3.live_id = t2.live_id
  	order by max_online_users desc
) 
select * from b4;
你的答案是对的,没毛病,👍
2025-01-10 快手面试真题(3)同时在线人数峰值时点 
输出示例没放对
with b as (/*对数据打标签*/
	select usr_id,live_id,enter_time as event_time,1 as flag
  	from ks_live_t1
  	union all
  	select usr_id,live_id,leave_time as event_time,-1 as flag
  	from ks_live_t1
),
b1 as (/*窗口函数累积求和得到每个直播间每个时刻的在线人数*/
	select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
  	from b
),
b2 as (/*利用窗口函数得到在线人数峰值及其时点*/
	select live_id,event_time,max(online_users)over(partition by live_id) as  max_online_users
  	from b1
),
b3 as (/*GROUP BY之后使用max min函数得到第一次和最后一次峰值*/
	select b2.live_id,t2.live_nm,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
  	from b2
  	left join ks_live_t2 t2 on b2.live_id = t2.live_id
  	group by live_id,live_nm,max_online_users
  	order by max_online_users desc
)
select * from b3;
运行没问题,但就是提交判定不正确,不知道是哪里出问题了
啥也没说
2025-01-09 分类别人均在线时长最火直播间 
好的,还有个问题是如果进入时间小于2021-9-13 3:59:59,但离开时间大于2021-9-13 3:59:59 或者进入时间小于2021-9-12 23:00:00,但离开时间大于2021-9-12 23:00:00,这两种情况下都是有部分的在线时长在2021-9-12 23:00:00和2021-9-13 3:59:59之间,部分不在,为什么只用where enter_time >= '2021-9-12 23:00:00' and enter_time<='2021-9-13 3:59:59'条件就可以筛选出来呢
妙啊!恭喜你发现了华点。所以应该怎么改呢?
2025-01-09 滴滴面试真题(2)打车订单呼叫应答时间 
select sum(timestampdiff(second, call_time, grab_time))/count(1) AS avg_response_time_seconds
from didi_order_rcd
where cancel_time = '1970-01-01 00:00:00'
这样跑出来的答案还是错的是为啥
计算平均应答时间,使用应答时间不为非法值、还是取消时间不为非法值呢,哪个更准确?
2025-01-07 快手面试真题(1)同时在线人数 
为什么筛选条件变成enter_time<='2021-9-12 11:48:38' and leave_time>='2021-9-12 11:48:38'就得出来的结果不对呢?
你知道什么叫晚上11点吗?
2025-01-07 分类别人均在线时长最火直播间 
with t as(
	select t1.live_id,t2.live_nm,t2.live_type,
  	sum(timestampdiff(second,t1.enter_time,t1.leave_time)) as total_duration,
  	count(distinct t1.usr_id) as total_users
  	from ks_live_t1 t1
  	left join ks_live_t2 t2 on t1.live_id = t2.live_id
  	where enter_time >= '2021-9-12 23:00:00' and enter_time<='2021-9-13 3:59:59'
  	group by live_id,live_nm,live_type
),
t1 as (
	select live_id,live_nm,live_type,total_duration,total_users,total_duration/total_users as avg_duration
  	from t
),
t2 as (
	select 	live_id,live_nm,live_type,total_duration,total_users,avg_duration,row_number()over(partition by live_type order by avg_duration) as rnk
  	from t1
)

select live_id,live_nm,live_type,total_duration,total_users,avg_duration
from t2
where rnk=1
order by live_id
实在不知道错哪了,就是得不出正确答案
row_number 后面的排序改成desc就行了啊
2025-01-04 德州扑克起手牌-同花两高张 
不懂为什么left(card1,2)=10也要包括在高牌里
正确答案已去除10

提交记录

提交日期 题目名称 提交代码
2025-01-16 冬季下雪天数 
select city,
	 sum(if(con like '%雪%',1,0))snowy_days
from weather_rcd_china
where month(dt) in (12,1,2)
group by city
order by snowy_days desc
2025-01-16 多云天气天数 
select city,
	 sum(if(con like '%多云%',1,0)) as cloudy_days,
 concat(round(sum(case when con like '%多云%' then 1 else 0 end)*100/count(1),2),'%')  as p
from weather_rcd_china
where year(dt) = '2021'
group by city
order by p desc
2025-01-16 城市平均最高气温 
select city , cast(avg(tmp_h) as decimal(4,2)) as avg_tmp_h
from weather_rcd_china
where year(dt) = '2021'
group by city
order by avg_tmp_h desc
2025-01-16 城市平均最高气温 
select city , round(sum(tmp_h)/count(1),2) as avg_tmp_h
from weather_rcd_china
where year(dt) = '2021'
group by city
order by avg_tmp_h desc
2025-01-10 快手面试真题(3)同时在线人数峰值时点 
with b as (
	select usr_id,live_id,enter_time as event_time,1 as flag
	from ks_live_t1
	union all
	select usr_id,live_id,leave_time as event_time,-1 as flag
	from ks_live_t1
),
b1 as (
	select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
	from b
),
b2 as (
	select live_id,event_time,online_users,max(online_users)over(partition by live_id) asmax_online_users
	from b1
),
b3 as (
	select live_id,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
	from b2
	WHERE online_users = max_online_users
	group by live_id,max_online_users
),
b4 as (
	select b3.live_id,t2.live_nm,max_online_users,first_peak_time,last_peak_time
	from b3
	left join ks_live_t2 t2 on b3.live_id = t2.live_id
	order by max_online_users desc,live_id desc
) 
select * from b4;
2025-01-10 快手面试真题(3)同时在线人数峰值时点 
with b as (
	select usr_id,live_id,enter_time as event_time,1 as flag
	from ks_live_t1
	union all
	select usr_id,live_id,leave_time as event_time,-1 as flag
	from ks_live_t1
),
b1 as (
	select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
	from b
),
b2 as (
	select live_id,event_time,online_users,max(online_users)over(partition by live_id) asmax_online_users
	from b1
),
b3 as (
	select live_id,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
	from b2
	WHERE online_users = max_online_users
	group by live_id,max_online_users
),
b4 as (
	select b3.live_id,t2.live_nm,max_online_users,first_peak_time,last_peak_time
	from b3
	left join ks_live_t2 t2 on b3.live_id = t2.live_id
	order by max_online_users desc,live_id
) 
select * from b4;
2025-01-10 快手面试真题(3)同时在线人数峰值时点 
with b as (
	select usr_id,live_id,enter_time as event_time,1 as flag
	from ks_live_t1
	union all
	select usr_id,live_id,leave_time as event_time,-1 as flag
	from ks_live_t1
),
b1 as (
	select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
	from b
),
b2 as (
	select live_id,event_time,online_users,max(online_users)over(partition by live_id) asmax_online_users
	from b1
),
b3 as (
	select live_id,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
	from b2
	WHERE online_users = max_online_users
	group by live_id,max_online_users
),
b4 as (
	select b3.live_id,t2.live_nm,max_online_users,first_peak_time,last_peak_time
	from b3
	left join ks_live_t2 t2 on b3.live_id = t2.live_id
	order by max_online_users desc
) 
select * from b4;
2025-01-10 快手面试真题(3)同时在线人数峰值时点 
with b as (
	select usr_id,live_id,enter_time as event_time,1 as flag
	from ks_live_t1
	union all
	select usr_id,live_id,leave_time as event_time,-1 as flag
	from ks_live_t1
),
b1 as (
	select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
	from b
),
b2 as (
	select live_id,event_time,online_users,max(online_users)over(partition by live_id) asmax_online_users
	from b1
),
b3 as (
	select b2.live_id,t2.live_nm,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
	from b2
	left join ks_live_t2 t2 on b2.live_id = t2.live_id
	WHERE online_users = max_online_users
	group by live_id,live_nm,max_online_users
	order by max_online_users desc
)
select * from b3;
2025-01-10 快手面试真题(3)同时在线人数峰值时点 
with b as (
	select usr_id,live_id,enter_time as event_time,1 as flag
	from ks_live_t1
	union all
	select usr_id,live_id,leave_time as event_time,-1 as flag
	from ks_live_t1
),
b1 as (
	select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
	from b
),
b2 as (
	select live_id,event_time,max(online_users)over(partition by live_id) asmax_online_users
	from b1
),
b3 as (
	select b2.live_id,t2.live_nm,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
	from b2
	left join ks_live_t2 t2 on b2.live_id = t2.live_id
	group by live_id,live_nm,max_online_users
	order by max_online_users desc,live_id desc
)
select * from b3;
2025-01-10 快手面试真题(3)同时在线人数峰值时点 
with b as (
	select usr_id,live_id,enter_time as event_time,1 as flag
	from ks_live_t1
	union all
	select usr_id,live_id,leave_time as event_time,-1 as flag
	from ks_live_t1
),
b1 as (
	select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
	from b
),
b2 as (
	select live_id,event_time,max(online_users)over(partition by live_id) asmax_online_users
	from b1
),
b3 as (
	select b2.live_id,t2.live_nm,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
	from b2
	left join ks_live_t2 t2 on b2.live_id = t2.live_id
	group by live_id,live_nm,max_online_users
	order by live_id
)
select * from b3;
2025-01-10 快手面试真题(3)同时在线人数峰值时点 
with b as (
	select usr_id,live_id,enter_time as event_time,1 as flag
	from ks_live_t1
	union all
	select usr_id,live_id,leave_time as event_time,-1 as flag
	from ks_live_t1
),
b1 as (
	select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
	from b
),
b2 as (
	select live_id,event_time,max(online_users)over(partition by live_id) asmax_online_users
	from b1
),
b3 as (
	select b2.live_id,t2.live_nm,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
	from b2
	left join ks_live_t2 t2 on b2.live_id = t2.live_id
	group by live_id,live_nm,max_online_users
	order by max_online_users desc
)
select * from b3;
2025-01-09 快手面试真题(2)同时在线人数峰值 
with t as (
	select live_id,enter_time as event_time ,1 as flag
	from ks_live_t1
	union all
	select live_id,leave_time as event_time ,-1 as flag
	from ks_live_t1
),
t1 as (
	select live_id , sum(flag)over(partition by live_id order by event_time) as online_users
	from t
),
t2 as (
	select t1.live_id ,live_nm,max(online_users) as max_online_users
	from t1
	left join ks_live_t2 a on t1.live_id = a.live_id
	group by live_id,live_nm
 	order by max_online_users desc
)
select * from t2
2025-01-09 快手面试真题(2)同时在线人数峰值 
with t as (
	select live_id,enter_time ,1 as flag
	from ks_live_t1
	union all
	select live_id,leave_time,-1 as flag
	from ks_live_t1
),
t1 as (
	select live_id , sum(flag)over(partition by live_id order by enter_time) as online_users
	from t
	order by online_users desc
),
t2 as (
	select t1.live_id ,live_nm,max(online_users) as max_online_users
	from t1
	left join ks_live_t2 a on t1.live_id = a.live_id
	group by live_id,live_nm
 	order by max_online_users desc
)
select * from t2
2025-01-09 快手面试真题(2)同时在线人数峰值 
with t as (
	select live_id,enter_time ,1 as flag
	from ks_live_t1
	union all
	select live_id,leave_time,-1 as flag
	from ks_live_t1
),
t1 as (
	select live_id , sum(flag)over(partition by live_id order by enter_time) as online_users
	from t
	order by online_users desc
),
t2 as (
	select t1.live_id ,live_nm,max(online_users) as max_online_users
	from t1
	left join ks_live_t2 a on t1.live_id = a.live_id
	group by live_id,live_nm
)
select * from t2
2025-01-09 滴滴面试真题(3)UTC转化后的本地时间呼叫高峰期 
select hour(local_call_time) as local_hour ,count(1)cnt
from (
	select order_id,
			cust_uid,
			date_add(call_time,interval -3 hour) as local_call_time,
			grab_time,
			cancel_time,
			finish_time
from didi_order_rcd
) as t
group by hour(local_call_time)
order by cnt desc
2025-01-09 滴滴面试真题(3)UTC转化后的本地时间呼叫高峰期 
select hour(local_call_time) as local_hour ,count(1)cnt
from (
	select order_id,
			cust_uid,
			date_add(call_time,interval -4 hour) as local_call_time,
			grab_time,
			cancel_time,
			finish_time
from didi_order_rcd
) as t
group by hour(local_call_time)
order by cnt desc
2025-01-09 滴滴面试真题(3)UTC转化后的本地时间呼叫高峰期 
select hour(call_time)-3 as local_hour ,count(1)cnt
from didi_order_rcd
group by hour(call_time)-3
order by cnt desc
2025-01-09 用户"kjhd30"的第一笔未完成订单 
select order_id, cust_uid ,call_time ,grab_time ,cancel_time , finish_time
from didi_order_rcd
where cust_uid = 'kjhd30' and cancel_time != '1970-01-01 00:00:00'
order by order_id
limit 1
2025-01-09 大于J小于K的手牌 
select id,card1 ,card2
from hand_permutations
where card1 > 'J' and card1 < 'K' and card2 > 'J' and card2 < 'K'
2025-01-09 语文数学英语至少1门超过100分的同学 
select student_id,chinese,math,english
from subject_score
where chinese > '100' or math > '100' or english > '100'
order by chinese