题意明确： 每个学校的用户平均答题数量  问题分解：  限定条件：无； 每个学校：按学校分组，group by university 平均答题数量：在每个学校的分组内，用总答题数量除以总人数即可得到平均答题数量count(question_id) / count(distinct device_id)。 表连接：学校和答题信息在不同的表，需要做连接   细节问题：  表头重命名：as  完整代码： select university, count(question_id) / count(distinct qpd.device_id) as avg_answer_cnt from questi...

题意明确： 所有来自浙江大学的用户题目回答明细情况  问题分解：  限定条件：来自浙江大学的用户，学校信息在用户画像表，答题情况在用户练习明细表，因此需要通过device_id关联两个表的数据； 方法1：join两个表，用inner join，条件是on up.device_id=qpd.device_id and up.university='浙江大学' 方法2：先从画像表找到浙江大学的所有学生id列表where university='浙江大学'，再去练习明细表筛选出id在这个列表的记录，用where in   完整代码： select qpd.device_id, qpd.questio...

题意明确： 不同大学的用户平均发帖情况，并按照平均发帖情况进行升序排列  问题分解：  限定条件：无； 不同大学：按学校分组group by university 平均发帖数：avg(question_cnt) 升序排序：order by avg_question_cnt   细节问题：  表头重命名：as  完整代码： select university, avg(question_cnt) as avg_question_cnt from user_profile group by university order by avg_question_cnt

题意明确： 取出平均发贴数低于5的学校或平均回帖数小于20的学校  问题分解：  限定条件：平均发贴数低于5或平均回帖数小于20的学校，avg(question_cnt)<5 or avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可； 按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此group by university   细节问题：  表头重命名：as 用having不用where  完整代码： select university, avg(question_cnt) as avg_questi...

题意明确： 每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量  问题分解：  限定条件：无； 每个学校每种性别：按学校和性别分组：group by gender, university 用户数：count(device_id) 30天内平均活跃天数：avg(active_days_within_30) 平均发帖数量：avg(question_cnt)   细节问题：  表头重命名：as 注意输出顺序，与示例保持一致  完整代码： select gender, university, count(device_id) as user_num, avg(active_days_with...

题意明确： 复旦大学学生gpa最高值  问题分解：  限定条件：复旦大学学生，university='复旦大学'； gpa最高值：max(gpa)；当然也可以按gpa降序排序，取第一名   细节问题：  表头重命名：as  完整代码： # 方法1 # select max(gpa) as gpa # from user_profile # where university='复旦大学'; # 方法2 select gpa from user_profile where university='复旦大学' order by gpa desc limit 1

题意明确： 将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，年龄为空输出为其他  问题分解：  限定条件：无； 划分年龄段：数值条件判断，可以用多重if，不过更方便的是用case when [expr] then [result1]...else [default] end 附：case when用法   细节问题：  表头重命名：as 输出的段明文在题目描述和输出示例中不一致，以输出示例为准  完整代码： select device_id, gender, case when age>=25 then '25岁及以上' when age&...

题意明确： 2021年8月每天用户练习题目的数量  问题分解：  限定条件：2021年8月，写法有很多种，比如用year/month函数的year(date)=2021 and month(date)=8，比如用date_format函数的date_format(date, "%Y-%m")="202108" 每天：按天分组group by date 题目数量：count(question_id)   细节问题：  表头重命名：as 输出示例中每天的字段只取了几号，要去掉年月，用day函数即可  完整代码： select day(date) as day...

题意明确： 用户在某天刷题后第二天再来刷题的平均概率  问题分解：  限定条件：第二天再来。 解法1：表里的数据可以看作是全部第一天来刷题了的，那么我们需要构造出第二天来了的字段，因此可以考虑用left join把第二天来了的拼起来，限定第二天来了的可以用date_add(date1, interval 1 day)=date2筛选，并用device_id限定是同一个用户。 解法2：用lead函数将同一用户连续两天的记录拼接起来。先按用户分组partition by device_id，再按日期升序排序order by date，再两两拼接（最后一个默认和null拼接），即lead(date)...

题意明确： 统计每个性别的用户分别有多少参赛者  问题分解：   限定条件：无；   每个性别：按性别分组group by gender，但是没有gender字段，需要从profile字段截取，按字符,分割后取出即可。可使用substring_index函数可以按特定字符串截取源字符串。 substring_index(FIELD, sep, n)可以将字段FIELD按照sep分隔： (1).当n大于0时取第n个分隔符(n从1开始)左边的全部内容； (2).当n小于0时取倒数第n个分隔符(n从-1开始)右边的全部内容； 因此，本题可以直接用substring_index(profile, ',...

题意明确： 统计每个年龄的用户分别有多少参赛者  问题分解：  限定条件：无； 每个年龄：按年龄分组group by age，但是没有age字段，需要从profile字段截取，按字符,分割后取出即可。可使用substring_index函数可以按特定字符串截取源字符串。substring_index(FIELD, sep, n)可以将字段FIELD按照sep分隔：(1).当n大于0时取第n个分隔符(n从1开始)之后的全部内容；(2).当n小于0时取倒数第n个分隔符(n从-1开始)之前的全部内容；因此，本题可以先用substring_index(profile, ',', 3)取出"1...

题意明确： 把用户的个人博客用户名字段提取出来单独记录为一个新的字段  问题分解：   限定条件：无；   提取字段内信息：个人博客字段中的用户名是被字符/分隔的最后一个子串，使用substring_index函数可以按特定字符串截取源字符串 substring_index(FIELD, sep, n)可以将字段FIELD按照sep分隔： (1).当n大于0时取第n个分隔符(n从1开始) 左边 的全部内容； (2).当n小于0时取 倒数第-n个 分隔符(n从-1开始) 右边 的全部内容； 因此，本题可以用substring_index(blog_url, '/', -1)取出用户名.   su...

题意明确： 每个学校里gpa最低的同学 问题分解：  限定条件：gpa最低，看似min(gpa)，但是要留意，是每个学校里的最低，不是全局最低。min(gpa)的时候对应同学的ID丢了，直接干是拿不到最低gpa对应的同学ID的； 每个学校最低：  第一种方式是用group by把学校分组，然后计算得到每个学校最低gpa，再去找这个学校里和这个gpa相等的同学ID。注意这样如果最低gpa对应多个同学，都会输出，题目没有明确此种情况，心理明白就行。 第二种方式是利用窗口函数，先按学校分组计算排序gpa，得到最低gpa的记录在用子查询语法拿到需要的列即可。此题中rou_number可以得到排序后的位...

题意明确： 2021年8月份所有练习过题目的总用户数和练习过题目的总次数  问题分解：  限定条件：2021年8月份，匹配date字段即可，匹配方法主要有三种：（1）like语法：date like "2021-08%"（2）year、month函数：year(date)='2021' and month(date)='08'；（3）date_format函数：date_format(date, '%Y-%m')='2021-08'; 2：总用户数：count函数计数，因为用户有重复，所以需要distinct去重，即count(distinct device_id) 3：总...