SQL22 统计每个学校的答过题的用户的平均答题数

用户信息表 user_profile
其中device_id指终端编号（认为每个用户有唯一的一个终端），gender指性别，age指年龄，university指用户所在的学校，gpa是该用户平均学分绩点，active_days_within_30是30天内的活跃天数.
答题情况明细表 question_practice_detail
其中question_id是题目编号，result是答题结果.

完成该查询的关键在于理解如何计算用户平均答题数.在q表中，由于存在一名用户回答多道题目的情况，因此计算时要在device_id前加上distinct去重.

select university,count(question_id)/count(distinct q.device_id) as avg_answer_cnt
from user_profile as u,question_practice_detail as q
where u.device_id=q.device_id
group by university;

SQL25 查找山东大学或者性别为男生的信息（结果不去重）

用户信息表 user_profile

由于题目要求结果不去重，因此使用union all连结两个select语句.

*union的结果是去重的.

select device_id , gender , age , gpa from user_profile where university = '山东大学' 
union all
select device_id , gender , age , gpa from user_profile where gender = 'male';

SQL26 计算25岁以上和以下的用户数量

用户信息表 user_profile

方法一：使用union all

select '25岁以下',count(device_id)
from user_profile
where age<25 or age is null
union all
select '25岁及以上',count(device_id)
from user_profile
where age>=25;

方法二：使用if函数.

if（x=n,a,b）表示如果x=n,则返回a，否则返回b.

select
if(age>=25,'25岁及以上','25岁以下' ) as age_cut,
count(device_id) as number
from user_profile
group by age_cut;

方法三：使用case函数.

case when x=n then a else b end）表示如果x=n,则返回a，否则返回b.

多条件时，case
when 条件1 then a
when 条件2 then b
when 条件3 then c
else '其他'end.

select (case when age>=25 then '25岁及以上' else '25岁以下' end) as age_cut, 
count(device_id) as number
from user_profile
group by age_cut;

SQL27 查看不同年龄段的用户明细

将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，若年龄为空返回其他.
用户信息表 user_profile

方法一：使用case函数.

select device_id,gender,
case
    when age<20 then '20岁以下'
    when age<25 then '20-24岁'
    when age>=25 then '25岁及以上'
    else '其他'
end age_cut
from user_profile;

方法二：使用if函数

select device_id, gender, 
if(age>=25,'25岁及以上',
if(age between 20 and 24,'20-24岁',
if(age<20,'20岁以下','其他'))) 
from user_profile;

SQL29 计算用户的平均次日留存率

答题情况明细表 question_practice_detail

该查询的核心在于计算用户在某天答题后第二天继续答题的概率.“平均”的含义是将前一天和次日留存的用户分别加总相除，因此需要用到聚集函数count；并且在分别计算的过程中用户id应当是唯一的，因此需要用到distinct去重.

datediff(date1,date2)，返回两个日期的间隔时间.

select count(distinct q2.device_id,q2.date)/count(distinct q1.device_id,q1.date) as avg_ret
from question_practice_detail as q1 
left outer join 
question_practice_detail as q2
on q1.device_id=q2.device_id and datediff(q2.date,q1.date)=1;

SQL32 截取出年龄

用户信息表 user_profile

substring_index(s,delim,n)截取字符串s中第n次出现位置之前的字符串.当n>0表示从左向右，当n<0表示从右向左.

其次查询中因年龄后面还有性别，因此要进行 substring_index函数嵌套.

select 
substring_index (substring_index(profile,',',-2),',',1)as age,
count(device_id) as number
from user_submit
group by age;

SQL33 找出每个学校GPA最低的同学

用户信息表 user_profile

不能直接使用min(gpa),因为查询出来的最低gpa无法与device_id匹配.应使用子查询.

select device_id,university,gpa from user_profile
where gpa in 
(select min(gpa) from user_profile group by university)
group by university
order by university;

或者使用联结

select u1.device_id,u1.university,u1.gpa
from user_profile as u1 
join 
(select university,min(gpa) as gpa 
from user_profile group by university) as u2
on u1.university=u2.university and u1.gpa=u2.gpa 
order by u1.university;

SQL34 统计复旦用户8月练题情况

用户信息表 user_profile

因为有复旦用户在8月并未练题，将这部分用户一并取出需要加上条件date is null.

select u.device_id,university,count(question_id) as question_cnt,
count(if(result='right', 1, null)) as right_question_cnt
from user_profile as u 
left outer join 
question_practice_detail as q
on u.device_id=q.device_id
where university='复旦大学' and (month(date)=8 or date is null)
group by u.device_id;

SQL35 浙大不同难度题目的正确率

用户信息表 user_profile
答题情况明细表 question_practice_detail
题目明细表 question_detail，中question_id是题目编号，difficult_level是题目难度

错误答案示例：

select difficult_level,count(if(result = 'right', 1, 0)) / count(result) as correct_rate
from user_profile u,question_practice_detail t2,question_detail t3
where t1.device_id = t2.device_id and t2.question_id = t3.question_id
and university = '浙江大学'
group by difficult_level
order by correct_rate

if函数汇总第二个返回值设置为0，意图当回答错误时返回0.但此处是计算回答正确题目的总数，当结果不为right时返回0，则此处也有一条会被记数的记录，因此无得到正确的结果.将0改为null，则当result不为right时，不存在会被记数的记录，就能得到正确的结果.
正确答案：

select difficult_level,
count(if(result='right',1,null))/count(q2.question_id)as correct_rate
from question_detail as q1 left join
 question_practice_detail as q2 on q1.question_id=q2.question_id
 left outer join user_profile as u on u.device_id=q2.device_id
 where university='浙江大学' group by difficult_level order by correct_rate

事实上，由于if函数前嵌套了聚集函数，所以当result为right时的返回值可以取不为null的任何数，因为返回值不影响计数.