Course Completion Rate

{
  "pageInstanceID": "course-player:CRS-ANL-101:production",
  "page": {
    "pageInfo": {
      "pageID": "course-player/CRS-ANL-101/final-assessment-result",
      "pageName": "course player:introduction to data analytics:final assessment result",
      "language": "en-US"
    },
    "category": {
      "primaryCategory": "course player",
      "subCategory1": "Data Science"
    }
  },
  "user": [
    {
      "profile": [
        {
          "profileInfo": {
            "profileID": "a3f5c91e2b7d4e08f6a1c3b95d2e7f40c8b16d9a4e2f7035b1c8d6e9a0f4b273",
            "userName": ""
          },
          "attributes": {
            "authState": "authenticated",
            "learnerRole": "learner"
          }
        }
      ]
    }
  ],
  "event": [
    {
      "eventInfo": {
        "eventName": "contentComplete",
        "eventAction": "complete",
        "type": "learning",
        "timeStamp": "2026-09-14T15:42:10-05:00"
      },
      "category": {
        "primaryCategory": "course"
      },
      "attributes": {
        "contentType": "course",
        "contentID": "CRS-ANL-101",
        "courseID": "CRS-ANL-101",
        "courseName": "Introduction to Data Analytics",
        "enrollmentID": "ENR-2026-0048213",
        "enrollmentCohort": "2026-08",
        "completionStatus": "completed",
        "modulesCompleted": 10,
        "modulesRequired": 10,
        "assessmentPassed": true,
        "completionTimestamp": "2026-09-14T15:42:10-05:00",
        "learningDeliveryFormat": "self_paced",
        "accessEntitlementType": "paid",
        "courseDifficultyLevel": "beginner"
      }
    }
  ]
}

{
  "event": "tutorial_complete",
  "user_id": "a3f5c91e2b7d4e08f6a1c3b95d2e7f40c8b16d9a4e2f7035b1c8d6e9a0f4b273",
  "content_type": "course",
  "content_id": "CRS-ANL-101",
  "course_id": "CRS-ANL-101",
  "course_name": "Introduction to Data Analytics",
  "enrollment_id": "ENR-2026-0048213",
  "enrollment_cohort": "2026-08",
  "completion_status": "completed",
  "modules_completed": 10,
  "modules_required": 10,
  "assessment_passed": "true",
  "completion_timestamp": "2026-09-14T15:42:10-05:00",
  "learning_delivery_format": "self_paced",
  "access_entitlement_type": "paid",
  "academic_subject": "Data Science",
  "course_difficulty_level": "beginner",
  "learner_role": "learner"
}

{
  "event": "contentComplete",
  "eventInfo": {
    "contentType": "course",
    "completionTimestamp": "2026-09-14T15:42:10-05:00"
  },
  "user": {
    "learnerId": "a3f5c91e2b7d4e08f6a1c3b95d2e7f40c8b16d9a4e2f7035b1c8d6e9a0f4b273",
    "authState": "authenticated",
    "learnerRole": "learner"
  },
  "content": {
    "contentType": "course",
    "contentId": "CRS-ANL-101",
    "courseId": "CRS-ANL-101",
    "courseName": "Introduction to Data Analytics",
    "academicSubject": "Data Science",
    "courseDifficultyLevel": "beginner",
    "learningDeliveryFormat": "self_paced"
  },
  "enrollment": {
    "enrollmentId": "ENR-2026-0048213",
    "enrollmentCohort": "2026-08",
    "accessEntitlementType": "paid"
  },
  "completion": {
    "completionStatus": "completed",
    "modulesCompleted": 10,
    "modulesRequired": 10,
    "assessmentPassed": true,
    "completionTimestamp": "2026-09-14T15:42:10-05:00"
  }
}

{
  "_id": "b3c1e6f2-4a7d-4f19-9c52-8e0d7a61f4b3",
  "timestamp": "2026-09-14T20:42:10Z",
  "eventType": "web.webinteraction.linkClicks",
  "identityMap": {
    "LearnerID": [
      {
        "id": "a3f5c91e2b7d4e08f6a1c3b95d2e7f40c8b16d9a4e2f7035b1c8d6e9a0f4b273",
        "authenticatedState": "authenticated",
        "primary": true
      }
    ]
  },
  "web": {
    "webInteraction": {
      "name": "contentComplete",
      "type": "other",
      "linkClicks": {
        "value": 1
      }
    }
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar20": "course",
          "eVar21": "CRS-ANL-101",
          "eVar22": "ENR-2026-0048213",
          "eVar23": "completed"
        }
      }
    }
  },
  "_tenant": {
    "learning": {
      "contentType": "course",
      "contentID": "CRS-ANL-101",
      "courseID": "CRS-ANL-101",
      "courseName": "Introduction to Data Analytics",
      "enrollmentID": "ENR-2026-0048213",
      "enrollmentCohort": "2026-08",
      "learnerID": "a3f5c91e2b7d4e08f6a1c3b95d2e7f40c8b16d9a4e2f7035b1c8d6e9a0f4b273",
      "completionStatus": "completed",
      "modulesCompleted": 10,
      "modulesRequired": 10,
      "assessmentPassed": true,
      "completionTimestamp": "2026-09-14T20:42:10Z",
      "learningDeliveryFormat": "self_paced",
      "accessEntitlementType": "paid",
      "academicSubject": "Data Science",
      "courseDifficultyLevel": "beginner"
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Course Completion Rate (%) = eligible enrolments completed within their completion window / all eligible enrolments, by enrolment cohort.
-- Enrolment base: `your-project.lms.enrollments` (LMS export, one row per enrolment record; sync retries can repeat rows).
-- Expected columns: enrollment_id STRING, learner_id STRING (hashed, equals GA4 user_id), course_id STRING,
--   enrollment_level STRING ('course' | 'program' | 'bundle'), enrollment_timestamp TIMESTAMP,
--   learning_delivery_format STRING ('self_paced' | 'cohort_based' | 'instructor_led'),
--   cohort_id STRING (scheduled cohort; NULL for self-paced), cohort_start_date DATE, cohort_end_date DATE,
--   access_period_days INT64 (NULL = no access limit), expected_duration_days INT64,
--   has_completion_criteria BOOL, requires_final_assessment BOOL,
--   access_entitlement_type STRING ('paid' | 'subscription' | 'sponsored' | 'audit' | 'free_preview' | 'trial'),
--   client_organization_id STRING (B2B client organisation; NULL for individual learners),
--   enrollment_status STRING ('active' | 'completed' | 'withdrawn' | 'refunded' | 'cancelled'), lms_completed_at TIMESTAMP (LMS completion record time),
--   status_changed_at TIMESTAMP, refund_window_end_at TIMESTAMP, learner_role STRING, is_test_account BOOL, record_updated_at TIMESTAMP.
WITH params AS (
  SELECT
    'America/Chicago' AS reporting_tz,  -- institution reporting time zone
    DATE '2025-01-01' AS cohort_from_date,
    CURRENT_DATE() AS cohort_to_date,
    FALSE AS segment_by_access_entitlement,  -- TRUE keeps audit / free-preview / trial access and splits the output by entitlement
    FALSE AS group_by_client_organization  -- TRUE adds client_organization_id as an extra grouping column (B2B client reporting)
),
-- Sync-retry duplicates: latest version of each enrolment record; programmes and bundles are measured separately.
lms_records AS (
  SELECT * EXCEPT (record_rank)
  FROM (
    SELECT
      e.*,
      ROW_NUMBER() OVER (PARTITION BY e.enrollment_id ORDER BY e.record_updated_at DESC) AS record_rank
    FROM `your-project.lms.enrollments` AS e
    WHERE e.enrollment_level = 'course'
  )
  WHERE record_rank = 1
),
-- Exclusions; withdrawals after the refund window stay in the base as non-completions.
-- Cohort date: enrolment date (self-paced) or scheduled cohort start date (cohort-based), in the reporting time zone.
eligible AS (
  SELECT
    r.*,
    IF(r.learning_delivery_format = 'self_paced', DATE(r.enrollment_timestamp, p.reporting_tz), r.cohort_start_date) AS cohort_date,
    -- Cohort period: scheduled cohort (cohort_id, else cohort_start_date) for cohort-based; calendar month of enrolment for self-paced.
    IF(r.learning_delivery_format = 'self_paced',
       CONCAT('month:', FORMAT_DATE('%Y-%m', DATE(r.enrollment_timestamp, p.reporting_tz))),
       CONCAT('cohort:', COALESCE(r.cohort_id, CAST(r.cohort_start_date AS STRING)))) AS cohort_period_key
  FROM lms_records AS r
  CROSS JOIN params AS p
  WHERE COALESCE(r.has_completion_criteria, FALSE)
    AND NOT COALESCE(r.is_test_account, FALSE)
    AND LOWER(COALESCE(r.learner_role, 'learner')) NOT IN ('staff', 'instructor', 'qa', 'test')
    AND NOT (r.enrollment_status IN ('refunded', 'cancelled') AND COALESCE(r.status_changed_at <= r.refund_window_end_at, TRUE))
    AND (p.segment_by_access_entitlement OR COALESCE(r.access_entitlement_type, '') NOT IN ('audit', 'free_preview', 'trial'))
),
-- Re-enrolment in the same cohort period counts once (latest active instance); a later re-enrolment closes the earlier one.
current_instances AS (
  SELECT
    e.*,
    ROW_NUMBER() OVER (
      PARTITION BY e.learner_id, e.course_id, e.cohort_period_key
      ORDER BY IF(e.enrollment_status IN ('withdrawn', 'refunded', 'cancelled'), 1, 0), e.enrollment_timestamp DESC
    ) AS instance_rank
  FROM eligible AS e
),
enrollment_base AS (
  SELECT
    c.*,
    LEAD(c.enrollment_timestamp) OVER (PARTITION BY c.learner_id, c.course_id ORDER BY c.enrollment_timestamp) AS next_enrollment_timestamp
  FROM current_instances AS c
  WHERE c.instance_rank = 1
),
-- Completion window: self-paced = access period (or 2x expected duration); cohort-based = scheduled end date + 14-day grace.
-- A NULL window end (self-paced without access_period_days and expected_duration_days, cohort-based without cohort_end_date) = no defined completion window.
enrollment_windows AS (
  SELECT
    b.enrollment_id,
    b.learner_id,
    b.course_id,
    b.cohort_date,
    b.cohort_id,
    b.learning_delivery_format,
    b.access_entitlement_type,
    b.client_organization_id,
    b.requires_final_assessment,
    b.enrollment_status,
    b.lms_completed_at,
    b.enrollment_timestamp,
    b.next_enrollment_timestamp,
    CASE
      WHEN b.learning_delivery_format = 'self_paced'
        THEN TIMESTAMP_ADD(b.enrollment_timestamp, INTERVAL COALESCE(b.access_period_days, 2 * b.expected_duration_days) DAY)
      ELSE TIMESTAMP(DATE_ADD(b.cohort_end_date, INTERVAL 15 DAY), p.reporting_tz)  -- exclusive bound: end of grace day 14
    END AS completion_window_end_at
  FROM enrollment_base AS b
  CROSS JOIN params AS p
  WHERE b.cohort_date BETWEEN p.cohort_from_date AND p.cohort_to_date
),
effective_windows AS (
  SELECT
    w.*,
    w.completion_window_end_at IS NOT NULL AS has_completion_window,
    IF(w.next_enrollment_timestamp < w.completion_window_end_at, w.next_enrollment_timestamp, w.completion_window_end_at) AS window_end_at
  FROM enrollment_windows AS w
),
-- Fallback completion signal (LMS status is primary): unified tutorial_complete event with content_type = course from authenticated users; params pivoted per event.
completion_events AS (
  SELECT
    user_id AS learner_id,
    enrollment_id,
    COALESCE(course_id, content_id) AS course_id,
    COALESCE(SAFE_CAST(completion_timestamp AS TIMESTAMP), TIMESTAMP_MICROS(event_timestamp)) AS completed_at,  -- LMS-recorded time first
    assessment_passed
  FROM (
    SELECT
      ev.user_id,
      ev.event_timestamp,
      (SELECT MAX(IF(ep.key = 'content_type', ep.value.string_value, NULL)) FROM UNNEST(ev.event_params) AS ep) AS content_type,
      (SELECT MAX(IF(ep.key = 'content_id', ep.value.string_value, NULL)) FROM UNNEST(ev.event_params) AS ep) AS content_id,
      (SELECT MAX(IF(ep.key = 'course_id', ep.value.string_value, NULL)) FROM UNNEST(ev.event_params) AS ep) AS course_id,
      (SELECT MAX(IF(ep.key = 'enrollment_id', ep.value.string_value, NULL)) FROM UNNEST(ev.event_params) AS ep) AS enrollment_id,
      (SELECT MAX(IF(ep.key = 'completion_status', ep.value.string_value, NULL)) FROM UNNEST(ev.event_params) AS ep) AS completion_status,
      (SELECT MAX(IF(ep.key = 'assessment_passed', ep.value.string_value, NULL)) FROM UNNEST(ev.event_params) AS ep) AS assessment_passed,
      (SELECT MAX(IF(ep.key = 'completion_timestamp', ep.value.string_value, NULL)) FROM UNNEST(ev.event_params) AS ep) AS completion_timestamp,
      (SELECT MAX(IF(ep.key = 'modules_completed', ep.value.int_value, NULL)) FROM UNNEST(ev.event_params) AS ep) AS modules_completed,
      (SELECT MAX(IF(ep.key = 'modules_required', ep.value.int_value, NULL)) FROM UNNEST(ev.event_params) AS ep) AS modules_required
    FROM `your-project.analytics_123456789.events_*` AS ev
    WHERE ev._TABLE_SUFFIX BETWEEN '20250101' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
      AND ev.event_name = 'tutorial_complete'
      AND ev.user_id IS NOT NULL
  )
  WHERE content_type = 'course'
    AND completion_status = 'completed'
    AND (modules_required IS NULL OR modules_completed >= modules_required)
),
-- Match on enrollment_id; events without it fall back to course_id + user_id.
candidate_matches AS (
  SELECT w.enrollment_id, w.enrollment_timestamp, w.window_end_at, w.requires_final_assessment, c.completed_at, c.assessment_passed
  FROM effective_windows AS w
  JOIN completion_events AS c
    ON c.enrollment_id = w.enrollment_id
  UNION ALL
  SELECT w.enrollment_id, w.enrollment_timestamp, w.window_end_at, w.requires_final_assessment, c.completed_at, c.assessment_passed
  FROM effective_windows AS w
  JOIN completion_events AS c
    ON c.enrollment_id IS NULL
   AND c.course_id = w.course_id
   AND c.learner_id = w.learner_id
),
-- One row per enrolment: first qualifying completion inside the window (repeat signals ignored).
first_completion AS (
  SELECT
    enrollment_id,
    MIN(completed_at) AS first_completed_at
  FROM candidate_matches
  WHERE completed_at >= enrollment_timestamp
    AND completed_at < window_end_at
    AND (NOT COALESCE(requires_final_assessment, FALSE) OR assessment_passed = 'true')
  GROUP BY enrollment_id
),
-- Completed = LMS record says completed (enrollment_status = 'completed' AND lms_completed_at inside the window); the joined web event is only a fallback.
enrollment_completion AS (
  SELECT
    w.*,
    IF(w.enrollment_status = 'completed' AND w.lms_completed_at >= w.enrollment_timestamp AND w.lms_completed_at < w.window_end_at, w.lms_completed_at, NULL) AS lms_completed_in_window_at,
    f.first_completed_at AS event_completed_in_window_at
  FROM effective_windows AS w
  LEFT JOIN first_completion AS f
    ON f.enrollment_id = w.enrollment_id
),
-- First completion = LEAST(lms_completed_at, first qualifying event time) within the window; COALESCE stops LEAST returning NULL when one side is missing.
enrollment_outcomes AS (
  SELECT
    c.enrollment_id,
    c.cohort_date,
    IF(c.learning_delivery_format = 'self_paced', NULL, c.cohort_id) AS cohort_id,
    c.learning_delivery_format,
    c.access_entitlement_type,
    c.client_organization_id,
    c.has_completion_window,
    LEAST(COALESCE(c.lms_completed_in_window_at, c.event_completed_in_window_at), COALESCE(c.event_completed_in_window_at, c.lms_completed_in_window_at)) AS first_completed_at,
    COALESCE(c.window_end_at <= CURRENT_TIMESTAMP(), FALSE) AS is_matured
  FROM enrollment_completion AS c
)
-- Cohorts with any open completion window are 'in progress' and excluded from trend comparisons.
-- Enrolments with no defined completion window are outside numerator and denominator and returned as excluded_no_completion_window.
SELECT
  FORMAT_DATE('%Y-%m', o.cohort_date) AS enrollment_cohort,  -- self-paced: enrolment month; cohort-based: cohort_start_date month
  o.cohort_id,  -- scheduled cohort where present (NULL for self-paced)
  o.learning_delivery_format,
  IF(p.segment_by_access_entitlement, COALESCE(o.access_entitlement_type, 'unknown'), 'all_eligible') AS access_entitlement_segment,
  IF(p.group_by_client_organization, COALESCE(o.client_organization_id, 'no_client_organization'), 'all_organizations') AS client_organization_segment,
  IF(COUNTIF(o.has_completion_window AND NOT o.is_matured) = 0, 'matured', 'in progress') AS cohort_status,
  COUNTIF(o.has_completion_window) AS eligible_enrollments,
  COUNTIF(o.has_completion_window AND o.first_completed_at IS NOT NULL) AS completed_enrollments,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(o.has_completion_window AND o.first_completed_at IS NOT NULL), COUNTIF(o.has_completion_window)), 2) AS course_completion_rate_pct,
  COUNTIF(NOT o.has_completion_window) AS excluded_no_completion_window
FROM enrollment_outcomes AS o
CROSS JOIN params AS p
GROUP BY 1, 2, 3, 4, 5
ORDER BY 1, 2, 3, 4, 5

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Course Completion Rate (%) = eligible enrolments completed within their completion window / all eligible enrolments, by enrolment cohort.
-- web_events_dataset: web ExperienceEvent dataset. replace _tenant with the organisation's tenant namespace (e.g. _acmelearning).
-- lms_enrollments_dataset: LMS enrolment records ingested into AEP (rename to the actual dataset), one row per enrolment record.
-- Expected columns: enrollment_id, learner_id (hashed, equals identityMap LearnerID), course_id,
--   enrollment_level ('course' | 'program' | 'bundle'), enrollment_timestamp (UTC),
--   learning_delivery_format ('self_paced' | 'cohort_based' | 'instructor_led'),
--   cohort_id (scheduled cohort; NULL for self-paced), cohort_start_date, cohort_end_date,
--   access_period_days (NULL = no access limit), expected_duration_days,
--   has_completion_criteria, requires_final_assessment,
--   access_entitlement_type ('paid' | 'subscription' | 'sponsored' | 'audit' | 'free_preview' | 'trial'),
--   client_organization_id (B2B client organisation; NULL for individual learners),
--   enrollment_status ('active' | 'completed' | 'withdrawn' | 'refunded' | 'cancelled'), lms_completed_at TIMESTAMP (LMS completion record time, UTC),
--   status_changed_at, refund_window_end_at, learner_role, is_test_account, record_updated_at.
WITH params AS (
  SELECT
    'America/Chicago' AS reporting_tz,  -- institution reporting time zone
    to_date('2025-01-01') AS cohort_from_date,
    current_date() AS cohort_to_date,
    false AS segment_by_access_entitlement,  -- true keeps audit / free-preview / trial access and splits the output by entitlement
    false AS group_by_client_organization  -- true adds client_organization_id as an extra grouping column (B2B client reporting)
),
-- Sync-retry duplicates: latest version of each enrolment record; programmes and bundles are measured separately.
lms_records AS (
  SELECT
    d.enrollment_id, d.learner_id, d.course_id, d.enrollment_timestamp, d.learning_delivery_format,
    d.cohort_id, d.cohort_start_date, d.cohort_end_date, d.access_period_days, d.expected_duration_days,
    d.has_completion_criteria, d.requires_final_assessment, d.access_entitlement_type, d.client_organization_id,
    d.enrollment_status, d.lms_completed_at, d.status_changed_at, d.refund_window_end_at, d.learner_role, d.is_test_account
  FROM (
    SELECT
      l.*,
      ROW_NUMBER() OVER (PARTITION BY l.enrollment_id ORDER BY l.record_updated_at DESC) AS record_rank
    FROM lms_enrollments_dataset l
    WHERE l.enrollment_level = 'course'
  ) d
  WHERE d.record_rank = 1
),
-- Exclusions; withdrawals after the refund window stay in the base as non-completions.
-- Cohort date: enrolment date (self-paced) or scheduled cohort start date (cohort-based), in the reporting time zone.
eligible AS (
  SELECT
    r.*,
    CASE
      WHEN r.learning_delivery_format = 'self_paced' THEN to_date(from_utc_timestamp(r.enrollment_timestamp, p.reporting_tz))
      ELSE r.cohort_start_date
    END AS cohort_date,
    -- Cohort period: scheduled cohort (cohort_id, else cohort_start_date) for cohort-based; calendar month of enrolment for self-paced.
    CASE
      WHEN r.learning_delivery_format = 'self_paced' THEN concat('month:', date_format(from_utc_timestamp(r.enrollment_timestamp, p.reporting_tz), 'yyyy-MM'))
      ELSE concat('cohort:', COALESCE(r.cohort_id, CAST(r.cohort_start_date AS STRING)))
    END AS cohort_period_key
  FROM lms_records r
  CROSS JOIN params p
  WHERE COALESCE(r.has_completion_criteria, false)
    AND NOT COALESCE(r.is_test_account, false)
    AND lower(COALESCE(r.learner_role, 'learner')) NOT IN ('staff', 'instructor', 'qa', 'test')
    AND NOT (r.enrollment_status IN ('refunded', 'cancelled') AND COALESCE(r.status_changed_at <= r.refund_window_end_at, true))
    AND (p.segment_by_access_entitlement OR COALESCE(r.access_entitlement_type, '') NOT IN ('audit', 'free_preview', 'trial'))
),
-- Re-enrolment in the same cohort period counts once (latest active instance); a later re-enrolment closes the earlier one.
current_instances AS (
  SELECT
    e.*,
    ROW_NUMBER() OVER (
      PARTITION BY e.learner_id, e.course_id, e.cohort_period_key
      ORDER BY CASE WHEN e.enrollment_status IN ('withdrawn', 'refunded', 'cancelled') THEN 1 ELSE 0 END, e.enrollment_timestamp DESC
    ) AS instance_rank
  FROM eligible e
),
enrollment_base AS (
  SELECT
    c.*,
    LEAD(c.enrollment_timestamp) OVER (PARTITION BY c.learner_id, c.course_id ORDER BY c.enrollment_timestamp) AS next_enrollment_timestamp
  FROM current_instances c
  WHERE c.instance_rank = 1
),
-- Completion window: self-paced = access period (or 2x expected duration); cohort-based = scheduled end date + 14-day grace.
-- A NULL window end (self-paced without access_period_days and expected_duration_days, cohort-based without cohort_end_date) = no defined completion window.
enrollment_windows AS (
  SELECT
    b.enrollment_id,
    b.learner_id,
    b.course_id,
    b.cohort_date,
    b.cohort_id,
    b.learning_delivery_format,
    b.access_entitlement_type,
    b.client_organization_id,
    b.requires_final_assessment,
    b.enrollment_status,
    b.lms_completed_at,
    b.enrollment_timestamp,
    b.next_enrollment_timestamp,
    CASE
      WHEN b.learning_delivery_format = 'self_paced'
        THEN CAST(unix_timestamp(b.enrollment_timestamp) + COALESCE(b.access_period_days, 2 * b.expected_duration_days) * 86400 AS TIMESTAMP)
      ELSE to_utc_timestamp(CAST(date_add(b.cohort_end_date, 15) AS TIMESTAMP), p.reporting_tz)  -- exclusive bound: end of grace day 14
    END AS completion_window_end_at
  FROM enrollment_base b
  CROSS JOIN params p
  WHERE b.cohort_date BETWEEN p.cohort_from_date AND p.cohort_to_date
),
effective_windows AS (
  SELECT
    w.*,
    CASE WHEN w.completion_window_end_at IS NOT NULL THEN 1 ELSE 0 END AS has_completion_window,
    CASE
      WHEN w.next_enrollment_timestamp < w.completion_window_end_at THEN w.next_enrollment_timestamp
      ELSE w.completion_window_end_at
    END AS window_end_at
  FROM enrollment_windows w
),
-- Fallback completion signal (LMS status is primary): contentComplete interaction with contentType = course from an identified learner.
completion_events AS (
  SELECT learner_id, enrollment_id, course_id, completed_at, assessment_passed
  FROM (
    SELECT
      COALESCE(_tenant.learning.learnerID, identityMap['LearnerID'][0].id) AS learner_id,
      _tenant.learning.enrollmentID AS enrollment_id,
      _tenant.learning.courseID AS course_id,
      COALESCE(CAST(_tenant.learning.completionTimestamp AS TIMESTAMP), timestamp) AS completed_at,  -- LMS-recorded time (UTC) first
      _tenant.learning.assessmentPassed AS assessment_passed
    FROM web_events_dataset
    WHERE timestamp >= to_timestamp('2025-01-01')
      AND web.webInteraction.name = 'contentComplete'
      AND _tenant.learning.contentType = 'course'
      AND _tenant.learning.completionStatus = 'completed'
      AND (_tenant.learning.modulesRequired IS NULL OR _tenant.learning.modulesCompleted >= _tenant.learning.modulesRequired)
  ) ev
  WHERE ev.learner_id IS NOT NULL
),
-- Match on enrollmentID; events without it fall back to courseID + learner ID.
candidate_matches AS (
  SELECT w.enrollment_id, w.enrollment_timestamp, w.window_end_at, w.requires_final_assessment, c.completed_at, c.assessment_passed
  FROM effective_windows w
  JOIN completion_events c
    ON c.enrollment_id = w.enrollment_id
  UNION ALL
  SELECT w.enrollment_id, w.enrollment_timestamp, w.window_end_at, w.requires_final_assessment, c.completed_at, c.assessment_passed
  FROM effective_windows w
  JOIN completion_events c
    ON c.enrollment_id IS NULL
   AND c.course_id = w.course_id
   AND c.learner_id = w.learner_id
),
-- One row per enrolment: first qualifying completion inside the window (repeat signals ignored).
first_completion AS (
  SELECT
    enrollment_id,
    MIN(completed_at) AS first_completed_at
  FROM candidate_matches
  WHERE completed_at >= enrollment_timestamp
    AND completed_at < window_end_at
    AND (NOT COALESCE(requires_final_assessment, false) OR COALESCE(assessment_passed, false))
  GROUP BY enrollment_id
),
-- Completed = LMS record says completed (enrollment_status = 'completed' AND lms_completed_at inside the window); the joined web event is only a fallback.
enrollment_completion AS (
  SELECT
    w.*,
    CASE
      WHEN w.enrollment_status = 'completed' AND w.lms_completed_at >= w.enrollment_timestamp AND w.lms_completed_at < w.window_end_at THEN w.lms_completed_at
      ELSE NULL
    END AS lms_completed_in_window_at,
    f.first_completed_at AS event_completed_in_window_at
  FROM effective_windows w
  LEFT JOIN first_completion f
    ON f.enrollment_id = w.enrollment_id
),
-- First completion = LEAST(lms_completed_at, first qualifying event time) within the window; COALESCE keeps the result defined when one side is missing.
enrollment_outcomes AS (
  SELECT
    c.enrollment_id,
    c.cohort_date,
    CASE WHEN c.learning_delivery_format = 'self_paced' THEN NULL ELSE c.cohort_id END AS cohort_id,
    c.learning_delivery_format,
    c.access_entitlement_type,
    c.client_organization_id,
    c.has_completion_window,
    least(COALESCE(c.lms_completed_in_window_at, c.event_completed_in_window_at), COALESCE(c.event_completed_in_window_at, c.lms_completed_in_window_at)) AS first_completed_at,
    CASE WHEN c.window_end_at IS NOT NULL AND c.window_end_at <= current_timestamp() THEN 1 ELSE 0 END AS is_matured
  FROM enrollment_completion c
)
-- Cohorts with any open completion window are 'in progress' and excluded from trend comparisons.
-- Enrolments with no defined completion window are outside numerator and denominator and returned as excluded_no_completion_window.
SELECT
  date_format(o.cohort_date, 'yyyy-MM') AS enrollment_cohort,  -- self-paced: enrolment month; cohort-based: cohort_start_date month
  o.cohort_id,  -- scheduled cohort where present (NULL for self-paced)
  o.learning_delivery_format,
  CASE WHEN p.segment_by_access_entitlement THEN COALESCE(o.access_entitlement_type, 'unknown') ELSE 'all_eligible' END AS access_entitlement_segment,
  CASE WHEN p.group_by_client_organization THEN COALESCE(o.client_organization_id, 'no_client_organization') ELSE 'all_organizations' END AS client_organization_segment,
  CASE WHEN SUM(CASE WHEN o.has_completion_window = 1 AND o.is_matured = 0 THEN 1 ELSE 0 END) = 0 THEN 'matured' ELSE 'in progress' END AS cohort_status,
  SUM(o.has_completion_window) AS eligible_enrollments,
  SUM(CASE WHEN o.has_completion_window = 1 AND o.first_completed_at IS NOT NULL THEN 1 ELSE 0 END) AS completed_enrollments,
  ROUND(100.0 * SUM(CASE WHEN o.has_completion_window = 1 AND o.first_completed_at IS NOT NULL THEN 1 ELSE 0 END) / NULLIF(SUM(o.has_completion_window), 0), 2) AS course_completion_rate_pct,
  SUM(1 - o.has_completion_window) AS excluded_no_completion_window
FROM enrollment_outcomes o
CROSS JOIN params p
GROUP BY 1, 2, 3, 4, 5
ORDER BY 1, 2, 3, 4, 5