HIveSQL面试题52:近一个月发布的视频中热度最高的top3视频【抖音面试题,不得不去吐槽的一个题目】

这篇具有很好参考价值的文章主要介绍了HIveSQL面试题52:近一个月发布的视频中热度最高的top3视频【抖音面试题,不得不去吐槽的一个题目】。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

目录

0 问题描述

        1 数据准备

        2 问题分析

       3 小结


0 问题描述

现有用户-视频互动表tb_user_video_log

id uid video_id start_time end_time if_follow if_like if_retweet comment_id
1 101 2001 2021-09-24 10:00:00 2021-09-24 10:00:30 1 1 1 NULL
2 101 2001 2021-10-01 10:00:00 2021-10-01 10:00:31 1 1 0 NULL
3 102 2001 2021-10-01 10:00:00 2021-10-01 10:00:35 0 0 1 NULL
4 103 2001 2021-10-03 11:00:50 2021-10-03 10:00:35 1 1 0 1732526
5 106 2002 2021-10-02 11:00:05 2021-10-02 11:01:04 2 0 1 NULL
6 107 2002 2021-10-02 10:59:05 2021-10-02 11:00:06 1 0 0 NULL
7 108 2002 2021-10-02 10:59:05 2021-10-02 11:00:05 1 1 1 NULL
8 109 2002 2021-10-03 10:59:05 2021-10-03 11:00:01 0 1 0 NULL
9 105 2002 2021-09-25 11:00:00 2021-09-25 11:00:30 1 0 1 NULL
10 101 2003 2021-09-26 11:00:00 2021-09-26 11:00:30 1 0 0 NULL
11 101 2003 2021-09-30 11:00:00 2021-09-30 11:00:30 1 1 0 NULL

(uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID)

短视频信息表tb_video_info

id video_id author tag duration release_time
1 2001 901 旅游 30 2021-09-05 07:00:00
2 2002 901 旅游 60 2021-09-05 07:00:00
3 2003 902 影视 90 2021-09-05 07:00:00
4 2004 902 影视 90 2021-09-05 08:00:00

(video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长, release_time-发布时间)

问题:找出近一个月发布的视频中热度最高的top3视频。

  • 热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度;
  • 新鲜度=1/(最近无播放天数+1);
  • 当前配置的参数a,b,c,d分别为100、5、3、2。
  • 最近播放日期以end_time-结束观看时间为准,假设为T,则最近一个月按[T-29, T]闭区间统计。
  • 结果中热度保留为整数,并按热度降序排序。

输出示例

示例数据的输出结果如下

video_id hot_index
2001 122
2002 56
2003

解释:

最近播放日期为2021-10-03,记作当天日期;近一个月(2021-09-04及之后)发布的视频有2001、2002、2003、2004,不过2004暂时还没有播放记录

视频2001完播率1.0(被播放次数4次,完成播放4次),被点赞3次,评论1次,转发2次,最近无播放天数为0,因此热度为:(100*1.0+5*3+3*1+2*2)/(0+1)=122

同理,视频2003完播率0,被点赞数1,评论和转发均为0,最近无播放天数为3,因此热度为:(100*0+5*1+3*0+2*0)/(3+1)=1(1.2保留为整数)。

1 数据准备

DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (
    `uid` string COMMENT '用户ID',
    video_id string COMMENT '视频ID',
    start_time string COMMENT '开始观看时间',
    end_time string COMMENT '结束观看时间',
    if_follow string COMMENT '是否关注',
    if_like string COMMENT '是否点赞',
    if_retweet string COMMENT '是否转发',
    comment_id string COMMENT '评论ID'
) ;

CREATE TABLE tb_video_info (
    video_id string COMMENT '视频ID',
    author string COMMENT '创作者ID',
    tag string COMMENT '类别标签',
    duration string COMMENT '视频时长(秒数)',
    release_time string COMMENT '发布时间'
);

INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES
   (101, 2001, '2021-09-24 10:00:00', '2021-09-24 10:00:30', 1, 1, 1, null)
  ,(101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:31', 1, 1, 0, null)
  ,(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:35', 0, 0, 1, null)
  ,(103, 2001, '2021-10-03 11:00:50', '2021-10-03 11:01:35', 1, 1, 0, 1732526)
  ,(106, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:04', 2, 0, 1, null)
  ,(107, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:06', 1, 0, 0, null)
  ,(108, 2002, '2021-10-02 10:59:05', '2021-10-02 11:00:05', 1, 1, 1, null)
  ,(109, 2002, '2021-10-03 10:59:05', '2021-10-03 11:00:01', 0, 1, 0, null)
  ,(105, 2002, '2021-09-25 11:00:00', '2021-09-25 11:00:30', 1, 0, 1, null)
  ,(101, 2003, '2021-09-26 11:00:00', '2021-09-26 11:00:30', 1, 0, 0, null)
  ,(101, 2003, '2021-09-30 11:00:00', '2021-09-30 11:00:30', 1, 1, 0, null);

INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES
   (2001, 901, '旅游', 30, '2021-09-05 7:00:00')
  ,(2002, 901, '旅游', 60, '2021-09-05 7:00:00')
  ,(2003, 902, '影视', 90, '2021-09-05 7:00:00')
  ,(2004, 902, '影视', 90, '2021-09-05 8:00:00');
----------------------------------
输出结果如下:
2001|122
2002|56
2003|1

2 问题分析

该题目中各项指标定义非常模糊,很多定义并不明确,明显出题人语文水平并不过关,很多定义并没有直接给出,容易产生歧义。

各项指标的确定:

(1)近一个月的定义:为当前日志表(视频互动表tb_user_video_log)中end_time最新的日期往前减去29天。题中给出的是假设为T,则最近一个月按[T-29, T]闭区间统计。这样的定义实际上有问题的(很无语。。。。),因为存在视频发布当天并没有播放记录的情况。

举个例子:10.03往前推30天是09.04。我们要获取的统计时间区间是09.04-10.03。假设某个视频09.01发布,但是5天后才有第一次播放记录,那么这个视频最早的end_time是09.06,按照这个end_time,用DATEDIFF法来生成,得到的时间区间是09.06-10.03,明显不完整,题目也是没有完全讲清楚。。。。按照题目那样区间的定义肯定有问题。。。。所以此题左边区间应该是end_time -29,右边区间应该是发布时间。。。。

(2)新鲜度:最近无播放天数:当前表中最新日期与该视频的最新播放日期之间的差值。

(题目中并没有给出明确定义,靠猜。。。。。。。。)

新鲜度=1/(最近无播放天数+1)

(3)热度:

         a:视频完播率:该题的完播率并没有给出明确定义,基本靠给的例子来猜测。

此处的定义应该是:用户-视频互动表tb_user_video_log中start_time 与end_time的差值大于短视频信息表tb_video_info中的duration时记为一次有效的播放(题目中描述的被播放次数。。。)与实际播放次数的比值(题目中描述的完成播放次数,我去理解实在太烧脑了。。。,实在不得不喷一下。。。)伪代码如下:

SUM(CASE WHEN(unix_timestamp(end_time)-unix_timestamp(start_time))>duration then 1 else 0 end) / COUNT(start.time)

      b:点赞数:SUM(CASE WHEN if_like=1 THEN 1 ELSE END)

      c:评论数              COUNT(comment_id)
      d:转发数              SUM(CASE WHEN if_retweet=1 THEN 1 ELSE 0 END)

最终SQL如下:

select
  video_id,
  cast(
    (
      100 * finish_play_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt
    ) * fresh_rate as decimal(18, 0)
  ) as hot_index
from
  (
    SELECT
      tvi.video_id,
      SUM(
        CASE
          WHEN(
            unix_timestamp(tuvl.end_time) - unix_timestamp(tuvl.start_time)
          ) >= cast(tvi.duration as int) then 1
          else 0
        end
      ) / COUNT(tuvl.start_time) as finish_play_rate,
      SUM(
        CASE
          WHEN tuvl.if_like = '1' THEN 1
          ELSE 0
        END
      ) as like_cnt,
      COUNT(tuvl.comment_id) as comment_cnt,
      SUM(
        CASE
          WHEN tuvl.if_retweet = '1' THEN 1
          ELSE 0
        END
      ) as retweet_cnt,
      1 / (
        datediff(
          to_date(max(tuvl.last_time)),
          to_date(max(tuvl.END_time))
        ) + 1
      ) as fresh_rate
    FROM
      (
        select
          *,
          max(end_time) over() as last_time
        from
          tb_user_video_log
      ) AS tuvl
      LEFT JOIN tb_video_info AS tvi ON tuvl.video_id = tvi.video_id
    WHERE
      TO_DATE(tvi.release_time) >= DATE_SUB(TO_DATE(tuvl.last_time), 29)
    GROUP BY
      tvi.video_id
  ) t
ORDER BY
  hot_index DESC
LIMIT
  3

最终结果如下

video_id        hot_index
2001            122
2002            56
2003            1
Time taken: 3.316 seconds, Fetched: 3 row(s)

3 小结

这道题源自于牛客网,题目本身没什么难度,但在牛客中通过率极低,原因是题目本身但表达很模糊,很多定义没有直接给出,靠面试者猜,造成了误解,如果面试遇到这种题目90%挂,因为你不能准确理解题意,需要反复和面试官确认,即使做出来了,最终也是失败,而这题竟然出自抖音,面试出题也太随意了,无力吐槽,看来面试有时候真的靠缘分,哈哈哈。。。。

欢迎关注石榴姐公众号"我的SQL呀",关注我不迷路

 文章来源地址https://www.toymoban.com/news/detail-424023.html

 

到了这里,关于HIveSQL面试题52:近一个月发布的视频中热度最高的top3视频【抖音面试题,不得不去吐槽的一个题目】的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • sql高频面试题-去除最高最低的平均

    面试或者笔试的过程中会设定各种各样的场景,在这些场景下考查我们SQL的查询能力,但是万变不离其宗,业务场景只是一个表现形式,抽象为SQL问题后其实基本上就是几类问题: 计算累计、连续,分类TopN等 。只要掌握这些问题的解法,并且可以举一反三,并不需要盲目的

    2024年02月13日
    浏览(57)
  • chromium 52 chrome 各个版本发布功能列表(58-84)

    From https://chromestatus.com/features ‘allow-top-navigation-by-user-activation’ iframe sandbox keyword Adds a new keyword named “allow-top-navigation-by-user-activation” for iframe sandbox, which requires a user activation (or gesture) being processed to trigger a top-level navigation. This change would enable more use cases of sandboxing untrusted

    2024年02月07日
    浏览(63)
  • 频次最高的38道selenium面试题及答案

    1、selenium的原理是什么? selenium的原理涉及到3个部分,分别是: 浏览器 driver:一般我们都会下载driver client:也就是我们写的代码 client其实并不知道浏览器是怎么工作的,但是driver知道,在selenium启动以后,driver其实充当了服务器的角色,跟client和浏览器通信,client根据we

    2024年02月01日
    浏览(62)
  • 「SQL面试题库」 No_61 每位学生的最高成绩

    「SQL面试题库」是由 不是西红柿 发起,全员免费参与的SQL学习活动。我每天发布1道SQL面试真题,从简单到困难,涵盖所有SQL知识点,我敢保证只要做完这100道题,不仅能轻松搞定面试,代码能力和工作效率也会有明显提升。 1.1 活动流程 整理题目 :西红柿每天无论刮风下雨

    2024年02月03日
    浏览(53)
  • 「SQL面试题库」 No_23 查询回答率最高的问题

    「SQL面试题库」是由 不是西红柿 发起,全员免费参与的SQL学习活动。我每天发布1道SQL面试真题,从简单到困难,涵盖所有SQL知识点,我敢保证只要做完这100道题,不仅能轻松搞定面试,代码能力和工作效率也会有明显提升。 1.1 活动流程 整理题目 :西红柿每天无论刮风下雨

    2023年04月08日
    浏览(37)
  • 面试算法52:展平二叉搜索树

    给定一棵二叉搜索树,请调整节点的指针使每个节点都没有左子节点。调整之后的树看起来像一个链表,但仍然是二叉搜索树。 看起来需要按照节点的值递增的顺序遍历二叉搜索树中的每个节点,并将节点用指向右子节点的指针连接起来。这就容易让人联想到二叉树的中序遍

    2024年02月05日
    浏览(36)
  • 阿里,字节,拼多多,B站挨个面试一遍,你们猜哪个待遇最高?

    我面试的是软件测试岗位,去年中旬的时候从原来的公司离职了,不是工作不好,而是公司发展速度太慢,自己干了几年,也没有太大的成长。以我目前的工作经验和实力,我认为准备一两个月,进大厂不是什么问题。 从11月到12月,总共面了4家公司,阿里,字节,拼多多,

    2023年04月26日
    浏览(54)
  • 52个AIGC视频生成算法模型介绍

    基于Diffusion模型的AIGC生成算法日益火热,其中文生图,图生图等图像生成技术普遍成熟,很多算法从业者开始从事视频生成算法的研究和开发,原因是视频生成领域相对空白。 AIGC视频算法发展现状 从2023年开始,AIGC+视频的新算法层出不穷,其中最直接的是把图像方面的成果

    2024年03月28日
    浏览(33)
  • Python - Real-ESRGAN 提升图像、视频清晰度 - 最高可达 4 K

    目录 一.引言 二.Real-ESRGAN 理论 1.模型简介 2.经典退化模型 ◆ 退化过程全览 ◆ K - 高斯滤波 ◆ N - 噪声 ◆ ↓r - Resize ◆ jpeg - 压缩 3.高阶退化模型 4.环形和超调伪影 5.网络结构 ◆ ESRGAN 生成器 ◆ U-Net 鉴别器 三.Real-ESRGAN 实战 1.快速体验 2.环境搭建 ◆ Package 安装 ◆ 预训练

    2024年02月04日
    浏览(42)
  • 剑指 Offer 52. 两个链表的第一个公共节点

    🚀 作者简介:一名在后端领域学习,并渴望能够学有所成的追梦人。 🚁 个人主页:不 良 🔥 系列专栏:🛸剑指 Offer  🛹Linux 📕 学习格言:博观而约取,厚积而薄发 🌹 欢迎进来的小伙伴,如果小伙伴们在学习的过程中,发现有需要纠正的地方,烦请指正,希望能够与诸

    2024年02月10日
    浏览(38)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包