排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
没有收藏的题目。

评论笔记

评论日期 题目名称 评论内容 站长评论
没有评论过的题目。

提交记录

提交日期 题目名称 提交代码
2026-03-23 特定歌曲的播放记录  
select 
	* 
from
	song_info as info
join
	listen_rcd as rcd
on
	info.song_id = rcd.song_id
where
	info.song_id = 13
and
	date(rcd.start_time) >= '2023-12-10'
and
	date(rcd.start_time) <= '2023-12-31'
order by
	rcd.start_time;
2026-03-23 特定歌曲的播放记录  
select 
	* 
from
	song_info as info
join
	listen_rcd as rcd
on
	info.song_id = rcd.song_id
where
	info.song_id = 13
and
	date(rcd.start_time) >= '2023-12-10'
and
	date(rcd.start_time) <= '2023-12-31'
2026-03-23 用户听歌习惯的时间分布  
select
	rcd.user_id
,dayname(rcd.start_time) as day_of_week
,count(info.song_id) aslistens_per_day
from
	listen_rcd as rcd
join
	song_info as info
on
	rcd.song_id = info.song_id
group by
	1,2
order by
	rcd.user_id
2026-03-22 S1年级物理成绩前10名（1）  
WITH ranked_scores AS (
    SELECT 
        s.student_id, 
        s.name, 
        sc.score,
        ROW_NUMBER() OVER (PARTITION BY s.grade_code ORDER BY sc.score DESC) AS rnk
    FROM 
        students s
    JOIN 
        scores sc ON s.student_id = sc.student_id
    WHERE 
        s.grade_code = 'S1' 
        AND sc.subject = '物理'
)
SELECT 
    student_id, 
    name, 
    score, 
    rnk
FROM 
    ranked_scores
WHERE 
    rnk <= 10
ORDER BY 
    rnk,student_id
2026-03-22 S1年级物理成绩前10名（1）  
select
	st.student_id as 学号
,st.name as 姓名
,sc.score as 分数
,row_number()over(order by sc.score desc) as 排名
from
	students as st
join
	scores as sc
on
	st.student_id = sc.student_id
where
	st.grade_code = 'S1'
and
	sc.subject = '物理' 
limit
	10
2026-03-22 S1年级物理成绩前10名（1）  
select
	st.student_id as 学号
,st.name as 姓名
,sc.score as 分数
,row_number()over(order by sc.score desc) as 排名
from
	students as st
join
	scores as sc
on
	st.student_id = sc.student_id
where
	st.grade_code = 'S1'
and
	sc.subject = '物理'
2026-03-22 曝光量最大的商品  
select
	rcd.prd_id as 产品ID
,map.prd_nm as 产品名称
,sum(rcd.if_snd) 曝光次数
from
tb_pg_act_rcd as rcd
join
	tb_prd_map as map
on
	rcd.prd_id = map.prd_id
group by
	1,2
order by
	3 desc
limit
	1
2026-03-22 曝光量最大的商品  
select
	rcd.prd_id as 产品ID
,map.prd_nm as 产品名称
,sum(rcd.if_snd) 曝光次数
from
tb_pg_act_rcd as rcd
join
	tb_prd_map as map
on
	rcd.prd_id = map.prd_id
group by
	1,2
order by
	3 desc;
2026-03-15 滴滴面试真题（2）打车订单呼叫应答时间  
select
	sum(timestampdiff(second,call_time,grab_time))/ count(order_id)
from
	didi_order_rcd
where 
	grab_time != '1970-01-01 00:00:00';
2026-03-15 滴滴面试真题（2）打车订单呼叫应答时间  
select
	sum(second(grab_time-call_time))/count(order_id) as avg_response_time_seconds
from
	didi_order_rcd;
2026-03-12 不分类别的最火直播间  
select 
	k2.live_id
,k2.live_nm
,count(live_nm) 进入人次
from 
	ks_live_t1 k1
join
	ks_live_t2 k2
on
	k1.live_id = k2.live_id
where
	DATE_FORMAT(k1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
group by 1,2
order by 3 desc
limit 5
2026-03-12 不分类别的最火直播间  
select 
	k2.live_id
,k2.live_nm
,count(live_nm) enter_cnt
from 
	ks_live_t1 k1
join
	ks_live_t2 k2
on
	k1.live_id = k2.live_id
where
	DATE_FORMAT(k1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
group by 1,2
order by 3 desc
limit 5
2026-03-12 不分类别的最火直播间  
select 
	k2.live_id
,k2.live_nm
,count(live_nm) enter_cnt
from 
	ks_live_t1 k1
join
	ks_live_t2 k2
on
	k1.live_id = k2.live_id
where
	k1.enter_time <='2021-09-12 23'
and
k1.leave_time >='2021-09-12 23'
group by 1,2
order by 3 desc
limit 5
2026-03-12 不分类别的最火直播间  
select 
	k2.live_id
,k2.live_nm
,count(live_nm) 进入人次
from 
	ks_live_t1 k1
join
	ks_live_t2 k2
on
	k1.live_id = k2.live_id
where
	k1.enter_time <='2021-09-12 23:00:00'
and
k1.leave_time >='2021-09-12 23:00:00'
group by 1,2
order by 3 desc
limit 5
2026-03-12 不分类别的最火直播间  
select 
	*
from 
	ks_live_t1
where
	enter_time <='2021-09-12 23:00:00'
and
leave_time >='2021-09-12 23:00:00'
2026-03-11 绘制小时进入人数曲线  
with hours as
(
select
	usr_id
	,hour(enter_time) hour_entered	
from 
	ks_live_t1	
)
select 
	hour_entered
,count(hour_entered) enter_count 
from hours
group by 1
order by 1
2026-03-09 基于共同兴趣爱好的餐厅推荐（1）-我吃过啥  
selectdistinct cust_uid
	 ,mch_nm	
from mt_trx_rcd1
where cust_uid = 'MT10000'
order by mch_nm