Skip to main content
This query returns course-level activity over a given time window: how many users started and completed each course, alongside baseline (pre-lesson) and endline (post-lesson) quiz accuracy scores. 
SELECT 
  ucp.course_id,
  c.name AS course_name,
  COUNT(ucp.user_id)::int AS num_started, 
  COUNT(ucp.user_id) FILTER (WHERE ucp.completed_at IS NOT NULL)::int AS num_completed,
  ROUND(
    SUM(ucp.num_baseline_answers_correct) * 100.0 /
    NULLIF(SUM(ucp.num_baseline_answers_correct) + SUM(ucp.num_baseline_answers_incorrect), 0)
  )::int AS baseline_accuracy_pct,
  ROUND(
    SUM(ucp.num_endline_answers_correct) * 100.0 /
    NULLIF(SUM(ucp.num_endline_answers_correct) + SUM(ucp.num_endline_answers_incorrect), 0)
  )::int AS endline_accuracy_pct
FROM user_course_performance ucp
JOIN courses c ON ucp.course_id = c.id
WHERE ucp.started_at > current_date - interval '28' day
GROUP BY ucp.course_id, c.name
ORDER BY num_started DESC
Column definitions:
ColumnDescription
num_startedUsers who started the course in the window
num_completedUsers who completed the course in the window
baseline_accuracy_pct% of pre-lesson quiz answers that were correct across all users. Null if baseline quizzes are not enabled for the course.
endline_accuracy_pct% of post-lesson quiz answers that were correct across all users
Notes:
  • To adjust the time window, replace '28' day with your preferred interval
  • Comparing baseline_accuracy_pct to endline_accuracy_pct gives a measure of learning gain — the increase in accuracy after completing the course
  • Courses with no quiz activity will return null accuracy values and can be filtered out with WHERE ucp.num_endline_answers_correct + ucp.num_endline_answers_incorrect > 0