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

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

https://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11

select * from(
select
       tag,
       day,
       sum(sum_like_day),
       max(sum_re_day)
from(
select day,
       tag,
       sum(if_like) sum_like_day,
       sum(if_retweet) sum_re_day
from(
select '2021-10-01' day,
       tag,
       if_like,
       if_retweet,
       date
from (
     select
       tag,
       if_like,
       if_retweet,
        date_format(start_time,'%Y-%m-%d') `date`
from tb_user_video_log tuvl
join tb_video_info tvi on  tuvl.video_id=tvi.video_id
         )tmp
where datediff('2021-10-01',date)<=6
and datediff('2021-10-01',date)>=0)t2
group by tag,date
order by  tag desc ,day)t3
group by day, tag
union
select
       tag,
       day,
       sum(sum_like_day),
       max(sum_re_day)
from(
select day,
       tag,
       sum(if_like) sum_like_day,
       sum(if_retweet) sum_re_day
from(
select '2021-10-02' day,
       tag,
       if_like,
       if_retweet,
       date
from (
     select
       tag,
       if_like,
       if_retweet,
        date_format(start_time,'%Y-%m-%d') `date`
from tb_user_video_log tuvl
join tb_video_info tvi on  tuvl.video_id=tvi.video_id
         )tmp
where datediff('2021-10-02',date)<=6
and datediff('2021-10-02',date)>=0)t2
group by tag,date
order by  tag desc ,day)t3
group by day, tag
union
select
       tag,
       day,
       sum(sum_like_day),
       max(sum_re_day)
from(
select day,
       tag,
       sum(if_like) sum_like_day,
       sum(if_retweet) sum_re_day
from(
select '2021-10-03' day,
       tag,
       if_like,
       if_retweet,
       date
from (
     select
       tag,
       if_like,
       if_retweet,
        date_format(start_time,'%Y-%m-%d') `date`
from tb_user_video_log tuvl
join tb_video_info tvi on  tuvl.video_id=tvi.video_id
         )tmp
where datediff('2021-10-03',date)<=6
and datediff('2021-10-03',date)>=0)t2
group by tag,date
order by  tag desc ,day)t3
group by day, tag)t2
order by tag desc ,day

#纯粹的学习分享#
全部评论

相关推荐

09-01 09:00
已编辑
四川旅游学院 运营
牛客55195891...:主要是专业不好,别的没毛病
牛客解忧铺
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务