# 首先根据定义，今天是out_time的最大值 # 新增一个字段：每个用户的最早登录日期 # 新晋用户：用户的最早登录日期在近七天内 0 # 忠实用户：用户的最早登录日期在七天前，而且近七天内活跃过 1 # 流失用户：用户的最早登录日期在三十天前，但近三十天内没有活跃过 2 # 沉睡用户：用户的最早登录日期在七天前，但近七天内没有活跃过 3 WITH temp_0 AS( # 临时表0，查询每条记录的uid，当天日期，今天，每个用户的首次活跃日期 SELECT uid, DATE_FORMAT(out_time, '%Y-%m-%d') date_time, # 记录当天日期...

# 将进入和离开时间格式化，然后像上一题那样提取组合，再去重，因为只涉及两天跨天的情况，所以这样就能得到每个用户的活跃日期 # 然后在每个用户维度使用lag/lead函数判断有没有在第二天又活跃 # 根据时间排序。新增一列是每个用户最开始活跃日期 # 如果开始日期和下一个日期都存在，并且开始日期等于首次活跃日期，那就说明新用户次日留存了，设为1，否则为0，还有再设一列是新用户登录记录 # 两列聚合后相除 # 创建临时表 WITH temp_0 AS( # 查询uid，开始日期， # 使用LEAD开窗函数针对每个用户分页，使用日期正序，得到开始日期的下一个日期， # 使用MIN开窗聚合函...

# 对进入时间进行编码为1，代表进入 # 同理，对离开时间编码为-1，代表离开 # 这样得到每一时刻的进入和离开情况表 # 然后计算每一时刻最大在看人数，先加后减，开窗函数（文章id分页，按时间和进出排序） # 最后以文章id分组，取最大在看人数最大值 # 最外层查询，文章id和最大在看人数值 SELECT artical_id, MAX(cnt) max_uv FROM ( # 子查询1 查询文章id，时间，对进入情况进行累加 SELECT artical_id, _time, SUM(come) OVER(PARTITION BY artical_id ORDER BY _t...

# 计算每条记录的时长 # 计算每天有几个人 # 按天分组 # 查询天，平均浏览时长 SELECT dt, ROUND(SUM(sec)/COUNT(DISTINCT uid), 1) avg_viiew_len_sec FROM ( # 子查询，查询uid，浏览时长、日期格式化 SELECT uid, TIMESTAMPDIFF(SECOND, in_time, out_time) sec, DATE_FORMAT(in_time, '%Y-%m-%d') dt FROM tb_user_log # 加限制条件是说当id为0的时候是在非浏览状态下的记录，不算浏览 ...

# 根据最近播放日期限制最近一个月的视频 # 计算热度，需要先计算视频完播率和新鲜度 # 完播率：完成播放次数/被播放次数 完成播放：实际时长大于等于理论时长 被播放：有记录 # 新鲜度：1/最近无播放天数+1 最近无播放天数：最近播放日期往前推最近的播放日期的间隔天数 # 点赞数、转发数：SUM # 评论数：COUNT # 创建临时表，提取视频id，实际播放时长，理论播放时长，播放完日期，是否点赞，是否转发，评论id，全局最大的日期，每个视频的最大日期，视频发布日期 # 排序 WITH temp_0 AS( SELECT video_id, TIMESTAMPDIFF(SE...

# 2021年的国庆前三天的每类视频，这三天中每一天的近一周总点赞数和一周内最大单天转发数 # 先根据限制条件筛选数据，2021年国庆前三天的，还有国庆前一周的，到9.25 # 再得到每类视频每一天的点赞数和转发数(求和即可) # 总点赞数由累加开窗函数的rows控制窗口是7天，单天最大使用最大函数和控制窗口 WITH temp_0 AS ( SELECT tag, DATE_FORMAT(end_time, "%Y-%m-%d") dt, SUM(if_like) like_cnt, SUM(if_retweet) retweet_cnt FR...

# 计算每月的涨粉情况，总粉丝量就是累加窗口 # 创建临时表，查询必要的用户，年月，涨粉情况——1，-1,0 WITH temp_0 AS ( SELECT author, DATE_FORMAT(start_time, '%Y-%m') the_month, CASE WHEN if_follow = 1 THEN 1 WHEN if_follow = 0 THEN 0 ELSE -1 END fo_count FROM tb_video_info JOIN tb_user_video_log...

# 查询用户最近活跃的日期，计算目标区间 # 分组计算每类的转发量和播放量，最终计算出转发率 # 临时表，查询出用户最近活跃的日期，只要有记录就是播放，就是活跃 # 往前找30天，使用DATE_SUB函数 WITH temp_0 AS( SELECT MAX(end_time) last_time, DATE_SUB(MAX(end_time), INTERVAL 30 DAY) first_time FROM tb_user_video_log ) # 查询类型，转发总数，转发率 SELECT tag, SUM(if_retweet) re_count, ROUND(SUM(if_ret...

# 先计算出每条记录的播放时长（秒） # 根据播放时长和对应的视频时长，计算百分比（大于的是100%） # 分类别平均百分比 SELECT tag, CONCAT(ROUND(AVG(cost_percent) * 100,2), '%') avg_play_progress FROM( SELECT a.uid, a.video_id, b.tag, b.duration, IF(TIMESTAMPDIFF(SECOND, a.start_time, a.end_time)>=b.duration, 1, TIMESTAMPDIFF(SECOND, a.start_time,...

SELECT uid, CASE WHEN CHAR_LENGTH(nick_name) > 13 THEN CONCAT(SUBSTRING(nick_name, 1, 10), '...') ELSE nick_name END FROM( SELECT uid, nick_name FROM user_info WHERE CHAR_LENGTH(nick_name) > 10 ) a CHAR_LENGTH函数返回字符串中的字符数量.LENGTH函数返回的是字符串的字节长度，而不是字符数.CONCAT函数拼接字符串

SELECT exam_id, SUBSTRING_INDEX(tag, ',', 1) AS tag, SUBSTRING_INDEX(SUBSTRING_INDEX(tag, ',', 2), ',', -1) AS difficulty, SUBSTRING_INDEX(tag, ',', -1) AS duration FROM( SELECT exam_id, tag, difficulty, duration FROM examination_info WHERE duration = 0 ) a 当然，我很乐意为您介绍SU...

# 找到求职方向为算法工程师，且注册当天就完成了算法类试卷的人， UID # 按参加过的所有考试最高得分排名 根据这些人的最高得分排序 # 取出第三页，也就是7-9条记录 SELECT uid, level, register_time, max_score FROM ( # 查询必要的字段，还有每个用户的历史最高的得分 SELECT uid, level, b.register_time,job,submit_time,tag, MAX(score) OVER(PARTITION BY uid) max_score FROM exam_record a JOIN...

# 先找到有记录的，新增一个得分等级字段 # 再按用户等级和得分等级分组，统计占比 # 暴力法，考虑所有等级情况，分别计数 WITH temp_0 AS( SELECT uid, exam_id, score, level, # 使用case when语句为不同区间赋值 CASE WHEN score >= 90 THEN '优' WHEN score < 90 AND score >= 75 THEN '良' WHEN score < 75 AND score >= 60 THEN '中' ELSE ...

# 把 当有任意一个0级用户未完成试卷数大于2 作为一个条件，临时表，如果有，那统计数量是大于0的 # 总体上输出全部用户的指标，如果是第一种情况，就只筛选出等级为0的 # 统计是否有满足条件的 WITH temp_0 AS( SELECT uid FROM user_info LEFT JOIN exam_record USING(uid) WHERE level = 0 # 选择0级用户 GROUP BY uid HAVING SUM(IF(score, 0, 1)) > 2 # 未完成的记录数大于2 ) SELECT uid, # 未完成数计算，这里要判断是否有开始的记...