涨粉率计算 注意点 涨粉的定义及时点，定义已给出，默认end_time为点击follow的时间点（涨粉的时点） 粉丝增长计算中，2代表-1，if判断即可 当前粉丝量是累计计算，使用sum(粉丝增长量) over (partition by 作者id order 月份) 返回值中Month是带有年份的，因此在按作者id和月份分组计算粉丝增长时，使用max(end_time)保留日期字段；在最后返回时，使用date_format()截取时间即可 最后按author、粉丝总量进行升序 select t.author, date_format(t.dt, '%Y-%m') a...

转发量/率计算 注意点 定义有用户互动的最近一个月，当前最近播放的时间的近一个月，即先查询所有记录中end_time的最大值，然后计算日期差datediff小于30（这里的日期差是包括了当天，因此不是小于等于） select tvi.tag, sum(tuvl.if_retweet) as retweet_cnt, round(sum(tuvl.if_retweet) / count(tuvl.video_id), 3) as retweet_rate from ( select video_id, if_retwe...

平均播放进度 三个注意点 播放进度大于100%时，即播放时长>视频时长，直接等于100% 保留2位，按比率值降序排序 返回结果中出现%，使用concat函数对比率值与符号进行连接 思路：与前一题一样，通过tb_user_video_lag计算每个用户的播放时长，左连接（这里内连接也可以）tb_video_info表取得视频时长，进而计算播放进度以及每个tag的平均播放进度。 select t2.tag, concat(t2.rate,'%') as avg_play_progress from ( select t.tag, ...

完播率计算 难点在于时间差的计算，个人觉得end_time - start_time这种写法存在一定的问题，最好的方式是采用timestampdiff进行计算。 select t.video_id, round(sum(if(t.t_diff >= t.duration, 1, 0)) / count(t.video_id), 3) as avg_comp_play_rate from ( select tvi.video_id, timestampdiff(second, tuvl.start_time, tuvl.end...

原题目 查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。 不要推荐该用户已经喜欢的音乐，并且按music的id升序排列，不包含重复项。 表内容 follow: 关注人user_id, 被关注人follower_id music_likes: user_id, music_id music: id, name 解题点 user_id = 1 推荐user_id = 1的follower_id喜欢的音乐且不推荐user_id = 1喜欢的音乐 music.id升序 select distinct m.music_name from follow as f ...

做的速度有点慢，用了两小时。

第五天

先找到最大值和最小值（用开窗函数排序），剔除后取平均。 select avg(t1.salary) from (select salary, dense_rank() over (order by salary) as minSalary, dense_rank() over (order by salary desc ) as maxSalary from salaries where to_date = '9999-01-01') as t1 where t1.minSalary <> 1 and t1.maxSalary <> 1;

day4