牛客464722063号

分类

题解(16)

/ 注册

全部文章（共16篇）

题解 | #每类视频近一个月的转发量/率#

select i.tag, sum(l.if_retweet) retweet_cut, round(sum(l.if_retweet)/count(i.tag),3) retweet_rate from tb_user_video_log l left join tb_video_info i o...

Mysql

2022-04-29

0 248

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

select a.tag,CONCAT(a.rate, "%") aa from ( select i.tag, round( avg(if(timestampdiff(second,l.start_time,l.end_time) >=i.duration,1,timestampdiff(...

Mysql

2022-04-28

0 193

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

select a.video_id, round(sum(a.dis)/count(a.video_id),3) res from (select l.video_id,if(l.end_time-l.start_time>=i.duration,1,0) as dis from tb_u...

Mysql

2022-04-28

0 269

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

select s.name,a.cnt from (select subject_id,count(subject_id) cnt from submission where create_time='2022-04-09' group by subject_id) a join subject ...

Mysql

2022-04-09

1 405

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

select music_name from music where id in ( select music_id from music_likes left join follow on music_likes.user_id = follow.follower_id where follow....

Mysql

2022-04-09

0 298

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

select user.id,user.name,f.s_n from user, (select user_id, sum(real_value) s_n from (select user_id ,if(type='reduce',-grade_num,grade_num) real_valu...

Mysql

2022-04-08

0 228

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

select u.id,u.name,g.s from user u left join (select user_id, sum(grade_num) s from grade_info group by user_id) g on u.id = g.user_id where g.s = (se...

Mysql

2022-04-08

0 238

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

select name,g.s_g from user join (select user_id,sum(grade_num) s_g from grade_info where type='add' group by user_id) g on user.id = g.user_id ord...

Mysql

2022-04-08

0 217

题解 | #牛客每个人最近的登录日期(三)#

select round(count(distinct l.user_id)/(select count(distinct user_id) from login),3) p from login l, (select user_id,DATE_ADD(date,INTERVAL 1 DAY) d ...

Mysql

2022-03-25

0 362

题解 | #牛客每个人最近的登录日期(二)#

(select u.name u_n,c.name c_n,l.date d,max(l.date) over(partition by l.user_id) aa from login l join user u on l.user_id = u.id join client c on l.cli...

Mysql

2022-03-25

0 234