排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-03-26 登录天数分布  已解决
2025-03-26 抖音面试真题（1）T+1日留存率  已解决

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-04-11 S1年级物理成绩前10名（1）  
select
	st.student_id as student_id,
st.name,
sc.score,
row_number()over(PARTITION BY st.grade_code order by sc.score desc) as rnk
from
	students as st
left join
	scores as sc
on st.student_id = sc.student_id 
where
	st.grade_code = 'S1' and sc.subject = '物理'
order by
	rnk, st.student_id
limit 10
2025-04-11 S1年级物理成绩前10名（1）  
select
	st.student_id as student_id,
st.name,
sc.score,
row_number()over(order by sc.score desc) as rnk
from
	students as st
left join
	scores as sc
on st.student_id = sc.student_id 
where
	st.grade_code = 'S1' and sc.subject = '物理'
order by
	rnk, st.student_id
limit 10
2025-04-11 S1年级物理成绩前10名（1）  
select
	st.student_id as student_id,
st.name,
sc.score,
row_number()over(order by sc.score desc) as rnk
from
	students as st
left join
	scores as sc
on st.student_id = sc.student_id 
where
	st.grade_code = 'S1' and sc.subject = '物理'
order by
	rnk
limit 10
2025-04-11 S1年级物理成绩前10名（1）  
select
	st.student_id as student_id,
st.name,
sc.score,
row_number()over(order by sc.score desc) as rnk
from
	students as st
left join
	scores as sc
on st.student_id = sc.student_id and sc.subject = '物理'
where
	st.grade_code = 'S1'
order by
	rnk
limit 10
2025-04-11 S1年级物理成绩前10名（1）  
select
	st.student_id as student_id,
st.name,
sc.score,
row_number()over(order by sc.score desc) as rnk
from
	students as st
left join
	scores as sc
on st.student_id = sc.student_id and sc.subject = '物理'
where
	st.grade_code = 'S3'
order by
	rnk
limit 10
2025-04-11 人数最多的学生姓氏  
select
	substring(name, 1, 1) as surname,
count(*) as cnt
from
	students
group by
	surname
order by
	cnt desc
limit 5;
2025-04-11 人数最多的学生姓氏  
select
	substring(name, 1, 1) as surname,
count(*) as cnt
from
	students
group by
	surname
order by
	surname
limit 5;
2025-04-08 多云天气天数  
with data as(
	select distinct
		city,
	sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
		count(dt) as days
	from
		weather_rcd_china
	where
		year(dt)=2021
	group by
		city
)
select distinct
	city,
cloudy_days,
concat(cast(cloudy_days/days*100 as decimal(10,2)),'%') as p
from
	data
order by
	p desc;
2025-04-08 多云天气天数  
with data as(
	select distinct
		city,
	sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
		count(dt) as days
	from
		weather_rcd_china
	group by
		city
)
select distinct
	city,
cloudy_days,
concat(cast(cloudy_days/days*100 as decimal(10,2)),'%') as p
from
	data
order by
	p desc
2025-04-08 多云天气天数  
with data as(
	select distinct
		city,
	sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
		count(dt) as days
	from
		weather_rcd_china
	group by
		city
)
select distinct
	city,
cloudy_days,
concat(round(cloudy_days/days*100,2),'%') as p
from
	data
order by
	p desc
2025-04-08 城市平均最高气温  
with data as(
	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
)
select distinct
	city,
avg_tmp_h
from data
order by avg_tmp_h desc
2025-04-08 城市平均最高气温  
with data as(
	select
		city,
		round(avg(tmp_h),2) as avg_tmp_h
	from
		weather_rcd_china
	where
		year(dt)=2021
group by
		city
)
select distinct
	city,
avg_tmp_h
from data
order by avg_tmp_h desc
2025-04-08 城市平均最高气温  
with data as(
	select
		city,
		round(avg(tmp_h),2) as avg_tmp_h
	from
		weather_rcd_china
	group by
		city
)
select distinct
	city,
avg_tmp_h
from data
order by avg_tmp_h desc
2025-04-08 城市平均最高气温  
with data as(
	select
		city,
		round(avg(tmp_h),2) as avg_tmp_h
	from
		weather_rcd_china
	group by
		city
)
select distinct
	city,
avg_tmp_h
from data
2025-04-08 用户"kjhd30"的第一笔未完成订单  
select
	*
from
	didi_order_rcd
where
	finish_time = '1970-01-01 00:00:00' and cust_uid = 'kjhd30'
order by
	order_id
limit 1
2025-04-08 用户"kjhd30"的第一笔未完成订单  
select
	*
from
	didi_order_rcd
where
	finish_time = '1970-01-01 00:00:00' and cust_uid = 'kjhd30'
2025-04-08 用户"kjhd30"的第一笔未完成订单  
select
	*
from
	didi_order_rcd
where
	finish_time > '1970-01-01 00:00:00' and cust_uid = 'kjhd30'
2025-04-08 用户"kjhd30"的第一笔未完成订单  
select
	*
from
	didi_order_rcd
where
	finish_time != '1970-01-01 00:00:00'
and
cust_uid = 'kjhd30'
order by
	order_id
2025-04-07 抖音面试真题（1）T+1日留存率  
with data1 as(
	select distinct
		usr_id,
		date(login_time) as login_date
	from
		user_login_log
	where
		datediff(date(now()),date(login_time)) <= 30
),
data2 as(
	select
		log1.usr_id as user1,
		log1.login_date as date1,
		log2.usr_id as user2,
		log2.login_date as date2
	from
		data1 as log1
	left join
		data1 as log2
	on 
		log1.usr_id = log2.usr_id
		and 
		datediff(log1.login_date, log2.login_date) = -1
)
select
 	date1 as login_date,
concat(round(avg(user2 is not null)*100,2),'%') as T1_retention_rate
from 
	data2
group by
	login_date
order by
	login_date;
2025-04-07 抖音面试真题（1）T+1日留存率  
with data1 as (
    select distinct 
        usr_id,
        date(login_time) as login_date 
    from 
        user_login_log 
    where 
        datediff(current_date, date(login_time)) <= 30
),
data2 as (
    select 
        T.usr_id, 
        T.login_date as T_date, 
        T_1.login_date as T_1_date 
    from 
        data1 as T 
    left join 
        data1 as T_1 
    on 
        T.usr_id = T_1.usr_id 
        and datediff(T.login_date, T_1.login_date) = -1
)
select 
    T_date as login_date, 
    concat(round(avg(T_1_date is not null)*100, 2), '%') as T1_retention_rate 
from 
    data2 
group by 
    T_date 
order by 
    T_date;