Course Funnel From Enrolment to Certificate — SQL Problem
The product team of an online course platform tracks a four-step funnel per course: learners enrolled; enrolled learners who started (completed at least one…
- Difficulty: Hard
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #144
Problem statement
The product team of an online course platform tracks a four-step funnel per course: learners enrolled; enrolled learners who started (completed at least one lesson of the course); those who reached halfway (completed at least half of the course's lessons); and those who were certified.
Return one row per course — including courses with no enrolments — with the columns course_title, enrolled, started, halfway and certified. Halfway means completed lessons × 2 ≥ the course's lesson count, so 2 of 4 counts and 1 of 3 does not. Order the rows by course_title.
Tables
Table: Course
| Column | Type |
|---|---|
| course_id | int |
| course_title | varchar |
Primary key: course_id.
Titles are unique; every course has at least one lesson.
Table: Lesson
| Column | Type |
|---|---|
| lesson_id | int |
| course_id | int |
Primary key: lesson_id.
Each lesson belongs to one course.
Table: Enrolment
| Column | Type |
|---|---|
| learner_id | int |
| course_id | int |
| enrolled_on | date |
Primary key: learner_id, course_id.
One row per learner per course they enrolled in.
Table: LessonCompleted
| Column | Type |
|---|---|
| learner_id | int |
| lesson_id | int |
| completed_on | date |
Primary key: learner_id, lesson_id.
One row per lesson a learner finished; only enrolled learners can complete a course's lessons.
Table: Certificate
| Column | Type |
|---|---|
| certificate_id | int |
| learner_id | int |
| course_id | int |
| issued_on | date |
Primary key: certificate_id.
At most one certificate per learner and course, issued only to enrolled learners.
Examples
Example 1
Course
| course_id | course_title |
|---|---|
| 1 | Data Analysis With SQL |
| 2 | Java for Placements |
| 3 | Cloud Basics |
Lesson
| lesson_id | course_id |
|---|---|
| 11 | 1 |
| 12 | 1 |
| 13 | 1 |
| 14 | 1 |
| 21 | 2 |
| 22 | 2 |
| 23 | 2 |
| 31 | 3 |
Enrolment
| learner_id | course_id | enrolled_on |
|---|---|---|
| 501 | 1 | 2024-04-01 |
| 502 | 1 | 2024-04-02 |
| 503 | 1 | 2024-04-05 |
| 504 | 1 | 2024-04-09 |
| 501 | 2 | 2024-04-03 |
| 505 | 2 | 2024-04-10 |
LessonCompleted
| learner_id | lesson_id | completed_on |
|---|---|---|
| 501 | 11 | 2024-04-02 |
| 501 | 12 | 2024-04-04 |
| 501 | 13 | 2024-04-06 |
| 501 | 14 | 2024-04-08 |
| 502 | 11 | 2024-04-03 |
| 502 | 12 | 2024-04-07 |
| 503 | 11 | 2024-04-06 |
| 505 | 21 | 2024-04-12 |
Certificate
| certificate_id | learner_id | course_id | issued_on |
|---|---|---|---|
| 1 | 501 | 1 | 2024-04-09 |
Output
| course_title | enrolled | started | halfway | certified |
|---|---|---|---|---|
| Cloud Basics | 0 | 0 | 0 | 0 |
| Data Analysis With SQL | 4 | 3 | 2 | 1 |
| Java for Placements | 2 | 1 | 0 | 0 |
How to solve Course Funnel From Enrolment to Certificate
A funnel is several counts over the same population, so the clean way is to put one row per enrolment in front of you with everything you need beside it, then count conditionally. Two small aggregates prepare that: total is the number of lessons per course, and progress is how many lessons each learner completed in each course (completions joined to Lesson for the course, grouped by learner and course).
Then start from Course and LEFT JOIN enrolments, progress and certificates on learner and course. Every enrolment appears once — progress and certificates are at most one row per learner and course, so nothing multiplies. Now each step is a COUNT: COUNT(e.learner_id) enrolled; COUNT(p.learner_id) started, because a progress row exists only with at least one completion; COUNT(CASE WHEN p.done * 2 >= t.lessons THEN 1 END) halfway, with the integer comparison making 2 of 4 count and 1 of 3 not; COUNT(cert.certificate_id) certified. A course with no enrolments still has its single row of NULLs and counts 0 everywhere.
The alternative computes each step as an independent scalar subquery per course — simpler to read, more scans.
Reference solution (MySQL)
WITH total AS (
SELECT course_id, COUNT(*) AS lessons FROM Lesson GROUP BY course_id
), progress AS (
SELECT lc.learner_id, l.course_id, COUNT(*) AS done
FROM LessonCompleted lc
JOIN Lesson l ON l.lesson_id = lc.lesson_id
GROUP BY lc.learner_id, l.course_id
)
SELECT c.course_title,
COUNT(e.learner_id) AS enrolled,
COUNT(p.learner_id) AS started,
COUNT(CASE WHEN p.done * 2 >= t.lessons THEN 1 END) AS halfway,
COUNT(cert.certificate_id) AS certified
FROM Course c
JOIN total t ON t.course_id = c.course_id
LEFT JOIN Enrolment e ON e.course_id = c.course_id
LEFT JOIN progress p ON p.learner_id = e.learner_id AND p.course_id = e.course_id
LEFT JOIN Certificate cert ON cert.learner_id = e.learner_id AND cert.course_id = e.course_id
GROUP BY c.course_id, c.course_title
ORDER BY c.course_titleAnother way
SELECT c.course_title, (SELECT COUNT(*) FROM Enrolment e WHERE e.course_id = c.course_id) AS enrolled, (SELECT COUNT(DISTINCT lc.learner_id) FROM LessonCompleted lc JOIN Lesson l ON l.lesson_id = lc.lesson_id WHERE l.course_id = c.course_id) AS started, (SELECT COUNT(*) FROM (SELECT lc.learner_id, l.course_id, COUNT(*) AS done FROM LessonCompleted lc JOIN Lesson l ON l.lesson_id = lc.lesson_id GROUP BY lc.learner_id, l.course_id) d WHERE d.course_id = c.course_id AND d.done * 2 >= (SELECT COUNT(*) FROM Lesson x WHERE x.course_id = c.course_id)) AS halfway, (SELECT COUNT(*) FROM Certificate k WHERE k.course_id = c.course_id) AS certified FROM Course c ORDER BY c.course_title← Median CGPA of Each Branch · Top Three Scores in Each National Mock Test →