### 整体的思路是新增一列标注为是否是当天的新用户active_user 当天是新用户：1，当天不是新用户：0 ### 每天的日活数 = SUM(active_user) ### 每天的新用户数 = COUNT(active_user) ### 确实是否是当天的新用户时，需要使用窗口函数进行分组比较，而不是使用GROUP BY直接进行分组 # SELECT uid, DATE_FORMAT(in_time,'%Y-%m-%d') AS dt # FROM tb_user_log # UNION # SELECT uid,DATE_FORMAT(out_time,'%Y-%m-%d') AS...

## 思路：分别创建‘每天新用户表’和‘用户活跃表’ -> 使用‘第一天新用户且第二天仍活跃’进行连接，保留相关记录 -> 分组计算留存率 ## 额外的思考：一般不会存在第一天in_time,第三天才out_time的情况，所以把in_time和out_time使用UNION进行去重取并集作为活跃日期是合理的 ## 第一步：每天新用户表 和 用户活跃表 # (SELECT uid,MIN(DATE(in_time)) AS dt # FROM tb_user_log # GROUP BY uid) t1 #每天新用户表 # (SELECT uid,DATE(in_...

# ## 最大同时在线人数：（有一个固定的套路，先记住甚至背下来就行） # ## 第一步 将用户的进入时间单列出来，记为1，将离开时间单列出来记为-1 # ## 第二步 使用窗口函数对计数 1或-1 进行累计求和，但同一时刻有进有出先算进后算出：先按照时间排序，然后按照计数排序 # ## 第三步 在每个分组里面求最大的累计和 就是最多同时在线的人数了 # ## 第一步: 将in_time和out_time统一记为dt列，值分别为1和-1 记为diff列 # SELECT # artical_id,in_time AS dt,1 AS diff # FROM tb_user_log ...

# ### 第一步生成国庆三天及每天往前推7天的日期 + 这10天里每天的like_cnt和retweet_cnt # SELECT # tag, # DATE_FORMAT(start_time,'%Y-%m-%d') AS dt, # SUM(if_like) AS like_cnt, # SUM(if_retweet) AS retweet_cnt # FROM tb_user_video_log a # JOIN tb_video_info b # ON a.video_id = b.video_id # WHERE DATEDIFF('2021-10...

## 涨粉率 = (加粉量 - 掉粉量)/播放量 SELECT author, DATE_FORMAT(start_time,'%Y-%m') AS month, ROUND((SUM(CASE WHEN if_follow = 1 THEN 1 WHEN if_follow = 2 THEN -1 ELSE 0 END ))/COUNT(start_time),3) AS fans_growth_rate, SUM(SUM(CASE WHEN if_follow = 1 THEN 1 WHEN if_follow = 2 THEN -1 ELSE 0...

# SELECT tb_user_video_log # ## uid，video_id,start_time,end_time, if_follow, # start_time, release_time, duration,tag,author,video_id ## 最近一个月：首先倒序排列出最后一个日期(使用窗口函数)：ORDER BY DATE_FORMAT(start_time,'%Y%m%d') LIMIT 1 AS last_time ## 转发率 = 转发量/播放量 ## 播放量：有start_time即认为有1个播放量 ## DATE_SUB(时间，INTERVAL n ...

# SELECT a.video_id,start_time,end_time,tag, # ROUND(AVG(TIMESTAMPDIFF(SECOND,start_time,end_time)/duration *100%), 2) AS avg_play_progress # FROM tb_user_video_log a # LEFT JOIN tb_video_info b # ON a.video_id = b.video_id # GROUP BY tag # HAVING avg_play_progress >60 # ORDER BY avg_play_progres...

## 先增加一列作为是否完播,然后计数：完播的数量/总数量 ## 结束时间-开始时间：TIMESTAMPDIFF(second,start_time,end_time) ## duration >= TIMESTAMPDIFF(second,start_time,end_time) ## IF((end_diff>duration,'%Y%m')1,0) AS end_video SELECT video_id,ROUND(SUM(end_video)/COUNT(end_video),3) AS avg_comp_play_rate FROM( SELECT video...

SELECT exam_id, substring_index(tag, ',', 1) AS tag, substring_index(substring_index(tag, ',', 2), ',', -1) AS difficulty, CAST( substring_index(tag, ',', -1) AS DECIMAL ) AS duration FROM examination_info WHERE tag LIKE '%,%';

## 用户信息表user_info,信息表examination_info,作答记录表exam_record ### SELECT * FROM user_info, examination_info, exam_record, avg_score, nick_name ## 昵称是'^牛客[0-9]+号$' OR '^[0-9]+$' ## 试卷类别是tag LIKE '^[Cc]' ## 已完成的试卷：submit_time IS NOT NULL或者写成 score IS NOT NULL ## SELECT a.uid,a.exam_id,ROUND(AVG(score),0) ...

## lead窗口函数的用法： ## LEAD(字段名，n) OVER() 取值向后偏移n行，即向前推n位，后面有n位的位置空出 ## LAG(字段名，n) OVER() 取值向前偏移n行，即向后推n位，前面有n位的空出 ## LAG(字段名，n，x) OVER()取值向前偏移n行，并将空值填充为数字x ## 更简单的理解：LEAD是带领，现有的去前面带头；LAG是滞后，现有的去后面呆着 ## LEAD/LAG函数可以对现有的列进行前移或后移后生成新列，有两个作用： ## 1.可以做向前或者向后差分：将前移或后移的作为新列，和原来的列进行相减即可 ## 2.可以得出这一列中某个值重复出现次数...

SELECT DISTINCT exam_id,duration,release_time FROM( SELECT exam_id,duration,release_time, SUM(CASE WHEN rank1 = 2 THEN time WHEN rank2 = 2 THEN -time ELSE 0 END) AS sub FROM( SELECT a.exam_id,duration,release_time, TIMESTAMPDIFF(minute,start_time,submit_time) as time, ...

SELECT uid,nick_name,achievement FROM user_info WHERE achievement BETWEEN 1200 AND 2500 AND nick_name LIKE '牛客%号' AND uid IN ( SELECT uid FROM( SELECT uid,start_time AS act_time FROM exam_record UNION ALL SELECT uid,submit_time AS act_time FROM practic...

## 首先判断是否完成： ## 记录未完成和完成的用时：IF(submit_time IS NULL, duration, timestampdiff(minute,start_time,submit_time)) AS new_time ## 记录未完成和完成的分数：IF(score IS NULL, 0, score) AS new_score ## 计算平均用时和平均分数：AVG(new_score) AS avg_score & AVG(new_time) AS avg_time_took # SELECT a.uid,a.exam_id,a.start_time,a.su...