Data Analyst Interview Questions & Answers: Set 8, EdTech & Online Learning

SQL

1. 1.Find the course completion rate for each course.

   Hide answer

   Answer: Divide completed enrolments by total enrolments per course. Assume enrolments(enrolment_id, course_id, student_id, completed_at), where completed_at is NULL until the learner finishes. COUNT(completed_at) counts only non-NULL values, so it gives completions directly. Multiply by 100.0 (not 100) to avoid integer division, which is a common trap that returns 0. Also decide with the business whether very recent enrolments should be excluded, since they have not had time to finish.

   SELECT
     c.course_id,
     c.course_name,
     COUNT(e.enrolment_id) AS enrolments,
     COUNT(e.completed_at) AS completions,
     ROUND(100.0 * COUNT(e.completed_at)
           / NULLIF(COUNT(e.enrolment_id), 0), 1) AS completion_rate_pct
   FROM courses c
   LEFT JOIN enrolments e ON e.course_id = c.course_id
   GROUP BY c.course_id, c.course_name
   ORDER BY completion_rate_pct DESC;

2. 2.Find students who enrolled in more than 3 courses but completed none.

   Hide answer

   Answer: Group enrolments by student, then use HAVING to keep students with more than 3 distinct courses and zero completions. Assume enrolments(student_id, course_id, completed_at). COUNT(completed_at) = 0 means no row has a completion date. Use COUNT(DISTINCT course_id) so a duplicate enrolment in the same course is not double counted. These learners are a good target list for nudges, since they show interest but struggle to finish.

   SELECT
     student_id,
     COUNT(DISTINCT course_id) AS courses_enrolled
   FROM enrolments
   GROUP BY student_id
   HAVING COUNT(DISTINCT course_id) > 3
      AND COUNT(completed_at) = 0
   ORDER BY courses_enrolled DESC;