SQL刷题记录三

题目6：SQL1 各个视频的平均完播率

链接：各个视频的平均完播率_牛客题霸_牛客网 (nowcoder.com)

题目解析：

1. 先理解题目意思：有播放记录的视频的id号和其完播率。条件是：2021年和根据其完播率降序排序。
2. 拆解复杂指标：这里需要拆解的是完播率这个指标 

   

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

 

题目7：SQL30 统计每种性别的人数

链接：统计每种性别的人数_牛客题霸_牛客网 (nowcoder.com)

题目解析:使用substring_index(str,delim,count)

select substring_index(profile,',',-1) as gender,
        count(*) as number
from user_submit
group by gender;

题目8：SQL32 截取出年龄

链接：截取出年龄_牛客题霸_牛客网 (nowcoder.com)

题目解析:先用substring_index(profile, ',', -2)取出27,male，

                再用substring_index(profile, ',', 1)取出27

select
    substring_index(substring_index(profile, ',', -2),',',1) as age,
    count(device_id) as number
from user_submit
group by age