Skip to content

Stats Queries

Jump to bottom
Alex Gondek edited this page Jul 16, 2026 · 1 revision

Warning

This content may be partially inaccurate, but has been kept for posterity. It was copied from iPeer's legacy Trac Wiki software (formerly hosted at https://ipeer.ctlt.ubc.ca/ which has since shut down).

Database

Find student who saved the evaluation but not submit yet

SET @EVENTID=EVENT_ID;
SELECT * FROM users 
WHERE id IN 
    (SELECT distinct(evaluator) FROM evaluation_mixevals WHERE event_id = @EVENTID AND evaluator NOT IN 
        (SELECT distinct(submitter_id) FROM evaluation_submissions WHERE event_id = @EVENTID)
    );

Replace EVENT_ID to event id.

Submit for those students

SET @EVENTID=EVENT_ID, @COURSEID=COURSE_ID;
INSERT INTO evaluation_submissions 
    SELECT NULL,@EVENTID, group_events.id, user_id,1,NOW(),'A',1,NOW(),NULL,NOW() 
    FROM groups 
    LEFT JOIN groups_members ON groups.id = groups_members.group_id 
    LEFT JOIN group_events ON group_events.group_id = groups.id 
    WHERE
        course_id = @COURSEID
        AND event_id = @EVENTID
        AND user_id IN 
            (SELECT distinct(evaluator) FROM evaluation_mixevals WHERE event_id = @EVENTID AND evaluator NOT IN 
                (SELECT distinct(submitter_id) FROM evaluation_submissions WHERE event_id = @EVENTID)
            );

Replace EVENT_ID to event id and COURSE_ID to course id.

Merge Students with Duplicated Accounts (the accounts starts with "s" and without)

DROP PROCEDURE IF EXISTS merge_students;

delimiter //

CREATE PROCEDURE merge_students()
BEGIN
        DECLARE old, new INT;
        DECLARE done INT DEFAULT FALSE;
	DECLARE cur CURSOR FOR (SELECT u1.id, u2.id FROM users as u1 JOIN users as u2 ON u2.username = CONCAT("s", u1.username) WHERE u1.username REGEXP '[0-9]{8}');
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        
        OPEN cur;
        
        read_loop: LOOP
        	FETCH cur INTO old, new;
                IF done THEN
                	LEAVE read_loop;
                END IF;
          SELECT CONCAT("Merging user ", new, " into ", old) as "";
	update groups_members set user_id = old where user_id = new; 
	update user_enrols set user_id = old where user_id = new; 
	update survey_inputs set user_id = old where user_id = new; 
	update evaluation_submissions set submitter_id = old where submitter_id = new; 
	update evaluation_simples set evaluator = old where evaluator = new; 
	update evaluation_simples set evaluatee = old where evaluatee = new; 
	update evaluation_rubrics set evaluator = old where evaluator = new; 
	update evaluation_rubrics set evaluatee = old where evaluatee = new; 
	update evaluation_mixevals set evaluator = old where evaluator = new; 
	update evaluation_mixevals set evaluatee = old where evaluatee = new; 
	delete from users where id = new;

        END LOOP;
        
        CLOSE cur;
END//

delimiter ;

CALL merge_students();

Find Duplicate Students

select id, username, student_no  from users where student_no in (select student_no from (select student_no, count(*) c from users group by student_no having c > 1) as a) order by student_no, username;

Merge Students old accounts into new ones

The old student accounts using student number as username and the new ones uses PUID.

DROP PROCEDURE IF EXISTS merge_accounts;
DROP PROCEDURE IF EXISTS merge_by_student_no;

delimiter //

CREATE PROCEDURE merge_accounts()
BEGIN
	DECLARE old, new, count INT;
	DECLARE sn VARCHAR(20);
	DECLARE done INT DEFAULT FALSE;
	DECLARE cur CURSOR FOR SELECT student_no, count(*) c FROM users group by student_no having c = 2;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	OPEN cur;

	read_loop: LOOP
			FETCH cur INTO sn, count;
			IF done THEN
					LEAVE read_loop;
			END IF;
	 CALL merge_by_student_no(sn);

	END LOOP;

	CLOSE cur;
END//

CREATE PROCEDURE merge_by_student_no(IN sn VARCHAR(20))
BEGIN
	DECLARE target_id, source_id, temp_id INT;
	DECLARE username_v1,username_v2 VARCHAR(20);
	DECLARE done INT DEFAULT FALSE;
	DECLARE cur CURSOR FOR (SELECT a.id, b.id, a.username, b.username FROM 
		(SELECT id, username FROM users WHERE student_no = sn AND username != student_no AND LENGTH(username) = 12) as a,
		(SELECT id, username FROM users WHERE student_no = sn AND username = student_no AND LENGTH(username) != 12) as b);
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	OPEN cur;
	
	read_loop: LOOP
		FETCH cur INTO target_id, source_id, username_v1, username_v2;
		IF done THEN
				LEAVE read_loop;
		END IF;

		SELECT CONCAT("Merging user ", source_id, "(", username_v2,") into ", target_id, "(",username_v1,")") as "";
       UPDATE IGNORE groups_members set user_id = target_id where user_id = source_id; 
       UPDATE IGNORE user_enrols set user_id = target_id where user_id = source_id; 
		UPDATE IGNORE survey_group_members set user_id = target_id where user_id = source_id; 
       UPDATE survey_inputs set user_id = target_id where user_id = source_id; 
       UPDATE evaluation_submissions set submitter_id = target_id where submitter_id = source_id; 
       UPDATE evaluation_simples set evaluator = target_id where evaluator = source_id; 
       UPDATE evaluation_simples set evaluatee = target_id where evaluatee = source_id; 
		UPDATE evaluation_simples set creator_id = target_id where creator_id = source_id; 
		UPDATE evaluation_simples set updater_id = target_id where updater_id = source_id; 
       UPDATE evaluation_rubrics set evaluator = target_id where evaluator = source_id; 
       UPDATE evaluation_rubrics set evaluatee = target_id where evaluatee = source_id; 
		UPDATE evaluation_rubrics set creator_id = target_id where creator_id = source_id; 
		UPDATE evaluation_rubrics set updater_id = target_id where updater_id = source_id; 
		UPDATE evaluation_rubric_details set creator_id = target_id where creator_id = source_id; 
		UPDATE evaluation_rubric_details set updater_id = target_id where updater_id = source_id; 
       UPDATE evaluation_mixevals set evaluator = target_id where evaluator = source_id; 
       UPDATE evaluation_mixevals set evaluatee = target_id where evaluatee = source_id; 
		UPDATE evaluation_mixevals set creator_id = target_id where creator_id = source_id; 
		UPDATE evaluation_mixevals set updater_id = target_id where updater_id = source_id; 
		UPDATE evaluation_mixeval_details set creator_id = target_id where creator_id = source_id; 
		UPDATE evaluation_mixeval_details set updater_id = target_id where updater_id = source_id; 
       DELETE from users where id = source_id LIMIT 1;

	END LOOP;
	CLOSE cur;
END//

delimiter ;

Merge Student Accounts by Id

DROP PROCEDURE IF EXISTS merge_by_id;

delimiter //

CREATE PROCEDURE merge_by_id(IN target_id INT, IN source_id INT)
BEGIN
       DECLARE temp_id INT;

       SELECT CONCAT("Merging user ", source_id, " into ", target_id) as "";
       UPDATE IGNORE groups_members set user_id = target_id where user_id = source_id; 
       UPDATE IGNORE user_enrols set user_id = target_id where user_id = source_id; 
                UPDATE IGNORE survey_group_members set user_id = target_id where user_id = source_id; 
       UPDATE survey_inputs set user_id = target_id where user_id = source_id; 
       UPDATE evaluation_submissions set submitter_id = target_id where submitter_id = source_id; 
       UPDATE evaluation_simples set evaluator = target_id where evaluator = source_id; 
       UPDATE evaluation_simples set evaluatee = target_id where evaluatee = source_id; 
                UPDATE evaluation_simples set creator_id = target_id where creator_id = source_id; 
                UPDATE evaluation_simples set updater_id = target_id where updater_id = source_id; 
       UPDATE evaluation_rubrics set evaluator = target_id where evaluator = source_id; 
       UPDATE evaluation_rubrics set evaluatee = target_id where evaluatee = source_id; 
                UPDATE evaluation_rubrics set creator_id = target_id where creator_id = source_id; 
                UPDATE evaluation_rubrics set updater_id = target_id where updater_id = source_id; 
                UPDATE evaluation_rubric_details set creator_id = target_id where creator_id = source_id; 
                UPDATE evaluation_rubric_details set updater_id = target_id where updater_id = source_id; 
       UPDATE evaluation_mixevals set evaluator = target_id where evaluator = source_id; 
       UPDATE evaluation_mixevals set evaluatee = target_id where evaluatee = source_id; 
                UPDATE evaluation_mixevals set creator_id = target_id where creator_id = source_id; 
                UPDATE evaluation_mixevals set updater_id = target_id where updater_id = source_id; 
                UPDATE evaluation_mixeval_details set creator_id = target_id where creator_id = source_id; 
                UPDATE evaluation_mixeval_details set updater_id = target_id where updater_id = source_id; 
       DELETE from users where id = source_id LIMIT 1;
END//

delimiter ;

Merge Instructor/Admin Accounts by Id

DROP PROCEDURE IF EXISTS merge_instructor_by_id;

delimiter //

CREATE PROCEDURE merge_instructor_by_id(IN target_id INT, IN source_id INT)
BEGIN
       DECLARE temp_id INT;

       SELECT CONCAT("Merging user ", source_id, " into ", target_id) as "";
       UPDATE courses set creator_id = target_id where creator_id = source_id; 
       UPDATE courses set updater_id = target_id where updater_id = source_id; 
       UPDATE email_schedules set creator_id = target_id where creator_id = source_id; 
       UPDATE email_templates set creator_id = target_id where creator_id = source_id; 
       UPDATE email_templates set updater_id = target_id where updater_id = source_id; 
       UPDATE events set creator_id = target_id where creator_id = source_id; 
       UPDATE events set updater_id = target_id where updater_id = source_id; 
       UPDATE groups set creator_id = target_id where creator_id = source_id; 
       UPDATE groups set updater_id = target_id where updater_id = source_id; 
       UPDATE IGNORE groups_members set user_id = target_id where user_id = source_id; 
       UPDATE group_events set creator_id = target_id where creator_id = source_id; 
       UPDATE group_events set updater_id = target_id where updater_id = source_id; 
       UPDATE mixevals set creator_id = target_id where creator_id = source_id; 
       UPDATE mixevals set updater_id = target_id where updater_id = source_id; 
       UPDATE oauth_clients set user_id = target_id where user_id = source_id; 
       UPDATE personalizes set user_id = target_id where user_id = source_id; 
       UPDATE roles_users set user_id = target_id where user_id = source_id; 
       UPDATE rubrics set creator_id = target_id where creator_id = source_id; 
       UPDATE rubrics set updater_id = target_id where updater_id = source_id; 
       UPDATE simple_evaluations set creator_id = target_id where creator_id = source_id; 
       UPDATE simple_evaluations set updater_id = target_id where updater_id = source_id; 
       UPDATE surveys set creator_id = target_id where creator_id = source_id; 
       UPDATE surveys set updater_id = target_id where updater_id = source_id;
       UPDATE survey_group_members set user_id = target_id where user_id = source_id; 
       UPDATE survey_inputs set user_id = target_id where user_id = source_id; 
       UPDATE IGNORE user_courses set user_id = target_id where user_id = source_id; 
       UPDATE user_courses set creator_id = target_id where creator_id = source_id; 
       UPDATE user_courses set updater_id = target_id where updater_id = source_id; 
       UPDATE IGNORE user_tutors set user_id = target_id where user_id = source_id; 
       UPDATE user_tutors set creator_id = target_id where creator_id = source_id; 
       UPDATE user_tutors set updater_id = target_id where updater_id = source_id; 
       DELETE from user_faculties where user_id = source_id;
       DELETE from users where id = source_id LIMIT 1;
END//

delimiter ;

Remove the Leading "s" in Username

UPDATE users SET username = SUBSTRING(username, 2) WHERE username REGEXP 's[0-9]{8}';

Export Groups

SELECT group_name, username  FROM `groups` as g JOIN groups_members as m on g.id = m.group_id JOIN users as u on u.id = m.user_id WHERE `course_id` = COURSE_ID

Statistics

New course

select * from courses where created between "2011-09-01 0:0:0" and "2011-12-31 23:59:59";

New evaluation

select * from events where created between "2011-09-01 0:0:0" and "2011-12-31 23:59:59";

New instructor

select * from users where role LIKE "I" AND created between "2011-09-01 0:0:0" and "2011-12-31 23:59:59";

New student

select * from users where role LIKE "S" AND created between "2011-09-01 0:0:0" and "2011-12-31 23:59:59";

Total student

select * from users where role LIKE "S";

Export Survey Results

SELECT first_name, last_name, student_no, prompt, response_text INTO OUTFILE '/tmp/survey_export.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY "\\" LINES TERMINATED BY "\n" FROM
 survey_inputs si join users on user_id = users.id join survey_questions sq on sq.question_id = si.question_id join questions q on q.id = si.question_id WHERE event_id = EVENT_ID;

Clone this wiki locally