题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
select TABLE_A.device_id, TABLE_A.university, IFNULL(TABLE_B.question_cnt, 0) as question_cnt, IFNULL(TABLE_B.right_question_cnt, 0) as right_question_cnt from (select device_id, university from user_profile where university = '复旦大学') as TABLE_A left join (select A.device_id, count(A.question_id) as question_cnt, count(B.question_id) as right_question_cnt from (select device_id, question_id, result from question_practice_detail where EXTRACT(MONTH FROM date) = 8 and device_id in (select device_id from user_profile where university = '复旦大学')) as A left join (select distinct device_id, question_id, result from question_practice_detail where EXTRACT(MONTH FROM date) = 8 and result = 'right' and device_id in (select device_id from user_profile where university = '复旦大学')) as B on A.device_id = B.device_id and A.question_id = B.question_id group by A.device_id) as TABLE_B on TABLE_A.device_id = TABLE_B.device_id
首先这道题目一开始好一直有一个测试用例没有通过,是因为题目的理解上的问题。
题目:现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况,请取出相应明细数据,对于在8月份没有练习过的用户,答题数结果返回0.
有以下2个容易理解错的点:1. 8月份练习的总题目数(哪怕同一道题做了多次也算是总题目数的一种)2. 回答正确的题目数(不同的题目对了几道)
所以我就是把一个大的查询分成了好几个小的查询组合在一起。
ps:具体实现的代码如上,可能会有能够优化的地方,分步骤写的话第一感觉写的是这样的结果。