本文基于前段時間學習總結的 MySQL 相關的查詢語法，在牛客網找了相應的 MySQL 題目進行練習，以便加強對于 MySQL 查詢語法的理解和應用。

由于涉及到的數據庫表較多，因此本文不再展示，只提供 MySQL 代碼與示例輸出。

以下內容是牛客題霸-SQL大廠面試真題（抖音短視頻 1-6 題、百度信息流 1-5 題）的 MySQL 代碼答案。

SQL 156：查詢 2021 年里有播放記錄的每個視頻的完播率（結果保留三位小數），并按照完播率降序排列

select b.video_id,
round(avg(if(timestampdiff(second, start_time, end_time) >= duration, 1, 0)), 3) as avg_comp_play_rate
from tb_user_video_log a
left join tb_video_info b
on a.video_id = b.video_id
where year(start_time) = 2021
group by 1
order by 2 desc

SQL 157：查詢各類視頻的平均播放進度，將進度大于 60% 的類別輸出（結果保留兩位小數），并按照播放進度降序排列

select tag,
concat(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 a
join tb_video_info b
on a.video_id = b.video_id
group by 1
having avg(if(timestampdiff(second, start_time, end_time) >= duration, 1, 
timestampdiff(second, start_time, end_time)/duration)) > 0.6
order by 2 desc

SQL 158：查詢在有用戶互動的最近一個月（按包含當天在內的近30天算，比如10月31日的近30天為10.2~10.31之間的數據）中，每類視頻的轉發量和轉發率（保留3位小數），并按照轉發率降序排列

select tag,
sum(if_retweet) as retweet_cut,
round(sum(if_retweet)/count(a.video_id), 3) as retweet_rate
from tb_user_video_log a
join tb_video_info b
on a.video_id = b.video_id
where datediff((select max(start_time) from tb_user_video_log), start_time) <= 29
group by 1
order by 3 desc

SQL 159：查詢 2021 年里每個創作者每月的漲粉率及截止當月的總粉絲量，并按照創作者 ID，總粉絲量升序排列

select author, left(start_time, 7) as month,
round(sum(follow_fans_change)/count(1), 3) as fans_growth_rate,
sum(sum(follow_fans_change)) over(partition by author order by left(start_time, 7)) as total_fans
from(select b.video_id, author, start_time,case when if_follow = 1 then 1when if_follow = 2 then -1when if_follow = 0 then 0else -1000 end as follow_fans_changefrom tb_user_video_log ajoin tb_video_info bon a.video_id = b.video_id
) c
where year(start_time) = 2021
group by 1, 2
order by 1, 4

SQL 160：查詢 2021 年國慶頭 3 天每類視頻每天的近一周總點贊量和一周內最大單天轉發量，并按照視頻類別降序排列，日期升序排列

with a as(select tag, left(start_time, 10) as dt,sum(if_like) as like_cnt,sum(if_retweet) as retweet_cntfrom tb_user_video_log t1left join tb_video_info t2on t1.video_id = t2.video_idgroup by 1, 2
),
b as(select tag, dt,sum(like_cnt) over(partition by tag rows between 6 preceding and current row) as sum_like_cnt_7d,max(retweet_cnt) over(partition by tag rows between 6 preceding and current row) as max_retweet_cnt_7dfrom a
)select tag, dt, sum_like_cnt_7d, max_retweet_cnt_7d
from b
where dt in('2021-10-01', '2021-10-02', '2021-10-03')
order by 1 desc, 2

SQL 161：查詢近一個月發布的視頻中熱度最高的 top3 視頻

select video_id,
round((avg(if_complete) * 100 + sum(if_like) * 5 + sum(if_comment) * 3 + sum(if_retweet) * 2) * (1 / (1 + min(diff_time)))) as hot_index
from(selecta.video_id as video_id,if(timestampdiff(second, start_time, end_time) >= duration, 1, 0) as if_complete,if_like, if_retweet,if(comment_id is null, 0, 1) as if_comment,datediff((select max(end_time) from tb_user_video_log), end_time) as diff_timefrom tb_user_video_log aleft join tb_video_info bon a.video_id = b.video_idwhere datediff((select max(end_time) from tb_user_video_log), release_time) <= 29
) c
group by 1
order by 2 desc
limit 3

SQL 162：查詢 2021 年 11 月每天的人均瀏覽文章時長（秒數），結果保留 1 位小數，并按時長由短到長升序排列

select left(in_time, 10) as dt,
round(sum(timestampdiff(second, in_time, out_time)) / count(distinct uid), 1) as avg_viiew_len_sec
from tb_user_log
where left(in_time, 7) = '2021-11' and artical_id <> 0
group by 1
order by 2

SQL 163：查詢每篇文章同一時刻最大在看人數，如果同一時刻有進入也有離開時，先記錄用戶數增加再記錄減少，結果按最大人數降序排列

with a as(select uid, artical_id, in_time as dt, 1 as is_infrom tb_user_logunion allselect uid, artical_id, out_time as dt, -1 as is_infrom tb_user_log
)select artical_id, max(uv)
from(select artical_id, dt, sum(is_in) over(partition by artical_id order by dt, is_in desc) as uvfrom awhere artical_id <> 0
) b
group by 1
order by 2 desc

SQL 164：統計2021年11月每天新用戶的次日留存率（保留2位小數）

with reg as(select uid, min(left(in_time, 10)) as reg_datefrom tb_user_loggroup by 1
), -- 用戶注冊表
log as(select uid, date(in_time) as log_datefrom tb_user_logunionselect uid, date(out_time) as log_datefrom tb_user_log
) -- 用戶登陸表select
reg_date as dt,
round(ifnull(count(l.uid)/count(r.uid), 0), 2) as uv_left_rate
from reg r
left join log l
on r.uid = l.uid
and r.reg_date = date_sub(l.log_date, interval 1 day)
where left(reg_date, 7) = '2021-11'
group by 1
order by 1

SQL 165：統計活躍間隔對用戶分級后，各活躍等級用戶占比，結果保留兩位小數，且按占比降序排列

with a as(selectuid,date(min(in_time)) as first_date, -- 用戶注冊日期date(max(in_time)) as last_date, -- 用戶最近活躍日期(select date(max(in_time)) from tb_user_log) as todayfrom tb_user_loggroup by 1
)select
user_grade,
round(count(distinct uid) / (select count(distinct uid) from tb_user_log), 2) as ratio
from
(select uid,case when datediff(today, first_date) <= 6 then '新晉用戶'when datediff(today, first_date) > 6 and datediff(today, last_date) <= 6 then '忠實用戶'when datediff(today, first_date) > 6 and datediff(today, last_date) > 29 then '流失用戶'when datediff(today, first_date) > 6 and datediff(today, last_date) > 6 then '沉睡用戶'else '其他' end as user_gradefrom a
) b
group by 1

SQL 166：統計每天的日活數及新用戶占比

with reg as(select uid, date(min(in_time)) as reg_datefrom tb_user_loggroup by 1
), -- 用戶注冊表
log as(selectuid,date(in_time) as login_datefrom tb_user_logunionselectuid,date(out_time) as login_datefrom tb_user_log
) -- 用戶登陸表select
login_date,
count(distinct l.uid) as dau,
round(count(distinct r.uid) / count(distinct l.uid), 2) as uv_new_ratio
from log l
left join reg r
on l.uid = r.uid
and l.login_date = r.reg_date
group by 1
order by 1