牛客749132389号

分类

题解(8)

/ 注册

全部文章（共9篇）

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

SELECT video_id, -- 完播率 -- 新鲜度 round(( 100*sum(if(TIMESTAMPDIFF(second,start_time,end_time)>=duration,1,0))/count(video_id)+ 5*sum(if_like)+3*count...

2022-07-04

0 142

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

with temp1 as (SELECT tag, date(start_time) date,sum(if_like) if_like,sum(if_retweet) if_retweet FROM tb_user_video_log a JOIN tb_video_info b u...

2022-07-03

0 153

题解 | #2021年11月每天的人均浏览文章时长#

SELECT date(in_time) dt, ROUND( sum(TIMESTAMPDIFF(second,in_time,out_time))/count(DISTINCT uid), 1 ) avg_viiew_len_sec from tb_user_log WHERE DA...

Mysql

2022-06-29

0 148

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

with temp1 as (SELECT '2021-10-01' dt, DATE_FORMAT(start_time,'%Y-%m') date,tag,sum(if_like) sum_like_cnt_7d, SUM(if_retweet) as max_retweet_cnt_7d fr...

Mysql

2022-06-27

0 150

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

SELECT tag,SUM(a.if_retweet) as retweet_cut, ROUND(SUM(a.if_retweet)/count(start_time),3) as retweet_rate from tb_user_video_log a JOIN tb_video_info ...

Mysql

2022-06-25

0 141

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

with temp1 as ( select a.*,b.tag from tb_user_video_log a INNER JOIN tb_video_info b USING(video_id) ), temp2 as ( selec...

Mysql

2022-06-23

0 179

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

SELECT tag, CONCAT(ROUND(avg(IF(TIMESTAMPDIFF(second,start_time,end_time)>duration,1,TIMESTAMPDIFF(second,start_time,end_time)/duration))*100...

Mysql

2022-06-22

0 190

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

with temp1 as (SELECT tag,video_id,if(TIMESTAMPDIFF(second,start_time,end_time)>=duration,duration,TIMESTAMPDIFF(second,start_time,end_time)) as ga...

Mysql

2022-06-22

0 192

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

with totalcount as ( SELECT video_id, count() as totalcount from tb_user_video_log WHERE EXTRACT(YEAR from start_time) = '2021' GROUP BY video_id ), ...

Mysql

2022-06-21

0 172