排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-02-21 给商品打四类标签（列）  已解决
2025-02-21 一线城市历年平均气温  已解决
2025-02-19 连续登录3天及以上  已解决
2025-02-19 得物面试真题（4）首单Mac二单iPhone的客户  已解决
2025-02-18 快手面试真题（2）同时在线人数峰值  已解决
2025-02-18 分类别人均在线时长最火直播间  已解决
2025-02-18 窗口函数（2）所有前一和每类前一，继续搞懂排序窗口函数  已解决
2025-02-18 窗口函数（1）年度前三和每月前三，搞懂排序窗口函数  已解决
2024-11-29 小结（1）大数据早就能扫黄，找足证据不慌张  已解决
2024-11-28 按照车类统计行程次数  未解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-11-30 10月1日后再也没活跃过的用户  
题目描述和代码的实现，10-01所属区间不一致呀
 
注意开闭区间
2024-11-29 小宇宙电台的同期群分析  
with data1 as (
    select distinct usr_id,
                    date(login_time) as login_date
    from user_login_log
),
     data2 as (
         select usr_id,
                login_date,
                first_value(login_date) over (partition by usr_id order by login_date) as `start_date`,
                lag(login_date) over (partition by usr_id order by login_date) as `pre_date`
         from data1
     ),
     data3 as (
         select  distinct data1.usr_id,
                         data1.login_date,
                         case
                             when pre_date is null and datediff(data1.login_date,start_date) = 0 then 'flag-1'
                             when pre_date is not null and datediff(data1.login_date,pre_date) <= 3 then 'flag-2'
                             else 'flag-3'
                         end as flag
         from data1
                left join data2
                            on (data1.usr_id,data1.login_date) = (data2.usr_id,data2.login_date)
     ),
     data4 as (
         select login_date,
                round((sum(if(flag = 'flag-1', 1, 0)) / count(1)) * 100, 2) as rate_flag_1,
                round((sum(if(flag = 'flag-2', 1, 0)) / count(1)) * 100, 2) as rate_flag_2,
                round((sum(if(flag = 'flag-3', 1, 0)) / count(1)) * 100, 2) as rate_flag_3
         from data3
         group by login_date
     )
select login_date,
       concat_ws(',', rate_flag_1, rate_flag_2, rate_flag_3) as pct
from data4
where year(login_date) = '2024'
group by login_date
order by login_date;
求指点这个思路的错误呀，一直是0过不去
 
啥也没说
2024-11-29 小结（2）越花越多是死罪，按月统计Substr  
除了把条件二和条件一分开列出来，还有什么坑点呀，汗流浃背了
 
看了下你代码，感觉逻辑乱了啊哈哈哈
2024-11-29 小结（1）大数据早就能扫黄，找足证据不慌张  
交了十几次，最后发现是字段名不对
 
哈哈 好多人反应这个问题，现在已经修正了。不需要严格对应字段名

提交记录

提交日期 题目名称 提交代码
2025-07-23 窗口函数（7）三天吃四餐，你特么是不是乔杉？  
select
	distinct
	usr_id
from(
select
usr_id,
login_date,
lag(login_date,3)over(partition by usr_id order by login_date) as pre_date
from(
select 
usr_id,
date_format(trx_time,'%Y-%m-%d') as login_date
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
)a
)b
where datediff(login_date,pre_date) <= 3
order by usr_id asc;
2025-07-23 窗口函数（7）三天吃四餐，你特么是不是乔杉？  
select 
	usr_id
from(
select
usr_id,
login_date,
lag(login_date,3)over(partition by usr_id order by login_date) as pre_date
from(
select 
usr_id,
date_format(trx_time,'%Y-%m-%d') as login_date
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
)a
)b
where datediff(login_date,pre_date) <= 2
order by usr_id asc;
2025-07-23 窗口函数（7）三天吃四餐，你特么是不是乔杉？  
select 
	usr_id
from(
select
usr_id,
login_date,
lag(login_date,3)over(partition by usr_id order by login_date) as pre_date
from(
select 
usr_id,
date_format(trx_time,'%Y-%m-%d') as login_date
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
)a
)b
where datediff(login_date,pre_date) <= 3
order by usr_id asc;
2025-07-23 快手面试真题（2）同时在线人数峰值  
select 
	c.live_id,
live_nm,
max_online_users
from(
select 
live_id,
max(cur_cnt) as max_online_users
from(
select 
live_id,
user_time,
sum(flag) over(partition by live_id order by user_time) as cur_cnt
from (
select 
live_id,
enter_time as user_time,
1 as flag
from ks_live_t1
union all
select
live_id,
leave_time as user_time,
-1 as flag
from ks_live_t1
)a
)b
group by live_id
)c
left join ks_live_t2 t2
on c.live_id = t2.live_id
order by max_online_users desc;
2025-07-23 快手面试真题（1）同时在线人数  
select 
	t1.live_id,
live_nm,
count(1) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
where enter_time <= '2021-09-12 23:48:38' and leave_time >= '2021-09-12 23:48:38'
group by live_id,live_nm
order by online_users desc;
2025-07-23 快手面试真题（1）同时在线人数  
select 
	t1.live_id,
live_nm,
count(1) as online_users
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
where enter_time <= '2021-09-12 11:48:38' and leave_time >= '2021-09-12 11:48:38'
group by live_id,live_nm
order by online_users desc;
2025-07-21 窗口函数（7）三天吃四餐，你特么是不是乔杉？  
select 
	distinct
	usr_id
from(
select 
	usr_id,
trx_time,
lag(trx_time,3)over(partition by usr_id order by trx_time) as pre_time
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
)a
where datediff(trx_time,pre_time) <= 3
order by usr_id;
2025-07-21 窗口函数（7）三天吃四餐，你特么是不是乔杉？  
select 
	distinct
	usr_id
from(
select 
	usr_id,
trx_time,
lag(trx_time,3)over(partition by usr_id order by trx_time) as pre_time
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
)a
where datediff(trx_time,pre_time) <= 4
order by usr_id;
2025-07-21 窗口函数（7）三天吃四餐，你特么是不是乔杉？  
select 
	usr_id
from(
select 
	usr_id,
trx_time,
lag(trx_time,3)over(partition by usr_id order by trx_time) as pre_time
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
)a
where datediff(trx_time,pre_time) <= 4
order by usr_id;
2025-07-21 快手面试真题（2）同时在线人数峰值  
select 
	live_id,
live_nm,
max(cur_cnt) as max_online_users
from(
select 
a.live_id,
t2.live_nm,
flag_time,
flag,
sum(flag) over(partition by live_id order by flag_time) as cur_cnt
from(
select 
live_id,
enter_time as flag_time,
1 as flag
from ks_live_t1
union all
select 
live_id,
leave_time as flag_time,
-1 as flag
from ks_live_t1
)a
left join ks_live_t2 t2 on a.live_id = t2.live_id
)b
group by live_id, live_nm
order by max_online_users desc;
2025-07-21 快手面试真题（1）同时在线人数  
select 
	t1.live_id,
live_nm,
count(*) as online_users
from ks_live_t1 t1
left join ks_live_t2 t2
on t1.live_id = t2.live_id
where date_format(t1.enter_time,'%Y-%m-%d %H:%i:%s') <= '2021-09-12 23:48:38' and date_format(t1.leave_time,'%Y-%m-%d %H:%i:%s') >= '2021-09-12 23:48:38'
group by live_id,live_nm
order by online_users desc;
2025-07-21 分类别人均在线时长最火直播间  
select 
	live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration
from(
select 
live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration,
row_number()over(partition by live_type order by avg_duration desc) as rn
from(
 select 
t1.live_id,
live_nm,
live_type,
sum(timestampdiff(SECOND,t1.enter_time,t1.leave_time)) as total_duration,
count(*) as total_users,
sum(timestampdiff(SECOND,t1.enter_time,t1.leave_time))/count(*) as avg_duration
from ks_live_t1 t1
left join ks_live_t2 t2
on t1.live_id = t2.live_id
where date_format(t1.enter_time, '%Y-%m-%d %H') >= '2021-09-12 23' and date_format(t1.leave_time, '%Y-%m-%d %H') <= '2021-09-13 04'
group by live_id,live_nm,live_type
)a
)b
where rn = 1
order by live_id asc;
2025-07-21 分类别的最火直播间  
select 
	live_id,
live_nm,
live_type,
enter_cnt
from(
select 
live_id,
live_nm,
	live_type,
enter_cnt,
row_number()over(partition by live_type order by enter_cnt desc) as rn
from(
select
t1.live_id,
live_nm,
	live_type,
count(*) as enter_cnt	
from ks_live_t1 t1
left join ks_live_t2 t2
on t1.live_id = t2.live_id
where date_format(enter_time,'%Y-%m-%d %H') = '2021-09-12 23'
group by live_id,live_nm,live_type
)a
)b
where rn = 1
order by live_id asc;
2025-07-21 分类别的最火直播间  
select 
	live_id,
live_nm,
live_type,
enter_cnt
from(
select 
live_id,
live_nm,
	live_type,
enter_cnt,
row_number()over(partition by live_id order by enter_cnt desc) as rn
from(
select
t1.live_id,
live_nm,
	live_type,
count(*) as enter_cnt	
from ks_live_t1 t1
left join ks_live_t2 t2
on t1.live_id = t2.live_id
where date_format(enter_time,'%Y-%m-%d %H') = '2021-09-12 23'
group by live_id,live_nm,live_type
)a
)b
where rn = 1
order by live_id asc;
2025-07-21 不分类别的最火直播间  
select t1.live_id,
 live_nm,
 count(*) as enter_cnt
from ks_live_t1 t1
left join ks_live_t2 t2 on t1.live_id=t2.live_id
where DATE_FORMAT(t1.enter_time,'%Y-%m-%d %H') = '2021-09-12 23'
group by live_id,live_nm
order by enter_cnt desc
limit 5;
2025-07-21 不分类别的最火直播间  
select t1.live_id,
 live_nm,
 count(*) as enter_cnt
from ks_live_t1 t1
left join ks_live_t2 t2 on t1.live_id=t2.live_id
where DATE_FORMAT(t1.enter_time,'%Y-%m-%d %H') <= '2021-09-12 23' and DATE_FORMAT(t1.enter_time,'%Y-%m-%d %H') >='2021-09-12 23'
group by live_id,live_nm;
2025-07-21 绘制小时进入人数曲线  
select 
	lpad(hour_entered,2,'0') as hour_entered,
cnt as hour_entered
from(
select 
hour(enter_time) as hour_entered,
count(*) as cnt
from ks_live_t1 t1 
left join ks_live_t2 t2
ont1.live_id = t2.live_id
group by hour(enter_time)
)a
order by hour_entered;
2025-02-24 超过3个标签的视频  
SELECT 
    video_id, 
    title, 
    author_id,
    tag
FROM 
    ks_video_inf 
WHERE 
    LENGTH(tag) - LENGTH(REPLACE(tag, ' ', '')) + 1 > 3;
2025-02-24 快手面试真题（3）同时在线人数峰值时点  
with a as(
select
	live_id,
	`enter_time` as action_time,
	1 as flag
from ks_live_t1
union all
select
	live_id,
	leave_time as action_time,
	-1 as flag
from ks_live_t1
),
b as (
select
	live_id,
	action_time,
	sum(flag) over(partition by live_id order by action_time) as prelive_pretime_cnt
from a
),
c as(
select
	live_id,
	action_time,
	prelive_pretime_cnt,
	max(prelive_pretime_cnt)over(partition by live_id) as target
from b
),
d as (
select
live_id,
target as max_online_users,
min(action_time) as first_peak_time,
max(action_time) as last_peak_time
from c
where prelive_pretime_cnt = target
group by live_id,target
)
select
	d.live_id,
ks_live_t2.live_nm,
max_online_users,
first_peak_time,
last_peak_time
from d
left join ks_live_t2 on d.live_id = ks_live_t2.live_id;
2025-02-24 快手面试真题（2）同时在线人数峰值  
WITH UserActivity AS (
    SELECT 
        t1.usr_id,
        t1.live_id,
        t1.enter_time AS event_time,
        1 AS act
    FROM 
        ks_live_t1 t1
    UNION ALL
    SELECT 
        t1.usr_id,
        t1.live_id,
        t1.leave_time AS event_time,
        -1 AS act
    FROM 
        ks_live_t1 t1
),
CumulativeOnline AS (
    SELECT 
        live_id,
        event_time,
        SUM(act) OVER (PARTITION BY live_id ORDER BY event_time) AS online_users
    FROM 
        UserActivity
),
PeakOnline AS (
    SELECT 
        live_id,
        event_time,
        online_users,
        MAX(online_users) OVER (PARTITION BY live_id) AS max_online_users
    FROM 
        CumulativeOnline
),
PeakTimes AS (
    SELECT 
        live_id,
        event_time AS peak_time,
        max_online_users
    FROM 
        PeakOnline
    WHERE 
        online_users = max_online_users
)
SELECT 
    pt.live_id,
    t2.live_nm,
    pt.max_online_users
FROM 
    PeakTimes pt
JOIN 
    ks_live_t2 t2
ON 
    pt.live_id = t2.live_id
GROUP BY
    pt.live_id,
    t2.live_nm,
    pt.max_online_users
ORDER BY 
    pt.max_online_users DESC;