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

WITH t1 AS (
    SELECT a.video_id,
           TIMESTAMPDIFF(SECOND,start_time,end_time) AS time_delta,
           b.duration
    FROM tb_user_video_log a
    LEFT JOIN tb_video_info b
    ON a.video_id = b.video_id
    WHERE SUBSTR(start_time,1,4) = '2021'
    HAVING time_delta >= duration
),
t2 AS (
    SELECT video_id,
           COUNT(video_id) AS wanboshu
    FROM t1
    GROUP BY video_id
),
t3 AS (
    SELECT video_id,
           COUNT(video_id) AS totalnum
    FROM tb_user_video_log
    WHERE SUBSTR(start_time,1,4) = '2021'
    GROUP BY video_id
),
t4 AS (
    SELECT t3.video_id,
           ROUND(IFNULL(wanboshu,0) / totalnum,3) AS avg_comp_play_rate
    FROM t3
    LEFT JOIN t2
    ON t3.video_id = t2.video_id
    ORDER BY avg_comp_play_rate DESC
)SELECT * FROM t4;

# 这道题的关键在于时间节点的筛选判断!

全部评论

相关推荐

我是没经验的毕业生,这啥情况啊会不会是hr在刷kpi
JamesGosli...:字节boss属于是群发了,我都快入职字节了,其他部门还在和我boss打招呼
点赞 评论 收藏
分享
门口唉提是地铁杀:之前b站被一个游戏demo深深的吸引了。看up主页发现是个初创公司,而且还在招人,也是一天60。二面的时候要我做一个登录验证和传输文件两个微服务,做完要我推到github仓库,还要我加上jaeger和一堆运维工具做性能测试并且面试的时候投屏演示。我傻乎乎的做完以后人家跟我说一句现在暂时不招人,1分钱没拿到全是白干
你的秋招第一场笔试是哪家
点赞 评论 收藏
分享
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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