Could someone please write an SQL query to view a student's progress?

Development
, ,
1

Hi everyone. I often need to check the progress of different students across different courses, and I’d really like to have an SQL query where I can just input the course_id and student_id to quickly see their progress, since I already have a table with this data.

It’s quite hard to write the query correctly because it has to take into account weights and other factors. I’ve tried, but it never comes out right.

If someone smart and kind-hearted could help me with this little task, I’d be very grateful! :folded_hands:

2

would the API endpoint api/course_home/v1/progress/{course_key}/{student_id}/ not be better suited to something like this? this is some example data you can get via this API.
you can test out this endpoint at yourlmsserver.tld/api-docs/

{
  "can_show_upgrade_sock": false,
  "verified_mode": null,
  "access_expiration": null,
  "certificate_data": {
    "cert_status": "audit_passing",
    "cert_web_view_url": null,
    "download_url": null,
    "certificate_available_date": null
  },
  "completion_summary": {
    "complete_count": 1,
    "incomplete_count": 1,
    "locked_count": 0
  },
  "course_grade": {
    "letter_grade": "Pass",
    "percent": 1,
    "is_passing": true
  },
  "credit_course_requirements": null,
  "end": null,
  "enrollment_mode": "audit",
  "grading_policy": {
    "assignment_policies": [
      {
        "num_droppable": 0,
        "num_total": 1,
        "short_label": "Final",
        "type": "Final Exam",
        "weight": 1
      }
    ],
    "grade_range": {
      "Pass": 0.5
    }
  },
  "has_scheduled_content": false,
  "section_scores": [
    {
      "display_name": "Content",
      "subsections": [
        {
          "assignment_type": null,
          "block_key": "redacted",
          "display_name": "Video",
          "has_graded_assignment": false,
          "override": null,
          "learner_has_access": true,
          "num_points_earned": 0,
          "num_points_possible": 0,
          "percent_graded": 0,
          "problem_scores": [],
          "show_correctness": "always",
          "show_grades": true,
          "url": "redacted"
        },
        {
          "assignment_type": "Final Exam",
          "block_key": "redacted",
          "display_name": "redacted",
          "has_graded_assignment": true,
          "override": null,
          "learner_has_access": true,
          "num_points_earned": 3,
          "num_points_possible": 3,
          "percent_graded": 1,
          "problem_scores": [
            {
              "earned": 3,
              "possible": 3
            }
          ],
          "show_correctness": "always",
          "show_grades": true,
          "url": "redacted"
        }
      ]
    }
  ],
  "studio_url": "redacted",
  "username": "name.surname",
  "user_has_passing_grade": true,
  "verification_data": {
    "link": null,
    "status": "none",
    "status_date": null
  },
  "disable_progress_graph": false
}
3

I have tested this method, and it works on newer versions of Open edX. I am currently working on upgrading to the final version, which is expected to happen in July or August. However, at the moment, the version in use is ironwood.master, and this situation is a bit challenging because I couldn’t find this API in it.