排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-01-10 德州扑克起手牌-最强起手牌KK+ 
select *
from hand_permutations
where concat(card1, card2) like 'A%A%'
or concat(card1, card2) like 'K%K%'or
concat(card1, card2) like 'A%K%'
2025-01-09 被收藏次数最多的商品 
select * from xhs_fav_rcd 
order by fav_trq
2025-01-09 登录天数分布 
with tt as (
select distinct usr_id, date(login_time) as login_date
from user_login_log
where date(login_time) >= date_sub(current_date, interval 180 day))
, bb as (
select usr_id, count(login_date) as days
from tt
group by usr_id)
select 
count(case when days between 1 and 5 then days end) as days_1_to_5,
count(case when days between 6 and 10 then days end) as days_6_to_10,
count(case when days between 11 and 20 then days end) as days_11_to_20,
count(case when days > 20 then days end) as days_over_20
from bb
2025-01-09 通勤、午休、临睡个时间段活跃人数分布 
select 
count(distinct 
case when (date_format(login_time, '%H:%i:%s') between '07:30:00' and '09:30:00') or (date_format(login_time, '%H:%i:%s') between '18:30:00' and '20:30:00') then usr_id end) as commute,
count(distinct 
case when date_format(login_time, '%H:%i:%s') between '11:30:00' and '14:00:00' then usr_id end) as lunch_break,
count(distinct 
case when (date_format(login_time, '%H:%i:%s') between '22:30:00' and '23:59:59') or (date_format(login_time, '%H:%i:%s') between '00:00:00' and '01:00:00') then usr_id end) as bedtime
from user_login_log
where login_time >= date_format(date_sub(current_date, interval 1 month), '%Y-%m-01 00:00:00') and login_time <= date_format(current_date, '%Y-%m-01 00:00:00')
2025-01-08 上月活跃用户数 
select COUNT(DISTINCT usr_id) AS active_users
from user_login_log
where login_time >= date_format(date_sub(current_date, interval 1 month), '%Y-%m-01 00:00:00') and login_time < date_format(current_date, '%Y-%m-01 00:00:00')
2025-01-08 海王发红包 
with tt as (
select snd_usr_id, pkt_amt 
, row_number()over(partition by snd_usr_id order by pkt_amt) as number
from tx_red_pkt_rcd
where pkt_amt IN (200, 520)
order by snd_usr_id,number)
select distinct snd_usr_id
from tt
where number >= 5
order by snd_usr_id
2025-01-08 海王发红包 
with tt as (
select snd_usr_id, pkt_amt 
, row_number()over(partition by snd_usr_id order by pkt_amt) as number
from tx_red_pkt_rcd
where pkt_amt IN (200, 520)
order by snd_usr_id,number)
select snd_usr_id
from tt
where number >= 5
order by snd_usr_id
2025-01-08 海王发红包 
with tt as (
select snd_usr_id, pkt_amt 
, row_number()over(partition by snd_usr_id order by pkt_amt) as number
from tx_red_pkt_rcd
where pkt_amt IN (200, 520)
order by snd_usr_id,number)
select snd_usr_id
from tt
where number >= 5
2025-01-08 海王发红包 
with tt as (
select snd_usr_id, pkt_amt 
, row_number()over(partition by snd_usr_id order by pkt_amt) as number
from tx_red_pkt_rcd
where pkt_amt = 200 or pkt_amt = 520
order by snd_usr_id,number)
select snd_usr_id
from tt
where number >= 5
2025-01-08 用户听歌习惯的时间分布 
with tt as (
select user_id, dayname(start_time) as day_of_week
from listen_rcd
order by user_id, dayname(start_time))
select *, count(*) as listens_per_day
from tt
group by user_id, day_of_week
order by user_id, day_of_week
2025-01-08 绘制小时进入人数曲线 
with tt as (select usr_id, live_id, hour(enter_time) as enterhour
from ks_live_t1)
select 
lpad(enterhour, 2, '0') as hour_entered,
count(enterhour) as enter_count
from tt
group by enterhour
order by enterhour
2025-01-08 绘制小时进入人数曲线 
with tt as (select usr_id, live_id, hour(enter_time) as enterhour
from ks_live_t1)
select enterhour as hour_entered,
count(enterhour) as enter_count
from tt
group by enterhour
order by enterhour
2025-01-08 抖音面试真题(1)T+1日留存率 
with user_next_day as (
select *, 
lead(lgdate)over(partition by usr_id order by lgdate) as nextday
from(
select distinct usr_id, date(login_time) as lgdate
from user_login_log
where datediff (current_date, date(login_time)) <= 30) as tt)
select lgdate as login_date,
concat(round(
count(distinct case when datediff(nextday, lgdate)=1 then usr_id end)/ 
count(distinct usr_id) * 100 , 2), '%') as T1_retention_rate
from user_next_day
group by lgdate
order by lgdate
2025-01-07 条件过滤-符合条件的班主任 
select name, subject, class_code, qualification
from teachers
where (fir_degr = '北京大学' or fir_degr ='清华大学') and head_teacher is not null
order by name
2025-01-07 条件过滤-符合条件的班主任 
select name, subject, class_code, qualification
from teachers
where fir_degr = '北京大学' or fir_degr ='清华大学' and head_teacher is not null
order by name
2025-01-07 条件过滤-符合条件的班主任 
select name, subject, class_code, qualification
from teachers
where fir_degr = '北京大学' or '清华大学' and head_teacher is not null
order by name
2025-01-07 条件过滤-符合条件的班主任 
select name, subject, class_code, qualification
from teachers
where fir_degr = '北京大学' or '清华大学'
order by name
2025-01-07 条件过滤-找出所有教授数学且具有高级职称的教师 
select name, subject, class_code, qualification
from teachers
where qualification = 'Senior' and subject = '数学'
order by name
2025-01-07 条件过滤-查找2009年出生的女学生 
select student_id, name, birth_date
from students 
where birth_date between '2009-01-01' and '2009-12-31' and gender = 'f'
order by birth_date
2025-01-07 数学成绩分段统计(1) 
with tt as (
select score,
case 
	when score >= 110 then '[110, 120]'
when score >= 90 then '[90, 110)'
when score >= 60 then'[60, 90)' 
else '[0, 60)'
end as score_range
from scores 
where subject = '数学' and exam_date = '2024-06-30')
select score_range,
count(score_range) as num_students
from tt
group by score_range
ORDER BY 
    score_range DESC;