Ask Your Data Grade Distribution

[morris_gart]

I have a Principal that would like to see the number of failing students within each course.  He just needs the total number for each course and not the individual students. My queries are returning the number of students in each course that have a grade below 60% for current_grade on the scores table for any course.  I have attempted several different prompts to group correctly with no success.

[kyle_cole]

I was able to get at that data in my instance using this prompt:

"List all courses and count the number of students who have workflow state of active with a current score below 60"

I don't have a sub-account like you do so you will have to add that to the prompt. Below is the SQL it generated as well.

SELECT

_c.id AS CourseId,

_c.name AS CourseName,

COUNT(DISTINCT _e.user_id) AS NumStudentsBelow60

FROM

`courses` _c

JOIN `enrollments` _e ON _c.id = _e.course_id

JOIN `scores` _s ON _e.id = _s.enrollment_id

WHERE

_e.type = 'StudentEnrollment'

AND _e.workflow_state = 'active'

AND _s.course_score = TRUE

AND _s.current_score < 60

AND _c.workflow_state NOT IN ('deleted', '__dap_unspecified__')

AND _e.workflow_state NOT IN ('deleted', '__dap_unspecified__')

AND _s.workflow_state NOT IN ('deleted', '__dap_unspecified__')

GROUP BY

_c.id,

_c.name

[GergelyTar]

Hey @morris_gart, if I understand it correctly, you're looking to get the count of students that are failing (=current score lower than 60%) in each course in the active term. Does something like this return the data that you're looking for?

"Count the active student enrollments with a current score lower than 60% in each active course in the XXXXX term."

[morris_gart]

I was able to get the correct data with some adjustment to the prompt.  I do find that using the same exact prompt and only changing the term name will sometimes produce a different SQL.