同济数分王

分类

/ 注册

全部文章（共11篇）

题解 | 近一个月发布的视频中热度最高的top3视频

select video_id, round( (100*(sum(if_wanbo)/count(*))+5*sum(if_like)+3*sum(if_comment)+2*sum(if_retweet))/(1+min(diff_time)) ...

2025-07-21

1 41

题解 | 国庆期间每类视频点赞量和转发量

select * from ( select tag, date_format(start_time, "%Y-%m-%d") as dt, sum(sum(if_like))...

2025-07-16

0 64

题解 | 每个创作者每月的涨粉率及截止当前的总粉丝量

select author, date_format(start_time, "%Y-%m") as month, round(sum(case when if_follow=2 then -1 else if_follow end)/count(star...

2025-07-16

0 52

题解 | 平均播放进度大于60%的视频类别

select tag, concat(avg_play_progress, "%") as avg_play_progress from ( select tag, round( ...

2025-07-10

0 65

题解 | 各个视频的平均完播率

select a.video_id, round((sum(if(end_time-start_time >= duration, 1, 0))/count(start_time)), 3) as avg_comp_play_rate from tb_user_vide...

2025-07-10

0 53

题解 | 获得积分最多的人(三)

select u.id, u.name, t.grade as grade_sum from ( select user_id, grade, rank() over(or...

2025-07-03

0 41

题解 | 输出播放量最高的视频

with t as ( select p1.id, p1.cid, sum( if ( p2.start_time between ...

2025-04-28

1 83

题解 | 完成员工考核试卷突出的非领导员工

-- 这是用窗口函数的解法 with t as ( select r.emp_id, emp_level, r.exam_id, tag, start_ti...

2025-04-22

1 71

题解 | 被重复观看次数最多的3个视频

with t1 as ( SELECT uid, c.cid, release_date, case when count(*) > 1 th...

2025-04-19

1 60

题解 | 最长连续登录天数

select user_id, max(continuous_days) as max_consec_days from ( select user_id, count(*) as continuous_days...

2025-04-05

1 55