Problem 1: August Practice Statistics for Fudan University Users

Context: Calculate the total number of questions practiced and the number of correct answers for users from Fudan University specifically in August.

• Filtering: Match users where university = '复旦大学' in the user_profile table.
• Time Constraint: Filter records from August using MONTH(practice_date) = 8 in the question_practice_detail table.
• Aggregation: Group results by device_id.
• Metrics:
  • Total questions: COUNT(question_id)
  • Correct answers: SUM(CASE WHEN status = 'right' THEN 1 ELSE 0 END)

SELECT 
    u.device_id, 
    '复旦大学' AS university,
    COUNT(p.question_id) AS total_questions,
    SUM(CASE WHEN p.status = 'right' THEN 1 ELSE 0 END) AS correct_count
FROM user_profile AS u
LEFT JOIN question_practice_detail AS p
    ON p.device_id = u.device_id 
    AND MONTH(p.practice_date) = 8
WHERE u.university = '复旦大学'
GROUP BY u.device_id;

Problem 2: Correct Answer Rate by Difficulty for Zhejiang University

Context: Determine the accuracy rate for questions based on their difficulty level for students belonging to Zhejiang University.

• Filtering: Select users from user_profile where university = '浙江大学'.
• Joins: Perform an INNER JOIN between user_profile, question_practice_detail, and question_detail to link users, their practice logs, and the difficulty level of the questions.
• Grouping: Group by difficulty_level to get stats per level.
• Calculation: Use AVG on a boolean condition (treated as 1 for true, 0 for false) to get the rate.
• Sorting: Order the results by the calculated rate in ascending order.

SELECT 
    qd.difficulty_level,
    AVG(CASE WHEN qpd.status = 'right' THEN 1 ELSE 0 END) AS correct_rate
FROM user_profile AS up
INNER JOIN question_practice_detail AS qpd
    ON up.device_id = qpd.device_id
INNER JOIN question_detail AS qd
    ON qpd.question_id = qd.question_id
WHERE up.university = '浙江大学'
GROUP BY qd.difficulty_level
ORDER BY correct_rate ASC;

Problem 3: Calculating Average Next-Day User Retention

Context: Compute the probabiilty that a user who practices on a specific day will return to practice the following day.

• Deduplication: First, select distinct combinations of device_id and date to ensure a user active multiple times in a day is counted only once.
• Logic: Use a LEFT JOIN to connect a user's activity on a day (Day A) with they activity on the next day (Day B).
• Date Math: Use DATE_ADD(current_date, INTERVAL 1 DAY) to check for the existence of a record on the subsequent day.
• Retention Rate: Divide the count of successful next-day matches by the count of all initial practice days.

SELECT 
    COUNT(next_day.date) / COUNT(current_day.date) AS avg_retention
FROM (
    SELECT DISTINCT 
        qpd.device_id, 
        qpd.date AS current_date
    FROM question_practice_detail AS qpd
) AS current_day
LEFT JOIN (
    SELECT DISTINCT 
        device_id, 
        date
    FROM question_practice_detail
) AS next_day
    ON current_day.device_id = next_day.device_id
    AND DATE_ADD(current_day.current_date, INTERVAL 1 DAY) = next_day.date;