排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-12-18 给商品打四类标签（列）  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-12-19 哔哩哔哩面试真题（1）按日分摊会员收入  
select
round(sum(case when '2020-11-20' between begin_date and end_date then pay_amount/(datediff(end_date,begin_date)) else 0 end),2) as total_daily_income
from bilibili_m1
 
啥也没说

提交记录

提交日期 题目名称 提交代码
2026-02-09 连续登录3天及以上  
with a as(
select
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(current_date(),login_time)<=30
group by usr_id, date_format(login_time,'%Y-%m-%d')),
b as(
select
usr_id,
login_time,
date_sub(login_time,interval row_number()over(partition by usr_id order by login_time) day) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having consecutive_days >=3
order by usr_id,
start_date
2026-02-09 连续登录3天及以上  
with a as(
select
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(current_date(),login_time)<=29
group by usr_id,login_time),
b as(
select
usr_id,
login_time,
date_sub(login_time,interval row_number()over(partition by usr_id order by login_time) day) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having consecutive_days >=3
order by usr_id,
start_date
2026-02-09 连续登录3天及以上  
with a as(
select
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(current_date(),login_time)<=30
group by usr_id,login_time),
b as(
select
usr_id,
login_time,
date_sub(login_time,interval row_number()over(partition by usr_id order by login_time) day) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having consecutive_days >=3
order by usr_id desc,
start_date
2026-02-09 连续登录3天及以上  
with a as(
select
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(current_date(),login_time)<=30
group by usr_id,login_time),
b as(
select
usr_id,
login_time,
date_sub(login_time,interval row_number()over(partition by usr_id order by login_time) day) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having consecutive_days >=3
order by usr_id,
start_date
2026-02-09 连续登录3天及以上  
with a as(
select
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(current_date(),login_time)<=30
group by usr_id,login_time),
b as(
select
usr_id,
login_time,
date_sub(login_time,interval row_number()over(partition by usr_id order by login_time) day) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having consecutive_days >=3
2026-01-29 购买人数最多的商品类目  
select
gd_typ,
count(distinct cust_uid) as buyer_count
from xhs_pchs_rcd a
join gd_inf b
on a.mch_id = b.gd_id
group by gd_typ
order by buyer_count desc
limit 1
2026-01-29 购买人数最多的商品类目  
select
gd_typ,
count(*) as buyer_count
from xhs_pchs_rcd a
join gd_inf b
on a.mch_id = b.gd_id
group by gd_typ
order by buyer_count desc
limit 1
2026-01-29 被收藏次数最多的商品  
select
gd_id,
gd_nm,
count(*) as fav_count
from xhs_fav_rcd a
join gd_inf b
on a.mch_id = b.gd_id
group by mch_id
order by fav_count desc
limit 1
2026-01-29 被收藏次数最多的商品  
select
gd_id,
gd_nm,
count(*) as fav_count
from xhs_fav_rcd a
join gd_inf b
on a.mch_id = b.gd_id
group by mch_id
order by fav_count
limit 1
2026-01-29 被收藏次数最多的商品  
select
gd_id,
gd_nm,
count(*) as fav_count
from xhs_fav_rcd a
join gd_inf b
on a.mch_id = b.gd_id
group by mch_id
2026-01-27 绘制小时进入人数曲线  
select
date_format(enter_time,'%h') as hour_entered,
count(distinct usr_id) as enter_count
from ks_live_t1
group by date_format(enter_time,'%h')
order by hour_entered desc
2026-01-27 绘制小时进入人数曲线  
select
date_format(enter_time,'%h') as hour_entered,
count(distinct usr_id) as enter_count
from ks_live_t1
group by date_format(enter_time,'%h')
order by hour_entered
2026-01-27 绘制小时进入人数曲线  
select
date_format(enter_time,'%h') as hour_entered,
count(*) as enter_count
from ks_live_t1
group by date_format(enter_time,'%h')
order by hour_entered
2026-01-27 绘制小时进入人数曲线  
select
date_format(enter_time,'%h') as hour_entered,
count(*) as enter_count
from ks_live_t1
group by date_format(enter_time,'%h')
2026-01-27 绘制小时进入人数曲线  
select
hour(enter_time) as hour_entered,
count(*) as enter_count
from ks_live_t1
group by hour(enter_time)
2026-01-27 绘制小时进入人数曲线  
select
date_format(enter_time,'%Y-%m-%d') as hour_entered,
count(*) as enter_count
from ks_live_t1
group by date_format(enter_time,'%Y-%m-%d')
2026-01-27 绘制小时进入人数曲线  
select
date_format(enter_time,'%y-%m-%d') as hour_entered,
count(*) as enter_count
from ks_live_t1
group by date_format(enter_time,'%y-%m-%d')
2025-12-24 销售金额前10的商品信息（2）  
select
*
from 
(select
date_format(order_time,'%Y-%m-%d') as order_date,
goods_id,
sum(order_gmv) as total_gmv,
row_number()over(partition by date_format(order_time,'%Y-%m-%d') order by sum(order_gmv)) as ranking
from order_info
where date_format(order_time,'%Y-%m') = '2024-10'
group by date_format(order_time,'%Y-%m-%d'),goods_id) a
where ranking <=3
2025-12-24 10月1日后再也没活跃过的用户  
select
 count(distinct usr_id)
 from user_login_log
 where date_format(login_time,'%Y-%m-%d') <='2024-10-01'
 and usr_id not in(
 select
 distinct
 usr_id
 from user_login_log
 where date_format(login_time,'%Y-%m-%d') >'2024-10-01'
 )
2025-12-24 10月1日后再也没活跃过的用户  
select
 distinct
 usr_id
 from user_login_log
 where date_format(login_time,'%Y-%m-%d') <='2024-10-01'
 and usr_id not in(
 select
 distinct
 usr_id
 from user_login_log
 where date_format(login_time,'%Y-%m-%d') >'2024-10-01'
 )