-- 统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据）中，每类视频的转发量和转发率（保留3位小数）。 -- 注：转发率＝转发量÷播放量。结果按转发率降序排序。 -- 1、找到最近的一天日期，30天前的日期 2、过滤最近一个月数据 3、分组统计，转发量求和和，播放量总数 SELECT d.tag, SUM(IF(c.if_retweet = NULL, 0, c.if_retweet)) retweet_num, ROUND(SUM(IF(c.if_retweet = NULL, 0, c.if_retweet)) / COU...

-- 计算各类视频的平均播放进度，将进度大于60%的类别输出。 -- 播放进度=播放时长÷视频时长*100%， -- 1、关联视频信息，计算播放时长 2、先计算每条视频播放进度，用小数表示 3、分类统计播放进度平均数，转换为分数 select e.tag,concat(e.avg_progress,'%') avg_progress_p from ( SELECT d.tag, round(AVG(d.play_progress)*100,2) avg_progress FROM (SELECT *, IF(play_duration >= duration, 1, play_durat...

-- 计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序 -- 完播率=播放完成数/播放总数 -- 1、过滤2021年播放记录 2、判断是否播放完成，新建playFlag标记是否播放完成 3、根据vedio_id分组统计,播放完成数、播放总数 4、计算完播率，排序 SELECT c.video_id, ROUND(SUM(playFlag) / COUNT(c.video_id), 3) complete_rate FROM (SELECT a.video_id, IF((a.end_time - a.start_time) >= b.duration,...

-- 请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷完成数和题目练习数， 只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。 -- 先找到红名大佬uid,再统计试卷完成数和题目练习数 -- 1、用户得分平均值,分组求平均数。先关联查询，再过滤，再分组求平均数，再过滤 2、统计用户试卷完成数 3、统计用户题目练习数 4、左连接查询 SELECT d.uid, IF(e.exam_cnt IS NULL, 0, e.exam_cnt) exam_cnt, IF(f.question_cnt IS NULL, 0, f.quest...

-- 先统计活动一的人员，再统计活动二，再合并 -- 活动1人员，每次试卷得分都能到85分的人，根据人分组，用最低分 -- 活动2人员，至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id，关联，过滤，去重 SELECT uid, 'activity1' FROM exam_record GROUP BY uid HAVING MIN(score) >= 85 UNION ALL SELECT DISTINCT (uid) uid, 'activity2' FROM exam_record a INNER JOIN examination_info b ON a.exam_id...

-- 统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序， -- 1、关联查询 2、过滤 3、分组统计，等级 SELECT c.level, COUNT(1) level_cnt FROM exam_record a INNER JOIN examination_info b ON a.exam_id = b.exam_id INNER JOIN user_info c ON a.uid = c.uid WHERE b.tag = 'SQL' AND a.score > 80 GROUP BY c.level ORDER BY level_cnt DESC;

-- 1、测试记录表关联试卷表查出试卷发布当天的记录，关联人员表 -- 2、过滤条件 -- 3、分组统计，试卷id，分组函数 COUNT(DISTINCT a.uid) uv,SUM(a.score) / COUNT(a.exam_id) SELECT a.exam_id, COUNT(DISTINCT a.uid) uv, ROUND(SUM(a.score) / COUNT(a.exam_id), 1) avg_score FROM exam_record a INNER JOIN examination_info b ON a.exam_id = b.exam_id AND DATE(a...

-- -- 统计出 月均完成试卷数不小于3的用户爱作答的类别及作答次数，按次数降序输出 -- 月均完成试卷数=总的完成试卷数/月数   总的完成试卷数=提交的   分组统计 -- 1、先统计用户月均完成试卷数  先过滤未完成数据  根据人员分组  统计总的完成试卷数 月数   -- 2、过滤不小于3的用户   -- 3、关联两表，作答信息，过滤用户条件&nb...

-- 请统计2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5）， -- 输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合，按未完成试卷数量由多到少排序。 -- 未完成试卷作答数=没有提交数量   完成试卷作答数=已提交数量 -- 1、先关联  2、分组统计 未完成试卷作答数 完成试卷作答数，拼接detail  3过滤有效用户  4过滤大于1的 5输出信息 SEL...

-- 统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt，以及该年的总体情况， -- 月总刷题数   日均刷题数=月总刷题数/月天数   年总刷题数=月总刷题数之和   -- 1、先过滤，获取月份字符列，月天数；2、再对月份分组，统计，计算日均刷题数  3、再汇总，除以31，union拼接 SELECT      st,   &nbsp...

-- 请计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau， -- 平均月活跃天数=总的活跃天数/月度活跃人数 月度活跃人数=提交的人数 总的活跃天数=每个用户活跃天数之和 -- 1、过滤非活跃数据 2、日期字符列 3、月分组 4、统计 COUNT(DISTINCT uid, std_str) 总的活跃天数，用户id和提交日期去重统计 SELECT st_str, ROUND(COUNT(DISTINCT uid, std_str) / COUNT(DISTINCT uid), 2) avg_active_days, COUNT(DISTINC...

-- 找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。 -- 1、关联过滤 2、计算平均值 3、最低得分，大于平均分，取最小的 SELECT MIN(score) FROM exam_record a INNER JOIN examination_info b ON a.exam_id = b.exam_id WHERE tag = 'SQL' AND score IS NOT NULL AND score >= (SELECT AVG(score) FROM exam_record a INNER JOIN examination_info b ON a.exam_id = b...

SELECT COUNT(1) total_pv, COUNT(score) complete_pv, (SELECT COUNT(DISTINCT (exam_id)) FROM exam_record WHERE score IS NOT NULL) complete_exam_pv FROM exam_record;

-- 截断平均值，去除最大值和最小值，再算平均数 -- 1、先关联，内连接 2再过滤，sql和hard,过滤最大值，最小值 3、再算平均分 4、保留一位小数 SELECT tag, difficulty, ROUND(AVG(score), 1) FROM examination_info a INNER JOIN exam_record b ON a.exam_id = b.exam_id WHERE tag = 'SQL' AND difficulty = 'hard' AND score IS NOT NULL AND score NOT IN ((SELECT MAX(b.score)...