牛客SQL大厂面试真题个人题解

已于 2023-10-06 18:24:25 修改
阅读量86 收藏
点赞数
分类专栏： SQL题 文章标签： sql 面试 数据库
于 2023-10-06 17:07:58 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Natsu_natsu/article/details/133614154
版权

一:某音短视频

1.各个视频的平均完播率

描述

用户-视频互动表tb_user_video_log

iduidvideo_idstart_timeend_timeif_followif_likeif_retweetcomment_id
110120012021-10-01  10:00:002021-10-01  10:00:30011NULL
210220012021-10-01  10:00:002021-10-01  10:00:24001NULL
310320012021-10-01  11:00:002021-10-01  11:00:340101732526
410120022021-09-01  10:00:002021-9-01  10:00:42101NULL
510220022021-10-01  11:00:002021-10-01  10:00:30101NULL

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

短视频信息表tb_video_info

idvideo_idauthortagdurationrelease_time
12001901影视302021-01-01 07:00:00
22002901美食602021-01-01 07:00:00
32003902旅游902021-01-01 07:00:00

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

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

注：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。

输出示例：

示例数据的结果如下：

video_idavg_comp_play_rate
20010.667
20020.000

题解: 

select u.video_id,
round(sum(if(end_time-start_time >= v.duration,1,0)) / count(u.video_id),3) as avg_comp_play_rate
from tb_user_video_log u,tb_video_info v
where u.video_id = v.video_id
and year(start_time) = 2021
group by u.video_id
order by avg_comp_play_rate desc

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

描述

用户-视频互动表tb_user_video_log

iduidvideo_idstart_timeend_timeif_followif_likeif_retweetcomment_id
110120012021-10-01 10:00:002021-10-01 10:00:30011NULL
210220012021-10-01 10:00:002021-10-01 10:00:21001NULL
310320012021-10-01 11:00:502021-10-01 11:01:200101732526
410220022021-10-01 11:00:002021-10-01 11:00:30101NULL
510320022021-10-01 10:59:052021-10-01 11:00:05101NULL

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

短视频信息表tb_video_info

idvideo_idauthortagdurationrelease_time
12001901影视302021-01-01 07:00:00
22002901美食602021-01-01 07:00:00
32003902旅游902021-01-01 07:00:00

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

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

注：

  • 播放进度=播放时长÷视频时长*100%，当播放时长大于视频时长时，播放进度均记为100%。
  • 结果保留两位小数，并按播放进度倒序排序。

输出示例：

示例数据的输出结果如下：

tagavg_play_progress
影视90.00%
美食75.00%

题解:

select tag,concat(avg_play_progress,'%') as avg_play_progress from(
select
    tag,
        round(
            avg(
                if (
                    TIMESTAMPDIFF (second, start_time, end_time) >= duration,
                    1,
                    TIMESTAMPDIFF (second, start_time, end_time) / duration
                )
            ) * 100,
            2
        ) as avg_play_progress
from
    tb_user_video_log
    join tb_video_info t1 using (video_id)
group by
    tag
having
    avg_play_progress > 60) t1
order by
    avg_play_progress desc

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

描述

用户-视频互动表tb_user_video_log

iduidvideo_idstart_timeend_timeif_followif_likeif_retweetcomment_id
110120012021-10-01 10:00:002021-10-01 10:00:20011NULL
210220012021-10-01 10:00:002021-10-01 10:00:15001NULL
310320012021-10-01 11:00:502021-10-01 11:01:150101732526
410220022021-09-10 11:00:002021-09-10 11:00:30101NULL
510320022021-10-01 10:59:052021-10-01 11:00:05100NULL

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

短视频信息表tb_video_info

idvideo_idauthortagdurationrelease_time
12001901影视302021-01-01 07:00:00
22002901美食602021-01-01 07:00:00
32003902旅游902020-01-01 07:00:00

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

问题：统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据）中，每类视频的转发量和转发率（保留3位小数）。

注：转发率＝转发量÷播放量。结果按转发率降序排序。

输出示例：

示例数据的输出结果如下

tagretweet_cutretweet_rate
影视20.667
美食10.500

解释：

由表tb_user_video_log的数据可得，数据转储当天为2021年10月1日。近30天内，影视类视频2001共有3次播放记录，被转发2次，转发率为0.667；美食类视频2002共有2次播放记录，1次被转发，转发率为0.500。

题解:

select
    tag,
    sum(if_retweet) as retweet_cut,
    round(sum(if_retweet) / count(u.video_id), 3) as retweet_rate
from
    tb_user_video_log u,
    tb_video_info v
where
    u.video_id = v.video_id
    and DATEDIFF (
        DATE (
            (
                select
                    max(start_time)
                FROM
                    tb_user_video_log
            )
        ),
        DATE (u.start_time)
    ) <= 29
group by
    tag
order by
    retweet_cut desc

4.每个创作者每月的涨粉率及截止当前的总粉丝量

描述

用户-视频互动表tb_user_video_log

iduidvideo_idstart_timeend_timeif_followif_likeif_retweetcomment_id
110120012021-09-01 10:00:002021-09-01 10:00:20011NULL
210520022021-09-10 11:00:002021-09-10 11:00:30101NULL
310120012021-10-01 10:00:002021-10-01 10:00:20111NULL
410220012021-10-01 10:00:002021-10-01 10:00:15001NULL
510320012021-10-01 11:00:502021-10-01 11:01:151101732526
610620022021-10-01 10:59:052021-10-01 11:00:05200NULL

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

短视频信息表tb_video_info

idvideo_idauthortagdurationrelease_time
12001901影视302021-01-01 07:00:00
22002901美食602021-01-01 07:00:00
32003902旅游902020-01-01 07:00:00
42004902美女902020-01-01 08:00:00

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

问题：计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量

注：

  • 涨粉率=(加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序。
  • if_follow-是否关注为1表示用户观看视频中关注了视频创作者，为0表示此次互动前后关注状态未发生变化，为2表示本次观看过程中取消了关注。

输出示例：

示例数据的输出结果如下

authormonthfans_growth_ratetotal_fans
9012021-090.5001
9012021-100.2502

解释：

示例数据中表tb_user_video_log里只有视频2001和2002的播放记录，都来自创作者901，播放时间在2021年9月和10月；其中9月里加粉量为1，掉粉量为0，播放量为2，因此涨粉率为0.500（保留3位小数）；其中10月里加粉量为2，掉份量为1，播放量为4，因此涨粉率为0.250，截止当前总粉丝数为2。

题解 

--if_follow为2时表示用户取消关注

select
    author,
    substr(end_time, 1, 7) as month,
    round(
        (
            sum(if (if_follow = 1, 1, 0)) - sum(if (if_follow = 2, 1, 0))
        ) / count(u.video_id),
        3
    ) as fans_growth_rate,
    sum(
        sum(
            case
                when if_follow = 1 then 1
                when if_follow = 2 then -1
                else 0
            end
        )
    ) over (
        partition by
            author
        order by
            substr(end_time, 1, 7)
    ) as total_fans
from
    tb_user_video_log u,
    tb_video_info v
where
    u.video_id = v.video_id
    and year(end_time) = 2021
group by
    author,
    month
order by
    author,
    total_fans

Natsu爱学习
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
牛客-SQL篇-SQL大厂面试真题-01某音短视频-某音短视频-SQL156各个视频的平均完播率
SunshineLu2019的博客
11-28 822
用户-视频互动表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年里
牛客SQL大厂面试真题之用户增长场景
m0_69435474的博客
05-03 1542
牛客SQL大厂面试真题之用户增长场景(某度信息流)，教会你如何用SQL解决连续登录签到问题、留存率问题、用户分层问题、同时最大在线人数等常考点。
牛客在线编程（SQL大厂面试真题）
qq_62773260的博客
12-03 581
牛客真题模拟（会员限免）（douyin真题）
大厂SQL面试题，连续时间问题
weixin_44116269的博客
07-08 2260
今天分享一个非常常考，同时也不太容易的sql题目类型，求连续时间问题。 这类题的核心是：分组排序，用时间减去排序，如果连续的话他们的差会是相同值 记住下面的表，按照提供的三步思路理清楚里面的数据逻辑，连续时间问题再不会难到你 思路： 求解连续天数是面试时非常常考的问题，这类问题有非常巧妙的统一解法，分为3步： 1、为日期排序 row number() over (partition by use_id order by date) as rank 2、求日期和排序的差值（diff） 因为日期存在周期
牛客SQL题解 – 从titles表获取按照title进行分组
12-14
题目描述 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。 CREATE TABLE IF NOT EXISTS “titles” ( `emp_no` int(11) NOT NULL, `title` varchar(50) NOT NULL, ...
牛客SQL刷题 Day-07
12-14
目录 1、获取select * from employees对应的执行计划 2、将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分 3、创建一个actor表，包含如下列信息 4、对于表actor批量插入如下...
牛客AI模拟面试1测开岗
01-06
需要的知识 软件测试基础理论知识 黑盒，白盒 编程语言基础 C/C++ java python等 自动化测试工具 Selenium Appium Robotium 计算机基础知识 数据库、Linux、计算机网络 测试框架 ...一般是开发用的比较多，动手能力强的...
SQL性能优化策略
modelsetget的博客
07-08 489
你不得不知的SQL调优策略笔记
F4搜索帮助和按条件写sql
m0_60109224的博客
07-08 273
2.按条件读取刚刚写的sql数据，if sy-subrc 中可以继续读。3.搜索帮助用函数和sql实现。4.根据屏幕输入数据筛选实现。
如何解决 PostgreSQL 中由于索引不当导致的性能下降问题？
最新发布
技术笔记
07-12 452
比如说，有一个订单表，其中“订单日期”这个列经常被用于按时间范围查询订单，但却没有为其创建索引，这就会导致数据库在查询时需要遍历整个表，极大地降低了查询速度。命令，我们可以查看查询的执行计划。再举一个例子，如果我们发现某个表上存在多个类似的索引，比如“idx_age_1”和“idx_age_2”，并且它们的定义几乎相同，这时候就可以考虑删除其中一个冗余的索引，以减少维护成本和提高性能。然而，就像任何复杂的系统一样，它也可能会遇到性能方面的挑战，其中由于索引不当导致的性能下降问题是比较常见且令人头疼的。
[高频 SQL 50 题(基础版)]第一千七百五十七题，可回收且低脂产品
weixin_45201305的博客
07-10 258
low_fats 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。recyclable 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。没什么难度，作为基础题五十题的第一题练手，现在也开始陆续写一些SQL，作为记录，和leetcode一起，大家都加油。编写解决方案找出既是低脂又是可回收的产品编号。是该表的主键（具有唯一值的列）。
SQL面试题-留存率计算
xiao4816的博客
07-07 326
计算的是整段时间范围内，每一天为基准的所有的留存1、2、7天的用户数。计算的是用户首次登陆时间为基准时间，计算该基准时间之后的n日留存率。方法一的优势是可以一次性计算出，每天的不同时间范围的留存率。缺点：如果要计算n天留存需要增加代码量。但是不是很直观，并且计算量比较大。优点：代码直观好理解。
SQL Server设置端口：跨平台指南
招风的黑耳CSDN
07-09 587
在使用SQL Server时，设置或修改其监听的端口是确保数据库服务安全访问和高效管理的重要步骤。由于SQL Server可以部署在多种操作系统上，包括Windows、Linux和Docker容器等，因此设置端口的步骤和方法也会因平台而异。本文将为您提供一个跨平台的指南，帮助您在不同环境下设置SQL Server的端口。
数据如何查询
qq_46863837的博客
07-08 1223
只有学会DQL，才是真正的进入数据库的世界
怎样优化 PostgreSQL 中对 XML 数据的存储和查询？
技术笔记
07-08 875
在 PostgreSQL 中处理 XML 数据时，为了实现高效的存储和查询，需要采取一系列的优化策略。以下将详细探讨如何优化 PostgreSQL 中对 XML 数据的存储和查询，并提供相应的解决方案和示例代码。
PostgreSQL 中如何实现数据的增量更新和全量更新的平衡？
技术笔记
07-09 940
在数据库管理中，数据的更新操作是常见的任务。对于大型数据集或高并发的系统，选择合适的更新策略至关重要。增量更新和全量更新是两种常见的数据更新方式，如何在 PostgreSQL 中平衡这两种更新方式以确保数据的一致性、性能和可靠性是一个值得深入探讨的问题。
在 PostgreSQL 里如何处理数据的版本跟踪和回滚？
技术笔记
07-08 1036
在数据库管理中，数据的版本跟踪和回滚是非常重要的功能，有助于在数据操作出现错误或需要回滚到特定状态时进行有效的处理。PostgreSQL 提供了几种方法来实现数据的版本跟踪和回滚，包括事务、保存点、版本控制扩展等。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值