题意：

复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况

分析：

• 由题意，统计8月份的数据，知：month(date)=8
• 由总题目数，知：count(question_id) 或者 sum(case when result is not null then 1 else 0 end)
• 由正确的题目数，知： sum(if (result="right",1,0)) 或者 sum(case when result='right' then 1 else 0 end)
• 限制条件：由题意查询复旦大学的每个用户：
  where university='复旦大学'
  group by up.device_id
  

代码：

• 法一：

select up.device_id,
		up.university,
		count(qpd.question_id) as question_cnt,
		sum(if (qpd.result="right",1,0)) as right_question_cnt
	from user_profile as up
left join question_practice_detail as qpd
on up.device_id=qpd.device_id and month(qpd.date)=8

where up.university='复旦大学' 
group by up.device_id

• 法二：

select up.device_id,
		up.university,
		sum(case when qpd.result is not null then 1 else 0 end) as question_cnt,
		sum(case when qpd.result='right' then 1 else 0 end) as right_question_cnt
	from user_profile as up
left join question_practice_detail as qpd
on up.device_id=qpd.device_id and month(qpd.date)=8

where up.university='复旦大学' 
group by up.device_id