Unique User Activity

-- Replace these with your desired date range
SET @start_date = '2025-01-05';
SET @end_date = '2025-01-11';

SELECT COUNT(DISTINCT student_id) AS total_unique_students
FROM (
    -- Students who submitted a quiz
    SELECT qs.student_id
    FROM quiz_summary qs
    JOIN user u ON u.id = qs.student_id
    WHERE u.type = 'student'
      AND qs.submit = 1
      AND qs.submit_at BETWEEN @start_date AND @end_date

    UNION

    -- Students who participated in a direct tutor session
    SELECT ts.student_id
    FROM tutor_session ts
    JOIN user u ON u.id = ts.student_id
    WHERE u.type = 'student'
      AND ts.status = 'completed'
      AND ts.created_at BETWEEN @start_date AND @end_date

    UNION

    -- Students who participated via tutor session participant table
    SELECT tsp.participant_id AS student_id
    FROM tutor_session_participant tsp
    JOIN tutor_session ts ON ts.id = tsp.session_id
    JOIN user u ON u.id = tsp.participant_id
    WHERE u.type = 'student'
      AND ts.status = 'completed'
      AND ts.created_at BETWEEN @start_date AND @end_date

    UNION

    -- Students who watched a video (file_log)
    SELECT fl.user_id AS student_id
    FROM file_log fl
    JOIN user u ON u.id = fl.user_id
    WHERE u.type = 'student'
      AND fl.type = 'video'
      AND fl.created_at BETWEEN @start_date AND @end_date

    UNION

    -- Students who triggered feature_user_logger
    SELECT ful.user_id AS student_id
    FROM feature_user_logger ful
    JOIN user u ON u.id = ful.user_id
    WHERE u.type = 'student'
      AND ful.created_at BETWEEN @start_date AND @end_date
) AS combined_activities;