What is course completion rate, and how do you measure it in Metabase?

WITH cohort AS (
  -- Denominator: only enrollments old enough to plausibly finish.
  -- Widen the window for long courses; state it on the dashboard.
  SELECT
    enrollment_id,
    course_id,
    student_id,
    access_type
  FROM creator_enrollments
  WHERE enrolled_at < CURRENT_DATE - INTERVAL '90 days'
    AND status <> 'refunded'
), required AS (
  -- Required lessons per course. If a course adds lessons later,
  -- students who already "finished" quietly drop below 100%.
  SELECT
    course_id,
    COUNT(DISTINCT lesson_id) FILTER (WHERE is_required) AS lessons_required
  FROM creator_lesson_progress
  GROUP BY 1
), done AS (
  SELECT
    enrollment_id,
    COUNT(DISTINCT lesson_id) FILTER (
      WHERE is_required AND completed_at IS NOT NULL
    ) AS lessons_done
  FROM creator_lesson_progress
  GROUP BY 1
)
SELECT
  c.course_id,
  c.access_type,
  COUNT(*) AS matured_enrollments,
  -- LEFT JOIN leaves lessons_done NULL for never-started enrollments;
  -- the comparison is NULL, so FILTER correctly counts them as not done.
  COUNT(*) FILTER (WHERE d.lessons_done >= r.lessons_required)
    AS completed,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE d.lessons_done >= r.lessons_required)
    / NULLIF(COUNT(*), 0), 1
  ) AS completion_rate_pct,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE COALESCE(d.lessons_done, 0) = 0)
    / NULLIF(COUNT(*), 0), 1
  ) AS never_started_pct
FROM cohort c
JOIN required r USING (course_id)
LEFT JOIN done d USING (enrollment_id)
GROUP BY 1, 2
ORDER BY matured_enrollments DESC;