排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-04-08 登录天数分布  
select
	SUM(day15) AS days_1_to_5,
SUM(day610) AS days_6_to_10,
SUM(day1120) AS days_11_to_20, 
SUM(day20) AS days_over_20
from
 (select
usr_id,
(case when count(1) <=5 then 1 else 0 end) day15,
(case when count(1) <=10 and count(1) >= 6 then 1 else 0 end) day610,
(case when count(1) <=20 and count(1) >= 11 then 1 else 0 end) day1120,
(case when count(1) > 20 then 1 else 0 end) day20
from
(select
distinct usr_id,
date_format(login_time,'%Y-%m-%d') login_time
from user_login_log
where login_time >= DATE_SUB(CURDATE(), INTERVAL 180 DAY)) y1
group by usr_id) y2
2026-04-08 登录天数分布  
select
	SUM(day15) AS days_1_to_5,
SUM(day610) AS days_6_to_10,
SUM(day1120) AS days_11_to_20, 
SUM(day20) AS days_over_20
from
 (select
usr_id,
(case when count(1) <=5 then 1 else 0 end) day15,
(case when count(1) <=10 and count(1) >= 6 then 1 else 0 end) day610,
(case when count(1) <=20 and count(1) >= 11 then 1 else 0 end) day1120,
(case when count(1) > 20 then 1 else 0 end) day20
from
(select
distinct usr_id,
date_format(login_time,'%Y-%m-%d') login_time
from user_login_log
where login_time >= DATE_SUB((SELECT MAX(login_time) FROM user_login_log), INTERVAL 180 DAY)) y1
group by usr_id) y2
2026-04-08 登录天数分布  
select
	sum((case when day15 = 1 then 1 else 0 end)) days_1_to_5,
sum((case when day610 = 1 then 1 else 0 end)) days_6_to_10,
sum((case when day1120 = 1 then 1 else 0 end)) days_11_to_20, 
sum((case when day20 = 1 then 1 else 0 end)) days_over_20
from
 (select
usr_id,
(case when count(1) <=5 then 1 else 0 end) day15,
(case when count(1) <=10 and count(1) >= 6 then 1 else 0 end) day610,
(case when count(1) <=20 and count(1) >= 11 then 1 else 0 end) day1120,
(case when count(1) > 20 then 1 else 0 end) day20
from
(select
distinct usr_id,
date_format(login_time,'%Y-%m-%d') login_time
from user_login_log) y1
group by usr_id) y2
2026-04-08 一线城市历年平均气温  
select
	year(dt) Y,
cast(avg(case when city = 'beijing' then tmp_h else null end) as decimal(4,2)) 北京,
cast(avg(case when city = 'shanghai' then tmp_h else null end) as decimal(4,2)) 上海,
cast(avg(case when city = 'shenzhen' then tmp_h else null end) as decimal(4,2)) 深圳,
cast(avg(case when city = 'guangzhou' then tmp_h else null end) as decimal(4,2)) 广州
from
	weather_rcd_china
where
	year(dt) between 2011 and 2022
group by 
	year(dt)
2026-04-08 一线城市历年平均气温  
select
	year(dt) Y,
round(avg(case when city = 'beijing' then tmp_h else null end),2) 北京,
round(avg(case when city = 'shanghai' then tmp_h else null end),2) 上海,
round(avg(case when city = 'shenzhen' then tmp_h else null end),2) 深圳,
round(avg(case when city = 'guangzhou' then tmp_h else null end),2) 广州
from
	weather_rcd_china
where
	year(dt) between 2011 and 2022
group by 
	year(dt)
2025-03-21 城市平均最高气温  
select
	city,
	cast(avg(tmp_h) as decimal(4,2)) avg_tmp_h
from
	weather_rcd_china
where
	year(dt)=2021
group by
	city
order by avg_tmp_h desc;
2025-03-21 城市平均最高气温  
select
	city,
	round(avg(tmp_h),2) avg_tmp_h
from
	weather_rcd_china
where
	year(dt)=2021
group by
	city
order by avg_tmp_h desc;
2025-03-21 抖音面试真题（1）T+1日留存率  
SELECT
a.login_date,
CONCAT(ROUND(COUNT(DISTINCT b.usr_id) * 100.0 / COUNT(DISTINCT a.usr_id), 2), '%') AS T1_retention_rate
FROM
(SELECT DISTINCT
usr_id,
DATE(login_time) AS login_date
 FROM
user_login_log
 WHERE
login_time >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
) a
LEFT JOIN
(SELECT DISTINCT
usr_id,
DATE(login_time) AS sub_date
 FROM
user_login_log
 WHERE
login_time >= DATE_SUB(CURRENT_DATE, INTERVAL 31 DAY)
) b
ON
a.usr_id = b.usr_id
AND b.sub_date = DATE_ADD(a.login_date, INTERVAL 1 DAY)
GROUP BY
a.login_date
ORDER BY
a.login_date;
2025-03-21 抖音面试真题（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 first_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;
2025-03-21 销售金额前10的商品信息  
select
	goods_id,
sum(order_gmv) total_gmv
from
	order_info
where
	date(order_time)='2024-9-10'
group by
	goods_id
order by
	total_gmv desc
limit 10;
2025-03-21 销售金额前10的商品信息  
select
	* 
from
	order_info
where
	date(order_time)='2024-9-10'
order by
	order_gmv desc
limit 10;
2025-03-21 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
select
	a.mch_nmasshole_tried,
a.trx_cnt,
b.mch_nm darling_tried
from
	(
select
	mch_nm,
	count(trx_amt) trx_cnt
from
	cmb_usr_trx_rcd
where
	usr_id=5201314520
and
year(trx_time) in (2023,2024)
group by mch_nm
having count(trx_amt)>=20
) a
left join
	(
select
distinct mch_nm
from
cmb_usr_trx_rcd
where
usr_id=5211314521
and
year(trx_time) in (2023,2024)
) b
on
	a.mch_nm=b.mch_nm
order by
	trx_cnt desc;
2025-03-19 表连接（1）你们难道都去过？那就试试用InnerJoin  
select
	distinct a.*
from
(select
	mch_nm
from
	cmb_usr_trx_rcd
where
	usr_id = 5201314520
and
	year(trx_time)=2024) a
inner join
(select
	mch_nm
from
	cmb_usr_trx_rcd
where
	usr_id = 5211314521
and
	year(trx_time)=2024) b
on
	a.mch_nm=b.mch_nm
order by
	1 desc;
2025-03-19 表连接（1）你们难道都去过？那就试试用InnerJoin  
select
	distinct a.mch_nm
from
	cmb_usr_trx_rcd a
join
cmb_usr_trx_rcd b
on
	a.mch_nm=b.mch_nm
where
	a.usr_id = 5201314520
and 
	b.usr_id = 5211314521
and
	year(a.trx_time)=2024
order by
	mch_nm desc;
2025-03-19 表连接（1）你们难道都去过？那就试试用InnerJoin  
select
	distinct a.mch_nm
from
	cmb_usr_trx_rcd a
join
cmb_usr_trx_rcd b
on
	a.mch_nm=b.mch_nm
where
	a.usr_id = 5201314520
and 
	b.usr_id = 5211314521
order by
	mch_nm desc;
2025-03-19 子查询（1）玩的最嗨那天在做甚？要用Where子查询  
select 
	*
from
	cmb_usr_trx_rcd
where
	usr_id=5201314520
and
	year(trx_time)=2024
order by
	trx_amt desc
limit 1;
2025-03-19 分组与聚合函数（5）五花八门的项目，其实都有固定套路（2）  
select
	trx_amt,
count(trx_amt) total_trx_cnt,
count(distinct usr_id) unique_usr_cnt,
count(trx_amt)/count(distinct usr_id) avg_trx_per_user
from 
	cmb_usr_trx_rcd
where
	mch_nm = '红玫瑰按摩保健休闲'
and
	date(trx_time) between '2023-1-1' and '2024-6-30'
group by
	trx_amt
order by
	avg_trx_per_user desc
limit 5;
2025-03-19 分组与聚合函数（3）五花八门的项目，其实都有固定套路（1）  
select
	trx_amt,
count(*) trx_cnt
from 
	cmb_usr_trx_rcd
where
	mch_nm = '红玫瑰按摩保健休闲'
and
	date(trx_time) between '2024-1-1' and '2024-7-31'
 group by
 	trx_amt
order by trx_cnt desc
limit 5;
2025-03-19 分组与聚合函数（2）擦边营收怎么样，聚合函数可看出  
select
	date(trx_time) trx_date,
max(trx_amt) max_trx_amt,
min(trx_amt) min_trx_amt,
avg(trx_amt) avg_trx_amt,
sum(trx_amt) total_trx_amt
from 
	cmb_usr_trx_rcd
where
	date(trx_time) between '2024-9-1' and '2024-9-30'
and
	mch_nm = '红玫瑰按摩保健休闲'
group by
	date(trx_time),mch_nm
order by
	date(trx_time);
2025-03-19 分组与聚合函数（1）Money全都花在哪，GroupBy来查一查  
select 
	mch_nm,
sum(trx_amt) sum_trx_amt
from
	cmb_usr_trx_rcd
where
	usr_id=5201314520
and
	year(trx_time)=2024
group by
	mch_nm;