题解 | #近一个月发布的视频中热度最高的top3视频#
近一个月发布的视频中热度最高的top3视频
http://www.nowcoder.com/practice/0226c7b2541c41e59c3b8aec588b09ff
当天时间的逻辑不是很好得出
select video_id,
round((100 * ratio + 5 * fun + 3 * comment_cnt + 2 * retweet) / (1 + lastest_day), 0)
from (
select t1.video_id,
sum(case when timestampdiff(second, t1.start_time, t1.end_time) < t2.duration
then 0 else 1 end) / count(1) ratio,
sum(t1.if_like) fun,
sum(t1.if_retweet) retweet,
count(t1.comment_id) comment_cnt,
min(datediff(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), date(t1.end_time))) lastest_day
from tb_user_video_log t1
left join tb_video_info t2
on t1.video_id = t2.video_id
where DATEDIFF(DATE((SELECT MAX(end_time) FROM tb_user_video_log)), DATE(release_time)) <= 29
group by 1
) t
ORDER BY 2 DESC LIMIT 3
