Show minutes per question per exam per student? - Attempt included.

[Samuel Marks]

Been scratching my head on this problem.

Looks like some of the information I needed was in MongoDB, the rest in MySQL. So I went about combining the two:

mongoexport --fields blocks --collection 'modulestore.structures' -d edxapp --assertExists --query '{"blocks.fields.format": {$in: ["Midterm Exam", "Final Exam"]}}' | node -e 'require("readline").createInterface({input: process.stdin,output: process.stdout,terminal: false}).on("line", l => console.info(JSON.parse(l).blocks.filter(e => ["Midterm Exam", "Final Exam"].indexOf(e.fields.format) > -1).map(e => `${e.block_id},${e.fields.format},${e.fields.display_name}`).join("\n")))' | { echo 'block_id,exam,display_name'; sort -u; } > /tmp/blockid_exam_displayname.csv

Then in MySQL I simply:

CREATE TEMPORARY TABLE blockid_exam_displayname (
  block_id            VARCHAR(33) PRIMARY KEY,
  exam                VARCHAR(33),
  display_name        VARCHAR(33),
  parent_display_name VARCHAR(33),
  children            TEXT
);

LOAD DATA LOCAL INFILE '/tmp/blockid_exam_displayname.csv'
INTO TABLE blockid_exam_displayname
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
IGNORE 1 LINES;

Finally to tie the two tables together:

WITH
Q0 AS (
  SELECT T0.id, T0.module_type, T0.course_id, T0.module_id, SUBSTRING_INDEX(T0.module_id, '@', -1) block_id, TIMESTAMPDIFF(MINUTE, T0.created, T0.modified) minutes_taken
  FROM edxapp.courseware_studentmodule T0
  WHERE T0.course_id = 'course-v1:UniversityOfSydney+OPHTH101+2017_T2' /* AND T0.module_type = 'problem' */
),
Q1 AS (
  SELECT T1.block_id, T1.exam, T1.children
  FROM blockid_exam_displayname T1
)
SELECT Q0.*, Q1.block_id q1_block_id, Q1.exam, Q1.children
FROM Q0
LEFT JOIN Q1 ON (
  LOCATE(Q0.block_id, Q1.children)
);

(note that I used a newer MySQL so I could use CTEs here)

What am I doing wrong? - How do I find minutes per question per exam per student?

[Samuel Marks]

Finally got around to solving this problem - the secret was the dump_course_structure function (from edx-platform).

Wrote some parsers and docs for it: https://github.com/SamuelMarks/openedx-modulestore-utils

Now finding number of minutes taken per problem per exam is as easy as:

SELECT
  T0.*, T1.exam, T1.display_name,

  TIMESTAMPDIFF(MINUTE, T0.created, T0.modified)  minutes_taken
FROM edxapp.

courseware_studentmodule T0
JOIN parsed_ms T1
ON T0.module_id = T1.block_id
WHERE T1.exam IS NOT NULL AND T0.module_type = 'problem';

:D