排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-01-08 每年在深交所上市的银行有多少家 
select
year(list_date) Y
,count(ts_code)
from stock_info
where right(ts_code,2) ='SZ' and industry = '银行'
group by Y
2025-01-08 一线城市历年平均气温 
select 
year(dt) Y
,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as beijing
,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as shanghai
,cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as shenzhen
,cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as guangzhou
from weather_rcd_china 
where year(dt) between '2011' and '2022'
group by Y
2025-01-08 一线城市历年平均气温 
select 
year(dt) Y
,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as beijing
,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as shanghai
,cast(avg(case when city='shenzhen' then tmp_h else 0 end) as decimal(4,2)) as shenzhen
,cast(avg(case when city='guangzhou' then tmp_h else 0 end) as decimal(4,2)) as guangzhou
from weather_rcd_china 
where year(dt) between '2011' and '2022'
group by Y
2025-01-08 一线城市历年平均气温 
select 
year(dt) Y
,round(avg(case when city='beijing' then tmp_h else null end),2) beijing
,round(avg(case when city='shanghai' then tmp_h else null end),2) shanghai
,round(avg(case when city='shenzhen' then tmp_h else null end),2)shenzhen
,round(avg(case when city='guangzhou' then tmp_h else null end),2) guangzhou
from weather_rcd_china 
where year(dt) between '2011' and '2022'
group by Y
2025-01-08 一线城市历年平均气温 
select 
year(dt) Y
,round(avg(case when city='beijing' then tmp_h else null end),2) beijing
,round(avg(case when city='shanghai' then tmp_h else null end),2) shanghai
,round(avg(case when city='guangzhou' then tmp_h else null end),2) guangzhou
,round(avg(case when city='shenzhen' then tmp_h else null end),2)shenzhen
from weather_rcd_china 
where year(dt) between '2011' and '2022'
group by Y
2025-01-08 一线城市历年平均气温 
select 
year(dt) Y
,round(avg(case when city='beijing' then tmp_l else null end),2) beijing
,round(avg(case when city='shanghai' then tmp_l else null end),2) shanghai
,round(avg(case when city='guangzhou' then tmp_l else null end),2) guangzhou
,round(avg(case when city='shenzhen' then tmp_l else null end),2)shenzhen
from weather_rcd_china 
where year(dt) between '2011' and '2022'
group by Y
2025-01-08 冬季下雪天数 
select 
city
,sum(case when con like '%雪%' then 1 else 0 end) snowy_days
from weather_rcd_china
where month(dt) in ('12','1','2')
group by city
order by snowy_days desc
2025-01-08 多云天气天数 
select
city
,sum(case when con like '%多云%' then 1 else 0 end) cloudy_days
,concat(round(sum(case when con like '%多云%' then 1 else 0 end)/count(*)*100,2),'%') p
from weather_rcd_china
where year(dt)= '2021'
group by city
order by p desc
2025-01-08 多云天气天数 
select
city
,sum(case when con like '%多云%' then 1 else 0 end) cloudy_days
,concat(round(sum(case when con like '%多云%' then 1 else 0 end)/count(*)*100,2),'%') p
from weather_rcd_china
group by city
order by p desc
2025-01-07 德州扑克起手牌- A花 
SELECT *
FROM hand_permutations
WHERE (LEFT(card1, 1) = 'A' OR LEFT(card2, 1) = 'A') 
AND (RIGHT(card1, 1) IN ('♠', '♣'))
ORDER BY id;
2025-01-07 德州扑克起手牌-最强起手牌KK+ 
SELECT *
FROM hand_permutations
WHERE LEFT(card1, 1) in('A','K') and LEFT(card2, 1) in('A','K')
order by id
2025-01-07 抖音面试真题(1)T+1日留存率 
with data1 as(
select
usr_id
,date(login_time) login_date 
from user_login_log
where date(login_time) between date_sub(curdate(), interval 30 day) and curdate()
),
data2 as(
select
d1.usr_id
,d1.login_date fir_login
,d2.login_date sec_login
from data1 d1
left join data1 d2 on d1.usr_id = d2.usr_id 
and datediff(d1.login_date, d2.login_date) = -1
)
select
fir_login,
concat(round(count(case when sec_login is not null then 1 end) * 100.0 / count(usr_id),2),'%') as retention_rate
from data2
group by fir_login
order by fir_login desc;
2025-01-07 抖音面试真题(1)T+1日留存率 
with data1 as(
select
usr_id
,date(login_time) login_date 
from user_login_log
where date(login_time) between date_sub(curdate(), interval 30 day) and curdate()
),
data2 as(
select
d1.usr_id
,d1.login_date fir_login
,d2.login_date sec_login
from data1 d1
left join data1 d2 on d1.usr_id = d2.usr_id 
and datediff(d1.login_date, d2.login_date) = -1
)
select
fir_login,
count(case when sec_login is not null then 1 end) as retained_users,
count(usr_id) as total_users,
count(case when sec_login is not null then 1 end) * 100.0 / count(usr_id) as retention_rate
from data2
group by fir_login
order by fir_login desc;
2025-01-07 抖音面试真题(1)T+1日留存率 
with data1 as(
select
count(usr_id) fir_usr
,date_add(date(login_time),interval 1 day) fir_login
from user_login_log
where(usr_id,date(login_time)) in 
 (select usr_id,min(date(login_time)) from user_login_log group by usr_id)
group by fir_login
),
data2 as(
select
count(usr_id) sec_usr
,date(login_time) sec_login
from user_login_log
where(usr_id,date(login_time)) in 
 (select usr_id,date_add(min(date(login_time)),interval 1 day) from user_login_log group by usr_id)
group by sec_login
),
data3 as(
select
date(login_time) dayday
from user_login_log
where date(login_time) between date_sub(curdate(), interval 30 day) and curdate()
)
select 
d1.fir_login login_date
,d2.sec_usr/d1.fir_usr
from data3 d3
left join data1 d1 on d3.dayday = d1.fir_login
left join data2 d2 on d3.dayday = d2.sec_login
order by login_date desc
limit 30
2025-01-07 抖音面试真题(1)T+1日留存率 
WITH data1 AS (
SELECT
COUNT(usr_id) AS fir_usr,
date(login_time) AS fir_login
FROM user_login_log
WHERE date(login_time) BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
GROUP BY date(login_time)
),
data2 AS (
SELECT
COUNT(usr_id) AS sec_usr,
date(login_time) AS sec_login
FROM user_login_log
WHERE date(login_time) BETWEEN DATE_SUB(CURDATE(), INTERVAL 29 DAY) AND CURDATE()
GROUP BY date(login_time)
)
SELECT 
d1.fir_login AS login_date,
(d2.sec_usr / d1.fir_usr) * 100 AS retention_rate
FROM data1 d1
LEFT JOIN data2 d2 ON d1.fir_login = DATE_SUB(d2.sec_login, INTERVAL 1 DAY)
ORDER BY login_date DESC
LIMIT 30;
2025-01-07 抖音面试真题(1)T+1日留存率 
with data1 as(
select
count(usr_id) fir_usr
,date_add(date(login_time),interval 1 day) fir_login
from user_login_log
where(usr_id,date(login_time)) in 
 (select usr_id,min(date(login_time)) from user_login_log group by usr_id)
group by fir_login
),
data2 as(
select
count(usr_id) sec_usr
,date(login_time) sec_login
from user_login_log
where(usr_id,date(login_time)) in 
 (select usr_id,date_add(min(date(login_time)),interval 1 day) from user_login_log group by usr_id)
group by sec_login
)
select 
d1.fir_login login_date
,d2.sec_usr/d1.fir_usr
from data1 d1 right join data2 d2 on d1.fir_login = d2.sec_login
order by login_date desc
limit 30
2025-01-07 输出地区为北京的所有银行 
select * 
from stock_info 
where area = '北京' and industry='银行'
order by list_date
2025-01-07 1989年12月13日出生的女歌手 
select 
    * 
from 
    singer_info 
where 
    gender = 'f' and birth_date = '1989-12-13'
2025-01-07 找出所有港台歌手 
select * 
from singer_info 
where type2 ='港台'
order by singer_id
2025-01-07 找出所有港台乐队 
select * 
from singer_info
where type2 = '港台' and type3 = '乐队'
order by singer_id