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

题目描述

运营团队希望了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况。

解题思路

1. 筛选复旦大学的用户：

   • 从 user_profile 表中筛选出 university 为 "复旦大学" 的用户，获取他们的 device_id 和 university。

2. 统计8月份的练习数据：

   • 从 question_practice_detail 表中筛选出日期在 8月份（2021-08）的记录。
   • 统计每个用户在8月份练习的总题目数 (question_cnt) 和回答正确的题目数 (right_question_cnt)。

3. 处理未练习用户的数据：

   • 使用 左连接 (LEFT JOIN) 将复旦大学的用户与8月份的练习数据进行连接。

4. 排序输出：

   • 按照 device_id 升序排列结果，确保输出顺序与期望一致。

SQL 查询

SELECT
    up.device_id,
    up.university,
    COUNT(qpd.question_id) AS question_cnt,
    SUM(
            CASE
                WHEN qpd.result = 'right' THEN 1
                ELSE 0
            END
        ) AS right_question_cnt
FROM
    user_profile up
LEFT JOIN 
    question_practice_detail qpd 
    ON up.device_id = qpd.device_id 
    AND qpd.date BETWEEN '2021-08-01' AND DATE_ADD('2021-08-01', INTERVAL 30 DAY)AND DATE_ADD('2021-08-01', INTERVAL 30 DAY)
WHERE
    up.university = '复旦大学'

GROUP BY
    up.device_id,
    up.university
ORDER BY
    up.device_id ASC;

代码解释

• LEFT JOIN 条件：
  • up.device_id = qpd.device_id：基于 device_id 进行连接。
  • qpd.date BETWEEN '2021-08-01' AND '2021-08-31'：仅关联8月份的练习数据。