Page Comparison

...

http://localhost:19001/api/v0/learners/edx?course_id=course-v1%3AedX%2BDemoX%2BDemo_Course

{
  'query': {
    'bool': {
      'must': [
        {'term': {'course_id':'course-v1:edX+DemoX+Demo_Course'}},
        {'term': {'username': 'edx'}}
      ]
    }
  }
}

SELECT * FROM user_activity
WHERE
	course_id='course-v1:edX+DemoX+Demo_Course' AND
	username='abigail123';

http://localhost:19001/api/v0/learners/edx?course_id=course-v1%3AedX%2BDemoX%2BDemo_Course

{
  'query': {
    'bool': {
      'must': [
        {'term': {'course_id':'course-v1:edX+DemoX+Demo_Course'}},
        {'term': {'username': 'edx'}}
      ]
    }
  }
}

This can be implemented with the following SQL.

...

get_users_in_course

http://localhost:19001/api/v0/learners/?course_id=course-v1%3AedX%2BDemoX%2BDemo_Course&segments=disengaging,struggling&cohort=test&enrollment_mode=verified&text_search=abigail

{
  'query': {
    'bool': {
      'must': [
        {'term': {'course_id': 'course-v1:edX+DemoX+Demo_Course'}}, 
        {'bool': {
          'should': [
            {'term': {'segments': 'disengaging'}},
            {'term': {'segments': 'struggling'}}
          ]
        }}
        {'term': {'cohort': 'test'}},
        {'term': {'enrollment_mode': 'verified'}},
        {'multi_match': {
          'query': 'abigal', 'fields': ['name', 'username', 'email']
        }}
      ]
    }
  }, 
  'sort': [{'username': {'order': 'asc', 'missing': '_last'}}]
}

SELECT * FROM user_activity
WHERE
	course_id='course-v1:edX+DemoX+Demo_Course' AND
	segments IN ('disengaging', 'struggling') AND
	cohort='test' AND
	enrollment_mode='verified' AND
	'abigail' IN (name, username, email)
SORT BY username; 

• I have not looked into how to parameterize the SQL or make it dynamically generated given a set of parameters. I’m assuming Django has this functionality even without Django models.

Odds and Ends

The elasticsearch RosterEntry document contains a field attempt_ratio_order. It’s used to make ordering by the problem_attempts_per_completed more correct. problem_attempts_per_completed can be infinite if no attempts were completed. My understanding is this is stored in the database as null. The comments say the following.

Useful for ordering problem_attempts_per_completed (because results can include null, which is different from zero). attempt_ratio_order is equal to the number of problem attempts if problem_attempts_per_completed is > 1 and set to -problem_attempts if problem_attempts_per_completed = 1.

...

But in the data pipeline, it says this.

Used to sort learners by problem_attempts_per_completed in a meaningful way. When using problem_attempts_per_completed as your primary sort key, you can secondary sort by attempt_ratio_order to see struggling and high performing users. At one extreme this identifies users who have gotten many problems correct with the fewest number of attempts, at the other extreme it highlights users who have gotten very few (if any) problems correct with a very high number of attempts. The two extremes identify the highest performing and lowest performing learners according to this metric. To see high performing learners sort by (problem_attempts_per_completed ASC, attempt_ratio_order DESC). To see struggling learners sort by (problem_attempts_per_completed DESC, attempt_ratio_order ASC).

...