Course Funnel From Enrolment to Certificate

Hard
144.

Course Funnel From Enrolment to Certificate

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

ColumnType
course_idint
course_titlevarchar

Primary key: course_id.

Titles are unique; every course has at least one lesson.

Table: Lesson

ColumnType
lesson_idint
course_idint

Primary key: lesson_id.

Each lesson belongs to one course.

Table: Enrolment

ColumnType
learner_idint
course_idint
enrolled_ondate

Primary key (together): learner_id, course_id.

One row per learner per course they enrolled in.

Table: LessonCompleted

ColumnType
learner_idint
lesson_idint
completed_ondate

Primary key (together): learner_id, lesson_id.

One row per lesson a learner finished; only enrolled learners can complete a course's lessons.

Table: Certificate

ColumnType
certificate_idint
learner_idint
course_idint
issued_ondate

Primary key: certificate_id.

At most one certificate per learner and course, issued only to enrolled learners.

Example

The rows must come back in the order shown.

Course

course_idcourse_title
1Data Analysis With SQL
2Java for Placements
3Cloud Basics

Lesson

lesson_idcourse_id
111
121
131
141
212
222
232
313

Enrolment

learner_idcourse_idenrolled_on
50112024-04-01
50212024-04-02
50312024-04-05
50412024-04-09
50122024-04-03
50522024-04-10

LessonCompleted

learner_idlesson_idcompleted_on
501112024-04-02
501122024-04-04
501132024-04-06
501142024-04-08
502112024-04-03
502122024-04-07
503112024-04-06
505212024-04-12

Certificate

certificate_idlearner_idcourse_idissued_on
150112024-04-09

Output

course_titleenrolledstartedhalfwaycertified
Cloud Basics0000
Data Analysis With SQL4321
Java for Placements2100
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 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

ColumnType
course_idint
course_titlevarchar

Primary key: course_id.

Titles are unique; every course has at least one lesson.

Table: Lesson

ColumnType
lesson_idint
course_idint

Primary key: lesson_id.

Each lesson belongs to one course.

Table: Enrolment

ColumnType
learner_idint
course_idint
enrolled_ondate

Primary key: learner_id, course_id.

One row per learner per course they enrolled in.

Table: LessonCompleted

ColumnType
learner_idint
lesson_idint
completed_ondate

Primary key: learner_id, lesson_id.

One row per lesson a learner finished; only enrolled learners can complete a course's lessons.

Table: Certificate

ColumnType
certificate_idint
learner_idint
course_idint
issued_ondate

Primary key: certificate_id.

At most one certificate per learner and course, issued only to enrolled learners.

Examples

Example 1

Course

course_idcourse_title
1Data Analysis With SQL
2Java for Placements
3Cloud Basics

Lesson

lesson_idcourse_id
111
121
131
141
212
222
232
313

Enrolment

learner_idcourse_idenrolled_on
50112024-04-01
50212024-04-02
50312024-04-05
50412024-04-09
50122024-04-03
50522024-04-10

LessonCompleted

learner_idlesson_idcompleted_on
501112024-04-02
501122024-04-04
501132024-04-06
501142024-04-08
502112024-04-03
502122024-04-07
503112024-04-06
505212024-04-12

Certificate

certificate_idlearner_idcourse_idissued_on
150112024-04-09

Output

course_titleenrolledstartedhalfwaycertified
Cloud Basics0000
Data Analysis With SQL4321
Java for Placements2100

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_title

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