Interview Question Bank · COUNT(DISTINCT) · LEFT JOIN · finished vs enrolled
4. Course completion rate
MediumEducationQuestion #220
Concept: COUNT(DISTINCT) · LEFT JOIN · finished vs enrolledshow
A completion rate takes its two counts from different tables: the denominator is everyone enrolled, and the numerator is everyone whose finished lessons cover the whole course. Work out each student's distinct completed lessons first, then LEFT JOIN that onto the enrollments so students who never started still count as enrolled. Count distinct lessons, because a student who redoes a lesson has two rows for it.
Example (different data):
WITH stamps AS ( SELECT member_id, COUNT(DISTINCT cafe_id) AS cafes FROM cafe_visits GROUP BY member_id ) SELECT COUNT(*) AS members, COUNT(*) FILTER (WHERE s.cafes = (SELECT COUNT(*) FROM cafes)) AS full_cards FROM loyalty_members m LEFT JOIN stamps s ON s.member_id = m.id;
Your task
Brightpath Academy tracks its self-paced courses lesson by lesson. The product team wants each one's completion rate as of 2025-04-30.
For each self-paced course (format 'self-paced'), return course (title), enrolled (enrollments in the course), completed (enrolled students with a completed_at on every lesson of the course, counting distinct lessons) and completion_pct (completed as a % of enrolled, rounded to 1 decimal). Sort by completion_pct descending, then course.
Starting Postgres in your browser…
Checking your access…