Course Completion Percentage for Each Learner

Medium
135.

Course Completion Percentage for Each Learner

MediumJoinsAggregation

MySQL

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

ColumnType
course_idint
course_titlevarchar

Primary key: course_id.

Course titles are unique.

Table: Lesson

ColumnType
lesson_idint
course_idint
positionint

Primary key: lesson_id.

Every lesson belongs to one course in Course; position is its order in the course.

Table: LessonProgress

ColumnType
learner_idint
lesson_idint
opened_atdatetime
completed_atdatetime

Primary key (together): learner_id, lesson_id.

At most one row per learner and lesson; completed_at is NULL while the lesson is unfinished.

Example

The rows must come back in the order shown.

Course

course_idcourse_title
1SQL for Analysts
2Python Foundations
3Spoken English

Lesson

lesson_idcourse_idposition
1111
1212
1313
1414
2121
2222
2323
3131

LessonProgress

learner_idlesson_idopened_atcompleted_at
501112024-05-01 09:00:002024-05-01 09:40:00
501122024-05-02 20:10:002024-05-02 20:55:00
501132024-05-04 21:00:00NULL
501212024-05-03 07:15:002024-05-03 07:50:00
502212024-05-05 18:00:00NULL
503112024-05-06 10:00:002024-05-06 10:30:00
503122024-05-06 10:31:002024-05-06 11:05:00
503132024-05-07 10:00:002024-05-07 10:20:00
503142024-05-07 10:21:002024-05-07 11:00:00

Output

learner_idcourse_titlelessons_donetotal_lessonscompletion_pct
501Python Foundations1333.33
501SQL for Analysts2450
502Python Foundations030
503SQL for Analysts44100
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
course_idint
course_titlevarchar

Primary key: course_id.

Course titles are unique.

Table: Lesson

ColumnType
lesson_idint
course_idint
positionint

Primary key: lesson_id.

Every lesson belongs to one course in Course; position is its order in the course.

Table: LessonProgress

ColumnType
learner_idint
lesson_idint
opened_atdatetime
completed_atdatetime

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_idcourse_title
1SQL for Analysts
2Python Foundations
3Spoken English

Lesson

lesson_idcourse_idposition
1111
1212
1313
1414
2121
2222
2323
3131

LessonProgress

learner_idlesson_idopened_atcompleted_at
501112024-05-01 09:00:002024-05-01 09:40:00
501122024-05-02 20:10:002024-05-02 20:55:00
501132024-05-04 21:00:00NULL
501212024-05-03 07:15:002024-05-03 07:50:00
502212024-05-05 18:00:00NULL
503112024-05-06 10:00:002024-05-06 10:30:00
503122024-05-06 10:31:002024-05-06 11:05:00
503132024-05-07 10:00:002024-05-07 10:20:00
503142024-05-07 10:21:002024-05-07 11:00:00

Output

learner_idcourse_titlelessons_donetotal_lessonscompletion_pct
501Python Foundations1333.33
501SQL for Analysts2450
502Python Foundations030
503SQL for Analysts44100

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_title

Another 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 →