排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-05-05 按歌手名字字符长度统计歌手个数  
select length(singer_name),count(singer_id)
from singer_info 
group by length(singer_name)
2026-05-05 用户听歌习惯的时间分布  
select 
	user_id,
dayname(start_time),
count(1)
from listen_rcd 
group by
	user_id,
dayname(start_time)
order by 1, 2
2026-05-05 渣男腰子可真行，端午中秋干不停  
select * 
from cmb_usr_trx_rcd 
where	
	usr_id = '5201314520'
and 
(date(trx_time) between '2024-06-08' and '2024-06-10'
OR date(trx_time) between '2024-09-15' and '2024-09-17')
2026-05-05 通勤、午休、临睡个时间段活跃人数分布  
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 
 	else null 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 else null end)) as lunch_break,
count(distinct case when (DATE_FORMAT(login_time, '%H:%i:%s') between '00:00:00' and '01:00:00' )
or (DATE_FORMAT(login_time, '%H:%i:%s') between '22:30:00' and '23:59:59' ) 
 	then usr_id
 	else null end) as commute
FROM 
    user_login_log
WHERE 
    login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00');
2026-05-05 通勤、午休、临睡个时间段活跃人数分布  
SELECT 
	sum(
	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 1 
 	else null end) as commute,
    sum((case when DATE_FORMAT(login_time, '%H:%i:%s') between '11:30:00' and '14:00:00' then 1 else null end)) as lunch_break,
sum(case when (DATE_FORMAT(login_time, '%H:%i:%s') between '00:00:00' and '01:00:00' )
or (DATE_FORMAT(login_time, '%H:%i:%s') between '22:30:00' and '23:59:59' ) 
 	then 1 
 	else null end) as commute
FROM 
    user_login_log
WHERE 
    login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00');
2026-05-05 上月活跃用户数  
SELECT 
    COUNT(DISTINCT usr_id) AS active_users
FROM 
    user_login_log
WHERE 
    login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00');
2026-05-05 一线城市历年平均气温  
select 
	year(dt) as Y,
cast(avg(casewhen city = 'beijing' then tmp_h else null end) as decimal(4,2)) as 北京
,cast(avg(casewhen city = 'shanghai' then tmp_h else null end) as decimal(4,2)) as 上海
,cast(avg(casewhen city = 'shenzhen' then tmp_h else null end) as decimal(4,2)) as 深圳
,cast(avg(casewhen city = 'guangzhou' then tmp_h else null end) as decimal(4,2)) as 广州
from weather_rcd_china 
group by
	year(dt)
2026-05-05 一线城市历年平均气温  
select 
	year(dt) as Y,
round(avg(casewhen city = 'beijing' then tmp_h else null end),2) as 北京
,round(avg(casewhen city = 'shanghai' then tmp_h else null end),2) as 上海
,round(avg(casewhen city = 'shenzhen' then tmp_h else null end),2) as 深圳
,round(avg(casewhen city = 'guangzhou' then tmp_h else null end),2) as 广州
from weather_rcd_china 
group by
	year(dt)
2026-05-05 冬季下雪天数  
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
	city
order by
	2 desc
2026-05-05 子查询（1）玩的最嗨那天在做甚？要用Where子查询  
select * 
from cmb_usr_trx_rcd 
where 
	usr_id='5201314520'
and trx_amt IN(
	select
			MAX(trx_amt)
	from
			cmb_usr_trx_rcd
where
			usr_id='5201314520'
group by
			usr_id
	)
2026-05-05 子查询（1）玩的最嗨那天在做甚？要用Where子查询  
select * 
from cmb_usr_trx_rcd 
where 
	usr_id='5201314520'
and trx_time like '2024%'
order by
	trx_amt desc
limit	1
2026-05-05 字符串与通配符（1）名称里面有特服，可以使用通配符  
select count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd 
where 
	mch_nm like '%按摩保健休闲%'
;
2026-05-05 分类（1）姿势太多很过分，分类要用CaseWhen  
select 
	(case trx_amt 
 	when 288 then '1.WithHand' 
 	when 388 then '2.WithMimi'
 	when 588 then '3.BlowJobbie'
 	when 888 then '4.Doi'
 	when 1288 then '5.DoubleFly'
 	else '6.other'
	end ) as ser_typ
,count(1) as trx_cnt
,date(MIN(trx_time)) as first_date
from cmb_usr_trx_rcd 
where usr_id = '5201314520'
	and mch_nm = '红玫瑰按摩保健休闲'
group by
	ser_typ
order by
	ser_typ ASC
2026-05-05 分组与聚合函数（6）想知道渣男有多坏，疯狂使用GroupBy  
select usr_id,mch_nm,SUM(trx_amt),count(1) as trx_cnt,MIN(trx_time)
from cmb_usr_trx_rcd 
where	
	usr_id = '5201314520'
and trx_amt > 287
group by
	usr_id,mch_nm
order by
	trx_cnt desc
2026-05-05 分组与聚合函数（6）想知道渣男有多坏，疯狂使用GroupBy  
select usr_id,mch_nm,SUM(trx_amt),count(1) as trx_cnt,MIN(trx_time)
from cmb_usr_trx_rcd 
where	
	usr_id = '5201314520'
and trx_amt > 287
group by
	usr_id,mch_nm
order by
	trx_cnt desc
limit 5;
2026-05-05 分组与聚合函数（6）想知道渣男有多坏，疯狂使用GroupBy  
select usr_id,mch_nm,SUM(trx_amt),count(1) as trx_cnt,MIN(trx_time)
from cmb_usr_trx_rcd 
where	
	usr_id = '5201314520'
and trx_amt > 288
group by
	usr_id,mch_nm
order by
	trx_cnt desc
limit 5;
2026-05-05 分组与聚合函数（5）想知道何时成瘾，用Max Or Min？  
select usr_id,min(trx_time) ,MAX(mch_nm)
from cmb_usr_trx_rcd 
where mch_nm = '红玫瑰按摩保健休闲'
	and usr_id = '5201314520'
group by
	usr_id
limit 5;
2026-05-05 分组与聚合函数（5）五花八门的项目，其实都有固定套路（2）  
select 
	trx_amt
,count(1) as total_trx_cnt
,count(distinct usr_id) as unique_usr_cnt
,count(1)/count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd 
where
	mch_nm = '红玫瑰按摩保健休闲'
and date(trx_time) between '2023-01-01' and '2024-06-30'
group by
	trx_amt
order by
	avg_trx_per_user desc
limit 5;
2026-05-05 分组与聚合函数（3）五花八门的项目，其实都有固定套路（1）  
select trx_amt,count(usr_id) as trx_cnt
from cmb_usr_trx_rcd 
where mch_nm like '红玫瑰按摩保健休闲' 
	and date(trx_time) between '2024-01-01' and '2024-07-31'
group by
	trx_amt
order by	
	trx_cnt DESC
limit 5
2026-05-05 分组与聚合函数（2）擦边营收怎么样，聚合函数可看出  
select date(trx_time),max(trx_amt),min(trx_amt),avg(trx_amt),sum(trx_amt) 
from cmb_usr_trx_rcd 
where mch_nm = '红玫瑰按摩保健休闲'
	and date(trx_time) like '2024-09%'
group by date(trx_time)
order by date(trx_time)