Course Completion Percentage for Each Learner — SQL Problem
On an online learning platform a learner's progress row is created when they open a lesson, and completed_at is filled in when they finish it.
- Difficulty: Medium
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #135
Problem statement
On an online learning platform a learner's progress row is created when they open a lesson, and completed_at is filled in when they finish it. The learner dashboard shows, for each course a learner has started, how much of it they have finished.
For every learner and every course in which the learner has at least one progress row, return learner_id, course_title, lessons_done (lessons with completed_at set), total_lessons (all lessons of the course) and completion_pct (lessons_done over total_lessons as a percentage, rounded to 2 decimal places). Order the rows by learner_id, then by course_title.
Tables
Table: Course
| Column | Type |
|---|---|
| course_id | int |
| course_title | varchar |
Primary key: course_id.
Course titles are unique.
Table: Lesson
| Column | Type |
|---|---|
| lesson_id | int |
| course_id | int |
| position | int |
Primary key: lesson_id.
Every lesson belongs to one course in Course; position is its order in the course.
Table: LessonProgress
| Column | Type |
|---|---|
| learner_id | int |
| lesson_id | int |
| opened_at | datetime |
| completed_at | datetime |
Primary key: learner_id, lesson_id.
At most one row per learner and lesson; completed_at is NULL while the lesson is unfinished.
Examples
Example 1
Course
| course_id | course_title |
|---|---|
| 1 | SQL for Analysts |
| 2 | Python Foundations |
| 3 | Spoken English |
Lesson
| lesson_id | course_id | position |
|---|---|---|
| 11 | 1 | 1 |
| 12 | 1 | 2 |
| 13 | 1 | 3 |
| 14 | 1 | 4 |
| 21 | 2 | 1 |
| 22 | 2 | 2 |
| 23 | 2 | 3 |
| 31 | 3 | 1 |
LessonProgress
| learner_id | lesson_id | opened_at | completed_at |
|---|---|---|---|
| 501 | 11 | 2024-05-01 09:00:00 | 2024-05-01 09:40:00 |
| 501 | 12 | 2024-05-02 20:10:00 | 2024-05-02 20:55:00 |
| 501 | 13 | 2024-05-04 21:00:00 | NULL |
| 501 | 21 | 2024-05-03 07:15:00 | 2024-05-03 07:50:00 |
| 502 | 21 | 2024-05-05 18:00:00 | NULL |
| 503 | 11 | 2024-05-06 10:00:00 | 2024-05-06 10:30:00 |
| 503 | 12 | 2024-05-06 10:31:00 | 2024-05-06 11:05:00 |
| 503 | 13 | 2024-05-07 10:00:00 | 2024-05-07 10:20:00 |
| 503 | 14 | 2024-05-07 10:21:00 | 2024-05-07 11:00:00 |
Output
| learner_id | course_title | lessons_done | total_lessons | completion_pct |
|---|---|---|---|---|
| 501 | Python Foundations | 1 | 3 | 33.33 |
| 501 | SQL for Analysts | 2 | 4 | 50 |
| 502 | Python Foundations | 0 | 3 | 0 |
| 503 | SQL for Analysts | 4 | 4 | 100 |
How to solve Course Completion Percentage for Each Learner
Two counts with different sources meet in this report. The finished lessons come from the learner's progress: join LessonProgress to Lesson to learn each lesson's course, group by learner and course, and COUNT(p.completed_at) — COUNT of a column skips NULLs, so opened-but-unfinished lessons are not counted.
The total lessons must not come from that same join: a learner's progress rows cover only the lessons they opened, so counting them would make every started course look nearly complete. Count lessons per course once in a CTE (or a correlated subquery) and join it in; since it is one value per course, it can sit in the GROUP BY.
Starting from LessonProgress limits the rows to courses the learner has touched, as the statement wants. The percentage is 100 * done / total, rounded for display. Each table is read once; the CTE is a small aggregate over the lesson catalogue.
Reference solution (MySQL)
WITH totals AS (
SELECT course_id, COUNT(*) AS total_lessons FROM Lesson GROUP BY course_id
)
SELECT p.learner_id, c.course_title,
COUNT(p.completed_at) AS lessons_done,
t.total_lessons,
ROUND(100 * COUNT(p.completed_at) / t.total_lessons, 2) AS completion_pct
FROM LessonProgress p
JOIN Lesson l ON l.lesson_id = p.lesson_id
JOIN Course c ON c.course_id = l.course_id
JOIN totals t ON t.course_id = l.course_id
GROUP BY p.learner_id, c.course_title, t.total_lessons
ORDER BY p.learner_id, c.course_titleAnother way
SELECT p.learner_id, c.course_title, SUM(CASE WHEN p.completed_at IS NOT NULL THEN 1 ELSE 0 END) AS lessons_done, (SELECT COUNT(*) FROM Lesson x WHERE x.course_id = c.course_id) AS total_lessons, ROUND(100 * SUM(CASE WHEN p.completed_at IS NOT NULL THEN 1 ELSE 0 END) / (SELECT COUNT(*) FROM Lesson x WHERE x.course_id = c.course_id), 2) AS completion_pct FROM LessonProgress p JOIN Lesson l ON l.lesson_id = p.lesson_id JOIN Course c ON c.course_id = l.course_id GROUP BY p.learner_id, c.course_id, c.course_title ORDER BY p.learner_id, c.course_title← Students Scoring Above Their Section's Internal Average · Students Whose SGPA Fell by More Than One Point →