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

SELECT 
      t.device_id,
      t.university,
      SUM(CASE
          WHEN MONTH(t.date)=8 
          THEN 1
          ELSE 0
      END) AS question_cnt,
      SUM(CASE
          WHEN t.result IN('right') AND MONTH(t.date)=8 
          THEN 1
          ELSE 0
      END) AS right_question_cnt
FROM 
    (
        SELECT device_id,university,date,result,question_id
        FROM user_profile
        LEFT JOIN question_practice_detail USING(device_id)
        WHERE university IN('复旦大学')
    ) t
GROUP BY t.device_id,t.university