排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-01-23 一线城市历年平均气温 
select year(dt) as 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 
    year(dt)
2025-01-23 冬季下雪天数 
select 
	city
,sum(case when con like '%雪%' then 1 else 0 end) as snowy_days
from weather_rcd_china 
where month(dt) in (1,2,12)
group by 1
order by 2 desc
2025-01-23 多云天气天数 
select 
	city
	,sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days
,concat(round(sum(case when con like '%多云%' then 1 else 0 end)/count(1)*100,2),'%') as p
from weather_rcd_china 
where year(dt) = 2021
group by city
order by p desc
2025-01-23 城市平均最高气温 
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-23 城市平均最高气温 
select 
	city
,round(avg(tmp_h),2) as avg_tmp_h
from weather_rcd_china 
where year(dt) = 2021
group by city
order by avg_tmp_h desc
2025-01-23 用户"kjhd30"的第一笔未完成订单 
select 
	* 
from didi_order_rcd 
where cust_uid = 'kjhd30' and finish_time = '1970-01-01 00:00:00'
order by order_id 
limit 1
2025-01-23 不分类别的最火直播间 
select 
	a.live_id
,b.live_nm
,count(1) as enter_cnt
from ks_live_t1 a
left join ks_live_t2 b on a.live_id = b.live_id
where date(enter_time) = '2021-09-12' and hour(enter_time) = 23
group by 1,2
order by 3 desc
limit 5
2025-01-23 绘制小时进入人数曲线 
select 
	lpad(hour(enter_time),2,'0') as hour_entered
,count(1) as enter_count
from ks_live_t1 
group by 1
order by 1
2025-01-23 德州扑克起手牌-同花 
select 
sum(case when right(card1,1) = right(card2,1) then 1 else 0 end)/2 as cnt
,count(1)/2 as ttl_cnt
,cast(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/count(1) AS DECIMAL(4,3)) as p
from hand_permutations
2025-01-23 德州扑克起手牌- 手对 
select * 
from hand_permutations 
where left(card1,1) = left(card2,1)
order by id
2025-01-23 德州扑克起手牌- A花 
select * from hand_permutations 
where (card1 like 'A%' or card2 like 'A%')and right(card1,1) = right(card2,1)
order by id
2025-01-23 德州扑克起手牌- A花 
select * from hand_permutations 
where left(card1,1) like 'A%' or left(card2,1) like 'A%'
order by id
2025-01-23 德州扑克起手牌- A花 
select * from hand_permutations 
where concat(card1,card2) like '%A%'
order by id
2025-01-23 德州扑克起手牌-最强起手牌KK+ 
select *
from hand_permutations
where 
    concat(card1, card2) like '%A%A%' or
    concat(card1, card2) like '%A%K%' or
    concat(card1, card2) like '%K%K%' or
    concat(card1, card2) like '%K%A%'
order by id;
2025-01-23 抖音面试真题(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
)
select 
      T.login_date as login_date 
      ,concat(round(avg(T_1.login_date is not null)*100, 2), '%') as T1_retention_rate 
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
group by login_date
order by login_date
2025-01-23 抖音面试真题(1)T+1日留存率 
select 
	date(login_time) as login_date
,count(login_time) as login_cnt
from user_login_log 
group by 1
order by 1
2025-01-23 销售金额前10的商品信息 
select 
	goods_id
,sum(order_gmv) as total_gmv
from order_info 
where date(order_time) = '2024-09-10' 
group by goods_id
order by total_gmv desc 
limit 10
2025-01-23 销售金额前10的商品信息 
select * 
from order_info 
where date(order_time) = '2024-09-10' 
order by order_gmv desc 
limit 10
2025-01-22 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select 
	a.mch_nm as asshole_tried
,a.trx_cnt
,b.mch_nm as darling_tried 
from 
(select mch_nm, count(1) trx_cnt
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5201314520'
group by mch_nm
having count(1) >=20)a
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5211314521')b
on a.mch_nm = b.mch_nm order by 2 desc
2025-01-22 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
select
	a.mch_nm as asshole_tried
	,a.trx_cnt
,b.mch_nm as darling_tried 
from 
(select
mch_nm 
,count(1) as trx_cnt
from cmb_usr_trx_rcd 
where usr_id = '5201314520' andyear(trx_time) in (2023,2024)
group by 1
having trx_cnt >= 20) a
left join
(select 
distinct mch_nm 
from cmb_usr_trx_rcd 
where usr_id = 'usr_id=5211314521' and year(trx_time) in (2023,2024)) b
on a.mch_nm = b.mch_nm
order by 2 desc