草庐IT

牛客SQL刷题第三趴——SQL大厂面试真题

徐若离 2023-03-28 原文

01 某音短视频

SQL156 各个视频的平均完播率

【描述】用户-视频互动表tb_user_video_log。（uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID）

短视频信息表tb_video_info。（video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长（秒）, release_time-发布时间）

【问题】计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序

注：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。
select t.video_id,
ROUND(sum(if((t.end_time-t.start_time)>=t1.duration,1,0))/count(start_time),3) as avg_comp_play_rate
from tb_user_video_log t 
left join tb_video_info t1
on t.video_id=t1.video_id
where year(t.start_time)='2021'
group by t.video_id
order by avg_comp_play_rate desc;

 解题思路：①、考察多表结合。

②、视频完播率的计算方法。

第1步：先计算完成播放次数。sum(if((t.end_time-t.start_time)>=t1.duration,1,0

第2步：计算总播放次数。count(start_time)

第3步：保存三位小数。使用ROUND（xx，3）

③、where子条件，筛选2021年，使用year函数

④、order by排序，按照完播率降序，使用desc。

 

SQL157 平均播放进度大于60%的视频类别

【描述】同上题

问题：计算各类视频的平均播放进度，将进度大于60%的类别输出。

注：播放进度=播放时长÷视频时长*100%，当播放时长大于视频时长时，播放进度均记为100%。

结果保留两位小数，并按播放进度倒序排序。

select t1.tag,
concat(ROUND(avg(if(timestampdiff(second,t.start_time,t.end_time)>=t1.duration,1,
             timestampdiff(second,t.start_time,t.end_time)/t1.duration))*100,2),'%') as avg_play_progress
from tb_video_info t1
join tb_user_video_log t
on t1.video_id=t.video_id
group by t1.tag
having avg_play_progress>60
order by avg_play_progress desc;

解题思路：

1、播放进度结算

①、时间差函数，timestampdiff（unit，char（begin-end））

②、判断，if或者用case when

③、结果保留两位小数，使用round（xx，2）

④、使用concat拼接数值和“%”

2、多表的内连接。

3、进度大于60%的类别输出，因为这个数值是聚合函数，需要使用having函数

4、并按播放进度倒序排序。使用order by desc

SQL158 每类视频近一个月的转发量/率

【描述】用户-视频互动表tb_user_video_log。（uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID）

短视频信息表tb_video_info。（video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长, release_time-发布时间）

问题：统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据）中，每类视频的转发量和转发率（保留3位小数）。
注：转发率＝转发量÷播放量。结果按转发率降序排序。
select t1.tag,
sum(t.if_retweet) as retweet_cut,-- 转发量
round(sum(t.if_retweet)/count(t.video_id),3) as retweet_rate
from tb_video_info t1
left join tb_user_video_log t
on t.video_id=t1.video_id
where date(start_time)>(select date_sub(MAX(start_time),interval 30 day) from tb_user_video_log)
group by t1.tag
order by retweet_rate desc;

解题思路：

转发量：sum(if_reweet)

转发率：转发量/播放量。也就是sum(if_reweet)/count(video_id)。结果保留3位小数，就是用ROUND（转发率，3）即可

最近30天：

写法①、date_sub函数（时间向前推移函数）

(select date_sub(MAX(start_time),interval 30 day) from tb_user_video_log   ------这一步的意思是以最新的播放日期为准，向前推移30天。

写法②、timestampdiff函数（时间差函数）

timestampdiff(date,t.start_time,(select max(t.start_time) from tb_user_video_log)<30

 

 

mdash真题spancolorstyleMySQL

有关牛客SQL刷题第三趴——SQL大厂面试真题的更多相关文章

  1. 【Java 面试合集】HashMap中为什么引入红黑树，而不是AVL树呢 - 2

    HashMap中为什么引入红黑树，而不是AVL树呢1.概述开始学习这个知识点之前我们需要知道，在JDK1.8以及之前，针对HashMap有什么不同。JDK1.7的时候，HashMap的底层实现是数组+链表JDK1.8的时候，HashMap的底层实现是数组+链表+红黑树我们要思考一个问题，为什么要从链表转为红黑树呢。首先先让我们了解下链表有什么不好？？？2.链表上述的截图其实就是链表的结构，我们来看下链表的增删改查的时间复杂度增：因为链表不是线性结构，所以每次添加的时候，只需要移动一个节点，所以可以理解为复杂度是N(1)删：算法时间复杂度跟增保持一致查：既然是非线性结构，所以查询某一个节点的时候

  2. Hive SQL 五大经典面试题 - 2

    目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类

  3. 牛客网专项练习30天Pytnon篇第02天 - 2

    1.在Python3中，下列关于数学运算结果正确的是:（B）a=10b=3print(a//b)print(a%b)print(a/b)A.3，3，3.3333...B.3，1，3.3333...C.3.3333...，3.3333...，3D.3.3333...，1，3.3333...解析：    在Python中，//表示地板除（向下取整），%表示取余，/表示除（Python2向下取整返回3）2.如下程序Python2会打印多少个数:（D）k=1000whilek>1:    print(k)k=k/2A.1000 B.10C.11D.9解析：    按照题意每次循环K/2，直到K值小于等

  4. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  5. sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2

    我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有

  6. ruby - 尝试比较两个文本文件，并根据信息创建第三个 - 2

    我有两个文本文件，master.txt和926.txt。如果926.txt中有一行不在master.txt中，我想写入一个新文件notinbook.txt。我写了我能想到的最好的东西，但考虑到我是一个糟糕的/新手程序员，它失败了。这是我的东西g=File.new("notinbook.txt","w")File.open("926.txt","r")do|f|while(line=f.gets)x=line.chompifFile.open("master.txt","w")do|h|endwhile(line=h.gets)ifline.chomp!=xputslineendende

  7. ruby - 防止SQL注入(inject)/好的Ruby方法 - 2

    Ruby中防止SQL注入(inject)的好方法是什么？ 最佳答案 直接使用ruby？使用准备好的语句:require'mysql'db=Mysql.new('localhost','user','password','database')statement=db.prepare"SELECT*FROMtableWHEREfield=?"statement.execute'value'statement.fetchstatement.close 关于ruby-防止SQL注入(inject

  8. ruby-on-rails - 如何在 Rails 中的不同数据库上执行直接 SQL 代码 - 2

    我正在编写一个Rails应用程序，它将监视某些特定数据库的数据质量。为了做到这一点，我需要能够对这些数据库执行直接SQL查询——这当然与用于驱动Rails应用程序模型的数据库不同。简而言之，这意味着我无法使用通过ActiveRecord基础连接的技巧。我需要连接的数据库在设计时是未知的(即:我不能将它们的详细信息放在database.yaml中)。相反，我有一个模型“database_details”，用户将使用它来输入应用程序将在运行时执行查询的数据库的详细信息。因此与这些数据库的连接实际上是动态的，细节仅在运行时解析。 最佳答案

  9. 华为OD机试真题 C++ 实现【带传送阵的矩阵游离】【2023 Q2 | 200分】 - 2

            所有题目均有五种语言实现。C实现目录、C++实现目录、Python实现目录、Java实现目录、JavaScript实现目录题目n行m列的矩阵，每个位置上有一个元素你可以上下左右行走，代价是前后两个位置元素值差的绝对值.另外，你最多可以使用一次传送阵(只能从一个数跳到另外一个相同的数)求从走上角走到右下角最少需要多少时间。输入描述：第一行两个整数n,m，分别代表矩阵的行和列。后面n行，每行m个整数，分别代表矩阵中的元素。输出描述：一个整数，表示最少需要多少时间。

  10. 西安华为OD面试体验 - 2

    西安华为OD面试体验开始投简历技术面试进展工作进展开始投简历去年一整年一直在考研和工作之间纠结，感觉自己的状态好像当时的疫情一样差劲。之前刚毕业的时候投了个大厂的简历，结果一面写算法的时候太拉跨了，虽然知道时dfs但是代码熟练度不够，放在平时给足时间自己可以调试通过，但是熟练度不够那面试当时就写不出来被刷了。说真的算法学到后期我感觉最重要的是熟练度和背板子（对于我这种普通玩家来说），面试题如果一上来短时间内想不出思路就完蛋了。然后由于当时找的工作不是很理想就又想考研了。但是考研是有风险的，我自我感觉自己可能冲不上那个学校，而找工作一个没成可以继续找嘛。本着抱着试试看的态度在boss上投了简历，

随机推荐