排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-11-17 专注力强的总用户数  已解决
2025-11-05 快手面试真题（2）同时在线人数峰值  已解决

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-11-18 横屏与竖屏视频的完播率（按AI配音和字幕分类）  
WITH VideoCompletion AS (
    SELECT 
        v.video_id,
        v.screen_type,
        v.if_AI_talking,
        v.if_hint,
        COUNT(DISTINCT CASE WHEN TIMESTAMPDIFF(SECOND, u.start_time, u.end_time) >= v.duration THEN u.uid END) AS completed_views,
        COUNT(DISTINCT u.uid) AS total_views
    FROM 
        ks_video_inf v
    JOIN 
        ks_video_wat_log u ON v.video_id = u.video_id
    WHERE 
        u.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
    GROUP BY 
        v.video_id, v.screen_type, v.if_AI_talking, v.if_hint
),
PivotTable AS (
    SELECT 
        screen_type,
        SUM(CASE WHEN if_AI_talking = 1 AND if_hint = 1 THEN completed_views ELSE 0 END) / NULLIF(SUM(CASE WHEN if_AI_talking = 1 AND if_hint = 1 THEN total_views ELSE 0 END), 0) * 100 AS AI_with_hint,
        SUM(CASE WHEN if_AI_talking = 1 AND if_hint = 0 THEN completed_views ELSE 0 END) / NULLIF(SUM(CASE WHEN if_AI_talking = 1 AND if_hint = 0 THEN total_views ELSE 0 END), 0) * 100 AS AI_no_hint,
        SUM(CASE WHEN if_AI_talking = 0 AND if_hint = 1 THEN completed_views ELSE 0 END) / NULLIF(SUM(CASE WHEN if_AI_talking = 0 AND if_hint = 1 THEN total_views ELSE 0 END), 0) * 100 AS no_AI_with_hint,
        SUM(CASE WHEN if_AI_talking = 0 AND if_hint = 0 THEN completed_views ELSE 0 END) / NULLIF(SUM(CASE WHEN if_AI_talking = 0 AND if_hint = 0 THEN total_views ELSE 0 END), 0) * 100 AS no_AI_no_hint
    FROM 
        VideoCompletion
    GROUP BY 
        screen_type
)
SELECT 
    screen_type,
    ROUND(AI_with_hint, 2) AS AI_with_hint,
    ROUND(AI_no_hint, 2) AS AI_no_hint,
    ROUND(no_AI_with_hint, 2) AS no_AI_with_hint,
    ROUND(no_AI_no_hint, 2) AS no_AI_no_hint
FROM 
    PivotTable;
2025-11-18 播放量最高的标签  
WITH TagSplit AS (
    SELECT 
        v.video_id,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(v.tag, ' ', n.n), ' ', -1)) AS tag
    FROM 
        ks_video_inf v
    JOIN 
        (SELECT 1 + units.i + tens.i * 10 AS n
         FROM (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) units,
              (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tens
         WHERE 1 + units.i + tens.i * 10 <= (SELECT MAX(LENGTH(tag) - LENGTH(REPLACE(tag, ' ', '')) + 1) FROM ks_video_inf)
        ) n ON n.n <= LENGTH(v.tag) - LENGTH(REPLACE(v.tag, ' ', '')) + 1
),
TagPlays AS (
    SELECT 
        ts.tag,
        COUNT(1) AS total_plays
    FROM 
        TagSplit ts
    JOIN 
        ks_video_inf v ON ts.video_id = v.video_id
    JOIN 
        ks_video_wat_log u ON v.video_id = u.video_id
    WHERE 
        u.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
    GROUP BY 
        ts.tag
)
SELECT 
    tag,
    total_plays
FROM 
    TagPlays
ORDER BY 
    total_plays DESC
LIMIT 1;
2025-11-17 专注力强的总用户数  
with inf as (select 
uid,count(distinct k1.video_id) as cnt
from 
ks_video_wat_log k1
left join 
ks_video_inf k2
on 
k1.video_id=k2.video_id
where 
timestampdiff(second,k1.start_time,k1.end_time)>=k2.duration
 and 
 duration>=180
andk1.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
group by uid 
having count(distinct k1.video_id)>=2)
select count( distinct uid) from inf
2025-11-17 专注力强的总用户数  
with inf as (select 
uid,count(distinct k1.video_id) as cnt
from 
ks_video_wat_log k1
left join 
ks_video_inf k2
on 
k1.video_id=k2.video_id
where 
timestampdiff(second,k1.start_time,k1.end_time)>=k2.duration
 and 
 duration>=180
andk1.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
group by uid 
having count(distinct k1.video_id)>2)
select count( distinct uid) from inf
2025-11-17 专注力强的总用户数  
with inf as (select 
uid,count(distinct k1.video_id) as cnt
from 
ks_video_wat_log k1
inner join 
ks_video_inf k2
on 
k1.video_id=k2.video_id
where 
timestampdiff(second,k1.start_time,k1.end_time)>=k2.duration
 and 
 duration>180
andk1.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
group by uid 
having cnt>2)
select count(uid) from inf
2025-11-17 专注力强的总用户数  
with inf as (select 
uid,count(distinct k1.video_id) as cnt
from 
ks_video_wat_log k1
inner join 
ks_video_inf k2
on 
k1.video_id=k2.video_id
and 
timestampdiff(second,k1.start_time,k1.end_time)>=k2.duration
where duration>180
andk1.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
group by uid 
having cnt>2)
select count(uid) from inf
2025-11-17 专注力强的总用户数  
with inf as (select 
uid,count(distinct k1.video_id) as cnt
from 
ks_video_wat_log k1
inner join 
ks_video_inf k2
on 
k1.video_id=k2.video_id
and 
timestampdiff(second,k1.start_time,k1.end_time)>=k2.duration
where duration>180
group by uid 
having cnt>2)
select count(uid) from inf
2025-11-17 计算完播率（按人数）  
SELECT 
    v.video_id, 
    v.title, 
    ROUND(
        (COUNT(DISTINCT CASE WHEN TIMESTAMPDIFF(SECOND, u.start_time, u.end_time) >= v.duration THEN u.uid END) / 
         COUNT(DISTINCT u.uid)) * 100, 
        4
    ) AS completion_rate
FROM 
    ks_video_inf v
JOIN 
    ks_video_wat_log u ON v.video_id = u.video_id
WHERE 
    u.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY 
    v.video_id, v.title
ORDER BY 
    completion_rate DESC
LIMIT 5;
2025-11-17 计算完播率（按人数）  
SELECT 
    v.video_id, 
    v.title, 
    ROUND(
        (SUM(CASE WHEN TIMESTAMPDIFF(SECOND, u.start_time, u.end_time) >= v.duration THEN 1 ELSE 0 END) / COUNT(u.uid)) * 100, 
        2
    ) AS completion_rate
FROM 
    ks_video_inf v
JOIN 
    ks_video_wat_log u ON v.video_id = u.video_id
WHERE 
    u.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY 
    v.video_id, v.title
HAVING 
    COUNT(u.uid) > 1 
ORDER BY 
    completion_rate DESC
LIMIT 5;
2025-11-17 总播放时长最长的视频  
SELECT 
    v.video_id, 
    v.title, 
    ROUND(SUM((TIMESTAMPDIFF(SECOND, u.start_time, u.end_time))) / 3600, 2) AS total_play_duration_hours
FROM 
    ks_video_inf v
JOIN 
    ks_video_wat_log u ON v.video_id = u.video_id
WHERE 
    u.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY 
    v.video_id, v.title
ORDER BY 
    total_play_duration_hours DESC
LIMIT 5;
2025-11-17 总播放时长最长的视频  
select 
video_id,
round(sum(timestampdiff(second,start_time,end_time))/3600,2)
from 
ks_video_wat_log 
group by video_id
order by 2 desc
;
2025-11-17 近1个月最热短视频  
select 
ks_video_wat_log.video_id,title,count(uid),sum(if_like),sum(if_retweet),sum(if_fav),sum(if_like)+sum(if_retweet)+sum(if_fav)
from 
ks_video_wat_log
left join
ks_video_inf
on 
ks_video_wat_log.video_id=ks_video_inf.video_id
group by 1
order by 7 desc
limit 3
;
2025-11-17 近1个月最热短视频  
select 
ks_video_wat_log.video_id,title,count(uid),sum(if_like),sum(if_retweet),sum(if_fav),sum(if_like)+sum(if_retweet)+sum(if_fav)
from 
ks_video_wat_log
left join
ks_video_inf
on 
ks_video_wat_log.video_id=ks_video_inf.video_id
group by 1
limit 3
;
2025-11-17 近1个月最热短视频  
select 
ks_video_wat_log.video_id,title,count(uid),sum(if_like),sum(if_retweet),sum(if_fav),sum(if_like)+sum(if_retweet)+sum(if_fav)
from 
ks_video_wat_log
left join
ks_video_inf
on 
ks_video_wat_log.video_id=ks_video_inf.video_id
group by 1
limit 10
;
2025-11-17 近1个月最热短视频  
select 
ks_video_wat_log.video_id,title,count(uid),sum(if_like),sum(if_retweet),sum(if_fav),sum(if_like)+sum(if_retweet)+sum(if_fav)
from 
ks_video_wat_log
left join
ks_video_inf
on 
ks_video_wat_log.video_id=ks_video_inf.video_id
group by 1
;
2025-11-17 超过3个标签的视频  
SELECT 
    video_id, 
    title, 
    author_id,
    tag
FROM 
    ks_ved_inf 
WHERE 
    LENGTH(tag) - LENGTH(REPLACE(tag, ' ', '')) + 1 > 3;
2025-11-17 快手面试真题（3）同时在线人数峰值时点  
with act as (
select usr_id,live_id,enter_time as act_time, 1 as acta
from ks_live_t1
union all
 select usr_id,live_id,leave_time as act_time,-1 as acta
from ks_live_t1
),
point as (
select
live_id,act_time,sum(acta) over(partition by live_id order by act_time) as psum
from act
),
rr as (
select 
live_id,max(psum) as hrn from point 
group by live_id
)
select
rr.live_id,live_nm,hrn as max_online_users,min(act_time),max(act_time)
from 
rr
left join
ks_live_t2
on rr.live_id=ks_live_t2.live_id
left join 
point
on rr.live_id=point.live_id and rr.hrn=point.psum
group by 1,2,3
2025-11-17 快手面试真题（2）同时在线人数峰值  
with act as (
select usr_id,live_id,enter_time as act_time, 1 as acta
from ks_live_t1
union all
 select usr_id,live_id,leave_time as act_time,-1 as acta
from ks_live_t1
),
point as (
select
live_id,act_time,sum(acta) over(partition by live_id order by act_time) as psum
from act
),
rr as (
select 
live_id,max(psum) as hrn from point 
group by live_id
)
select rr.live_id,live_nm,hrn from rr
join ks_live_t2
on rr.live_id=ks_live_t2.live_id
order by hrn desc
2025-11-17 快手面试真题（1）同时在线人数  
select
ks_live_t1.live_id,live_nm,count(distinct usr_id) 
from
ks_live_t1 
join ks_live_t2
on ks_live_t1.live_id=ks_live_t2.live_id
where
enter_time<='2021-09-12 23:48:38'
and 
leave_time>='2021-09-12 23:48:38'
group by 
1,2
order by 3 desc
2025-11-17 快手面试真题（1）同时在线人数  
select
ks_live_t1.live_id,live_nm,count(distinct usr_id) 
from
ks_live_t1 
join ks_live_t2
on ks_live_t1.live_id=ks_live_t2.live_id
where
enter_time<='2021-09-12 11:48:38'
and 
leave_time>='2021-09-12 11:48:38'
group by 
1,2
order by 3 desc