Active Learner Retention Rate

{
  "pageInstanceID": "course-player:sql-analytics-101:lesson-03-production",
  "page": {
    "pageInfo": {
      "pageID": "course-player/sql-analytics-101/lesson-03",
      "pageName": "course player:sql-analytics-101:lesson-03"
    },
    "category": {
      "primaryCategory": "learning",
      "subCategory1": "course player"
    }
  },
  "user": [
    {
      "profile": [
        {
          "profileInfo": {
            "profileID": "3f9a1c7e5b2d4a60c8e1f7b9d2a4c6e8f0b1d3a5c7e9f2b4d6a8c0e2f4b6d8a1"
          },
          "attributes": {
            "authState": "authenticated",
            "learnerRole": "learner",
            "accessEntitlementType": "paid",
            "subscriptionPlan": "pro_annual",
            "accountID": "none",
            "trafficType": "external"
          }
        }
      ]
    }
  ],
  "event": [
    {
      "eventInfo": {
        "eventName": "videoProgress",
        "eventAction": "progress",
        "type": "learning",
        "timeStamp": "2026-09-14T15:42:07.000Z"
      },
      "category": {
        "primaryCategory": "learning"
      },
      "attributes": {
        "contentType": "video",
        "formType": null,
        "contentID": "sql-analytics-101-l03-video",
        "contentName": "Lesson 3: Joins and Window Functions",
        "courseID": "sql-analytics-101",
        "courseName": "SQL for Analytics",
        "enrollmentID": "enr_58213904",
        "learningDeliveryFormat": "self_paced",
        "progressPercent": 50,
        "engagedSeconds": 32,
        "completionStatus": "in_progress",
        "videoPercent": 50,
        "videoCurrentTime": 312,
        "videoDuration": 624,
        "videoTitle": "Lesson 3: Joins and Window Functions",
        "videoProvider": "native player"
      }
    }
  ]
}

{
  "event": "video_progress",
  "user_id": "3f9a1c7e5b2d4a60c8e1f7b9d2a4c6e8f0b1d3a5c7e9f2b4d6a8c0e2f4b6d8a1",
  "content_type": "video",
  "form_type": null,
  "content_id": "sql-analytics-101-l03-video",
  "content_name": "Lesson 3: Joins and Window Functions",
  "course_id": "sql-analytics-101",
  "course_name": "SQL for Analytics",
  "enrollment_id": "enr_58213904",
  "learning_delivery_format": "self_paced",
  "progress_percent": 50,
  "engaged_seconds": 32,
  "completion_status": "in_progress",
  "video_percent": 50,
  "video_current_time": 312,
  "video_duration": 624,
  "video_title": "Lesson 3: Joins and Window Functions",
  "video_provider": "native player",
  "access_entitlement_type": "paid",
  "subscription_plan": "pro_annual",
  "account_id": "none",
  "learner_role": "learner",
  "traffic_type": "external"
}

{
  "event": "videoProgress",
  "eventInfo": {
    "contentType": "video",
    "progressPercent": 50,
    "engagedSeconds": 32
  },
  "user": {
    "learnerID": "3f9a1c7e5b2d4a60c8e1f7b9d2a4c6e8f0b1d3a5c7e9f2b4d6a8c0e2f4b6d8a1",
    "authenticatedState": "authenticated",
    "learnerRole": "learner",
    "accessEntitlementType": "paid",
    "subscriptionPlan": "pro_annual",
    "accountID": "none",
    "trafficType": "external"
  },
  "learning": {
    "contentType": "video",
    "formType": null,
    "contentID": "sql-analytics-101-l03-video",
    "contentName": "Lesson 3: Joins and Window Functions",
    "courseID": "sql-analytics-101",
    "courseName": "SQL for Analytics",
    "enrollmentID": "enr_58213904",
    "learningDeliveryFormat": "self_paced",
    "progressPercent": 50,
    "engagedSeconds": 32,
    "completionStatus": "in_progress"
  },
  "video": {
    "videoPercent": 50,
    "videoCurrentTime": 312,
    "videoDuration": 624,
    "videoTitle": "Lesson 3: Joins and Window Functions",
    "videoProvider": "native player"
  }
}

{
  "_id": "b7e2c1d4-9a3f-4e8b-8c61-2f5d7a9e0b13",
  "eventType": "web.webinteraction.linkClicks",
  "timestamp": "2026-09-14T15:42:07.000Z",
  "identityMap": {
    "ECID": [
      {
        "id": "40527913887631826590148376152738460921",
        "authenticatedState": "ambiguous",
        "primary": false
      }
    ],
    "LearnerID": [
      {
        "id": "3f9a1c7e5b2d4a60c8e1f7b9d2a4c6e8f0b1d3a5c7e9f2b4d6a8c0e2f4b6d8a1",
        "authenticatedState": "authenticated",
        "primary": true
      }
    ]
  },
  "web": {
    "webPageDetails": {
      "name": "course player:sql-analytics-101:lesson-03"
    },
    "webInteraction": {
      "name": "videoProgress",
      "type": "other",
      "linkClicks": {
        "value": 1
      }
    }
  },
  "_tenant": {
    "learning": {
      "contentType": "video",
      "formType": null,
      "contentID": "sql-analytics-101-l03-video",
      "contentName": "Lesson 3: Joins and Window Functions",
      "courseID": "sql-analytics-101",
      "courseName": "SQL for Analytics",
      "enrollmentID": "enr_58213904",
      "learningDeliveryFormat": "self_paced",
      "progressPercent": 50,
      "engagedSeconds": 32,
      "completionStatus": "in_progress",
      "videoPercent": 50,
      "videoCurrentTime": 312,
      "videoDuration": 624,
      "videoTitle": "Lesson 3: Joins and Window Functions",
      "videoProvider": "native player"
    },
    "learner": {
      "learnerID": "3f9a1c7e5b2d4a60c8e1f7b9d2a4c6e8f0b1d3a5c7e9f2b4d6a8c0e2f4b6d8a1",
      "learnerRole": "learner",
      "accessEntitlementType": "paid",
      "subscriptionPlan": "pro_annual",
      "accountID": "none",
      "trafficType": "external"
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Active Learner Retention Rate (period over period): learners active in P-1 and in P / learners active in P-1 x 100.
-- Learner = top-level user_id (hashed learner id, stitched across devices); null user_id is dropped, user_pseudo_id is not used.
-- period_grain: 'WEEK' (ISO week, Monday start), 'MONTH' or 'QUARTER'. reporting_tz: platform reporting time zone.
-- The Enrollment Cohort variant is a separate query and is never blended into this series.
-- Backend tables: your-project.lms.learner_entitlements (user_id, access_start_date, access_end_date) and your-project.privacy.erased_learners (user_id).
-- Join key: user_id here and learner_id in AEP (lms_learner_entitlements, privacy_erased_learners) hold the same hashed learner id.
-- Reactivation is capped by the lookback: a learner last active before data_start counts as neither new nor reactivated.
DECLARE period_grain STRING DEFAULT 'MONTH';
DECLARE reporting_tz STRING DEFAULT 'America/New_York';
DECLARE report_periods INT64 DEFAULT 13;
DECLARE min_base INT64 DEFAULT 50;
DECLARE excluded_account_ids ARRAY<STRING> DEFAULT ARRAY<STRING>[];
DECLARE today_local DATE DEFAULT CURRENT_DATE(reporting_tz);
DECLARE current_period_start DATE DEFAULT (CASE period_grain WHEN 'WEEK' THEN DATE_TRUNC(today_local, ISOWEEK) WHEN 'QUARTER' THEN DATE_TRUNC(today_local, QUARTER) ELSE DATE_TRUNC(today_local, MONTH) END);
-- Lookback covers the reported periods, their P-1 and the same periods one year earlier.
DECLARE data_start DATE DEFAULT (CASE period_grain WHEN 'WEEK' THEN DATE_SUB(current_period_start, INTERVAL (report_periods + 53) WEEK) WHEN 'QUARTER' THEN DATE_SUB(current_period_start, INTERVAL (3 * (report_periods + 5)) MONTH) ELSE DATE_SUB(current_period_start, INTERVAL (report_periods + 13) MONTH) END);

WITH raw_events AS (
  SELECT
    e.user_id,
    e.event_name,
    e.event_timestamp,
    DATE(TIMESTAMP_MICROS(e.event_timestamp), reporting_tz) AS event_date_local,
    (SELECT AS STRUCT
      MAX(IF(ep.key = 'content_type', ep.value.string_value, NULL)) AS content_type,
      MAX(IF(ep.key = 'form_type', ep.value.string_value, NULL)) AS form_type,
      MAX(IF(ep.key = 'course_id', ep.value.string_value, NULL)) AS course_id,
      MAX(IF(ep.key = 'enrollment_id', ep.value.string_value, NULL)) AS enrollment_id,
      MAX(IF(ep.key = 'access_entitlement_type', ep.value.string_value, NULL)) AS access_entitlement_type,
      MAX(IF(ep.key = 'subscription_plan', ep.value.string_value, NULL)) AS subscription_plan,
      MAX(IF(ep.key = 'account_id', ep.value.string_value, NULL)) AS account_id,
      MAX(IF(ep.key = 'learner_role', ep.value.string_value, NULL)) AS learner_role,
      MAX(IF(ep.key = 'traffic_type', ep.value.string_value, NULL)) AS traffic_type,
      MAX(IF(ep.key = 'learning_delivery_format', ep.value.string_value, NULL)) AS learning_delivery_format,
      MAX(IF(ep.key = 'completion_status', ep.value.string_value, NULL)) AS completion_status,
      MAX(IF(ep.key = 'engaged_seconds', COALESCE(ep.value.int_value, CAST(FLOOR(ep.value.double_value) AS INT64)), NULL)) AS engaged_seconds,
      MAX(IF(ep.key = 'progress_percent', COALESCE(ep.value.int_value, CAST(FLOOR(ep.value.double_value) AS INT64)), NULL)) AS progress_percent
    FROM UNNEST(e.event_params) AS ep) AS p
  FROM `your-project.analytics_123456789.events_*` AS e
  -- Daily tables follow the property time zone; one extra day on each side absorbs the reporting time zone offset.
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(data_start, INTERVAL 1 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_ADD(today_local, INTERVAL 1 DAY))
    AND e.event_name IN ('video_progress', 'video_complete', 'tutorial_complete', 'form_submit')
    AND e.user_id IS NOT NULL
),
events AS (
  SELECT
    user_id,
    event_name,
    event_timestamp,
    event_date_local,
    p.*,
    CASE period_grain WHEN 'WEEK' THEN DATE_TRUNC(event_date_local, ISOWEEK) WHEN 'QUARTER' THEN DATE_TRUNC(event_date_local, QUARTER) ELSE DATE_TRUNC(event_date_local, MONTH) END AS period_start
  FROM raw_events
  WHERE event_date_local >= data_start
    AND event_date_local < current_period_start
),
-- Internal staff, QA tenants and test accounts are removed from every period; GDPR / CCPA erasures are removed when history is restated.
-- Known bot traffic is filtered by GA4 at collection.
excluded_learners AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE traffic_type IN ('internal', 'test')
    OR account_id IN UNNEST(excluded_account_ids)
  UNION DISTINCT
  SELECT user_id
  FROM `your-project.privacy.erased_learners`
),
-- Qualifying learning activity only; instructors and admins never qualify (learner_role must be 'learner').
qualifying_activity AS (
  SELECT ev.*
  FROM events AS ev
  LEFT JOIN excluded_learners AS x ON x.user_id = ev.user_id
  WHERE x.user_id IS NULL
    AND ev.learner_role = 'learner'
    AND (
      (ev.event_name = 'video_progress' AND ev.content_type = 'video' AND ev.engaged_seconds >= 10)
      OR (ev.event_name = 'video_complete' AND ev.content_type = 'video')
      OR (ev.event_name = 'tutorial_complete' AND ev.content_type IN ('lesson', 'module', 'course'))
      OR (ev.event_name = 'form_submit' AND ev.form_type IN ('assessment', 'assignment'))
    )
),
-- One row per learner per period; the segment attribute is the last value seen in that period.
learner_periods AS (
  SELECT
    user_id,
    period_start,
    COALESCE(access_entitlement_type, 'unknown') AS access_entitlement_type
  FROM qualifying_activity
  WHERE TRUE
  QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id, period_start ORDER BY event_timestamp DESC) = 1
),
learner_first_period AS (
  SELECT user_id, MIN(period_start) AS first_period_start
  FROM learner_periods
  GROUP BY user_id
),
period_calendar AS (
  SELECT DISTINCT CASE period_grain WHEN 'WEEK' THEN DATE_TRUNC(d, ISOWEEK) WHEN 'QUARTER' THEN DATE_TRUNC(d, QUARTER) ELSE DATE_TRUNC(d, MONTH) END AS period_start
  FROM UNNEST(GENERATE_DATE_ARRAY(data_start, DATE_SUB(current_period_start, INTERVAL 1 DAY))) AS d
),
periods AS (
  SELECT
    period_start,
    CASE period_grain WHEN 'WEEK' THEN DATE_SUB(period_start, INTERVAL 1 WEEK) WHEN 'QUARTER' THEN DATE_SUB(period_start, INTERVAL 1 QUARTER) ELSE DATE_SUB(period_start, INTERVAL 1 MONTH) END AS prior_period_start,
    CASE period_grain WHEN 'WEEK' THEN DATE_ADD(period_start, INTERVAL 1 WEEK) WHEN 'QUARTER' THEN DATE_ADD(period_start, INTERVAL 1 QUARTER) ELSE DATE_ADD(period_start, INTERVAL 1 MONTH) END AS next_period_start
  FROM period_calendar
),
-- Closed periods with a complete P-1 only, finalised after the 72-hour late-hit window following period end.
closed_periods AS (
  SELECT period_start, prior_period_start, DATE_SUB(next_period_start, INTERVAL 1 DAY) AS period_end
  FROM periods
  WHERE prior_period_start >= data_start
    AND TIMESTAMP_ADD(TIMESTAMP(next_period_start, reporting_tz), INTERVAL 72 HOUR) <= CURRENT_TIMESTAMP()
),
-- P-1 base: one row per learner. The join to P is 1:1 because learner_periods is unique by user_id and period.
base_pairs AS (
  SELECT
    cp.period_start,
    cp.prior_period_start,
    cp.period_end,
    prev.user_id,
    prev.access_entitlement_type,
    cur.user_id IS NOT NULL AS is_retained
  FROM closed_periods AS cp
  JOIN learner_periods AS prev ON prev.period_start = cp.prior_period_start
  LEFT JOIN learner_periods AS cur ON cur.user_id = prev.user_id AND cur.period_start = cp.period_start
),
-- Access ended by design (course end date, contract expiry, refund) before P closed, with no entitlement continuing past P:
-- removed from both base and numerator for that period.
access_ended AS (
  SELECT b.user_id, b.period_start
  FROM base_pairs AS b
  JOIN `your-project.lms.learner_entitlements` AS en ON en.user_id = b.user_id
  GROUP BY b.user_id, b.period_start, b.prior_period_start, b.period_end
  HAVING COUNTIF(en.access_end_date BETWEEN b.prior_period_start AND b.period_end) > 0
    AND COUNTIF(en.access_start_date <= b.period_end AND (en.access_end_date IS NULL OR en.access_end_date > b.period_end)) = 0
),
eligible_base AS (
  SELECT b.*
  FROM base_pairs AS b
  LEFT JOIN access_ended AS ae ON ae.user_id = b.user_id AND ae.period_start = b.period_start
  WHERE ae.user_id IS NULL
),
-- Reactivated: active in P, inactive in P-1, active in an earlier period of the lookback. Reported separately, outside the ratio.
reactivated AS (
  SELECT cp.period_start, cur.user_id, cur.access_entitlement_type
  FROM closed_periods AS cp
  JOIN learner_periods AS cur ON cur.period_start = cp.period_start
  JOIN learner_first_period AS f ON f.user_id = cur.user_id
  LEFT JOIN learner_periods AS prev ON prev.user_id = cur.user_id AND prev.period_start = cp.prior_period_start
  WHERE prev.user_id IS NULL
    AND f.first_period_start < cp.prior_period_start
),
-- Access Entitlement Type breakdown uses the learner's P-1 value, so each base learner sits in exactly one segment.
segment_base AS (
  SELECT period_start, 'all_learners' AS segment_dimension, 'all' AS segment_value, user_id, is_retained FROM eligible_base
  UNION ALL
  SELECT period_start, 'access_entitlement_type', access_entitlement_type, user_id, is_retained FROM eligible_base
),
segment_reactivated AS (
  SELECT period_start, 'all_learners' AS segment_dimension, 'all' AS segment_value, user_id FROM reactivated
  UNION ALL
  SELECT period_start, 'access_entitlement_type', access_entitlement_type, user_id FROM reactivated
),
base_counts AS (
  SELECT
    period_start,
    segment_dimension,
    segment_value,
    COUNT(DISTINCT IF(is_retained, user_id, NULL)) AS retained_learners,
    COUNT(DISTINCT user_id) AS prior_period_active_learners
  FROM segment_base
  GROUP BY period_start, segment_dimension, segment_value
),
reactivated_counts AS (
  SELECT period_start, segment_dimension, segment_value, COUNT(DISTINCT user_id) AS reactivated_learners
  FROM segment_reactivated
  GROUP BY period_start, segment_dimension, segment_value
),
period_results AS (
  SELECT
    bc.period_start,
    cp.period_end,
    bc.segment_dimension,
    bc.segment_value,
    bc.retained_learners,
    bc.prior_period_active_learners,
    ROUND(SAFE_DIVIDE(bc.retained_learners, bc.prior_period_active_learners) * 100, 2) AS retention_rate_pct,
    bc.prior_period_active_learners < min_base AS below_minimum_base,
    COALESCE(rc.reactivated_learners, 0) AS reactivated_learners
  FROM base_counts AS bc
  JOIN closed_periods AS cp ON cp.period_start = bc.period_start
  LEFT JOIN reactivated_counts AS rc
    ON rc.period_start = bc.period_start
    AND rc.segment_dimension = bc.segment_dimension
    AND rc.segment_value = bc.segment_value
),
reported_periods AS (
  SELECT period_start
  FROM closed_periods
  WHERE TRUE
  QUALIFY ROW_NUMBER() OVER (ORDER BY period_start DESC) <= report_periods
)
SELECT
  r.period_start,
  r.period_end,
  period_grain AS period_grain,
  r.segment_dimension,
  r.segment_value,
  r.retained_learners,
  r.prior_period_active_learners,
  r.retention_rate_pct,
  r.below_minimum_base,
  IF(r.below_minimum_base, NULL, r.retention_rate_pct) AS reportable_retention_rate_pct,
  r.reactivated_learners,
  -- Seasonality: same period last year (52 ISO weeks earlier at weekly grain).
  IF(ly.below_minimum_base, NULL, ly.retention_rate_pct) AS same_period_last_year_retention_rate_pct,
  ROUND(IF(r.below_minimum_base OR ly.below_minimum_base, NULL, r.retention_rate_pct - ly.retention_rate_pct), 2) AS yoy_change_pts
FROM period_results AS r
JOIN reported_periods AS rp ON rp.period_start = r.period_start
LEFT JOIN period_results AS ly
  ON ly.segment_dimension = r.segment_dimension
  AND ly.segment_value = r.segment_value
  AND ly.period_start = CASE period_grain WHEN 'WEEK' THEN DATE_SUB(r.period_start, INTERVAL 52 WEEK) ELSE DATE_SUB(r.period_start, INTERVAL 1 YEAR) END
ORDER BY r.period_start, r.segment_dimension, r.segment_value;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Active Learner Retention Rate (period over period) on the XDM web ExperienceEvent dataset.
-- Replace _tenant with the organisation's tenant namespace and learner_web_experience_events with the dataset table name.
-- Learner = identityMap.LearnerID[0].id (fallback _tenant.learner.learnerID); events without a learner id are dropped.
-- The Enrollment Cohort variant is a separate query and is never blended into this series.
-- Backend tables: lms_learner_entitlements (learner_id, access_start_date, access_end_date) and privacy_erased_learners (learner_id), the AEP equivalents of BigQuery lms.learner_entitlements and privacy.erased_learners.
-- Join key: learner_id here and user_id in BigQuery hold the same hashed learner id.
-- Reactivation is capped by the lookback: a learner last active before data_start counts as neither new nor reactivated.
WITH params AS (
  SELECT
    'MONTH' AS period_grain,
    'America/New_York' AS reporting_tz,
    13 AS report_periods,
    50 AS min_base,
    CAST(array() AS ARRAY<STRING>) AS excluded_account_ids
),
-- period_grain: 'WEEK' (ISO week, Monday start), 'MONTH' or 'QUARTER'; reporting_tz: platform reporting time zone.
anchors AS (
  SELECT
    p.*,
    CAST(date_trunc(p.period_grain, from_utc_timestamp(current_timestamp(), p.reporting_tz)) AS DATE) AS current_period_start
  FROM params AS p
),
-- Lookback covers the reported periods, their P-1 and the same periods one year earlier.
window_bounds AS (
  SELECT
    a.*,
    CASE a.period_grain
      WHEN 'WEEK' THEN date_sub(a.current_period_start, 7 * (a.report_periods + 53))
      WHEN 'QUARTER' THEN add_months(a.current_period_start, -3 * (a.report_periods + 5))
      ELSE add_months(a.current_period_start, -(a.report_periods + 13))
    END AS data_start
  FROM anchors AS a
),
raw_events AS (
  SELECT
    COALESCE(e.identityMap['LearnerID'][0].id, e._tenant.learner.learnerID) AS learner_id,
    e.timestamp AS event_ts,
    CAST(from_utc_timestamp(e.timestamp, w.reporting_tz) AS DATE) AS event_date_local,
    e.web.webInteraction.name AS activity_name,
    e._tenant.learning.contentType AS content_type,
    e._tenant.learning.formType AS form_type,
    e._tenant.learning.engagedSeconds AS engaged_seconds,
    e._tenant.learner.learnerRole AS learner_role,
    e._tenant.learner.accessEntitlementType AS access_entitlement_type,
    e._tenant.learner.subscriptionPlan AS subscription_plan,
    e._tenant.learner.accountID AS account_id,
    e._tenant.learner.trafficType AS traffic_type,
    w.period_grain,
    w.current_period_start,
    w.data_start,
    w.excluded_account_ids
  FROM learner_web_experience_events AS e
  CROSS JOIN window_bounds AS w
  WHERE e.eventType = 'web.webinteraction.linkClicks'
    AND e.web.webInteraction.name IN ('videoProgress', 'videoComplete', 'contentComplete', 'formSubmit')
    -- timestamp is UTC; one extra day on each side absorbs the reporting time zone offset.
    AND e.timestamp >= CAST(date_sub(w.data_start, 1) AS TIMESTAMP)
    AND e.timestamp < CAST(date_add(w.current_period_start, 1) AS TIMESTAMP)
),
events AS (
  SELECT
    r.*,
    CAST(date_trunc(r.period_grain, CAST(r.event_date_local AS TIMESTAMP)) AS DATE) AS period_start
  FROM raw_events AS r
  WHERE r.learner_id IS NOT NULL
    AND r.event_date_local >= r.data_start
    AND r.event_date_local < r.current_period_start
),
-- Internal staff, QA tenants and test accounts are removed from every period; GDPR / CCPA erasures are removed when history is restated.
-- Bot traffic is removed at collection (bot rules) before the dataset is written.
excluded_learners AS (
  SELECT DISTINCT learner_id
  FROM events
  WHERE traffic_type IN ('internal', 'test')
    OR array_contains(excluded_account_ids, account_id)
  UNION
  SELECT learner_id
  FROM privacy_erased_learners
),
-- Qualifying learning activity only; instructors and admins never qualify (learnerRole must be 'learner').
qualifying_activity AS (
  SELECT ev.*
  FROM events AS ev
  LEFT JOIN excluded_learners AS x ON x.learner_id = ev.learner_id
  WHERE x.learner_id IS NULL
    AND ev.learner_role = 'learner'
    AND (
      (ev.activity_name = 'videoProgress' AND ev.content_type = 'video' AND ev.engaged_seconds >= 10)
      OR (ev.activity_name = 'videoComplete' AND ev.content_type = 'video')
      OR (ev.activity_name = 'contentComplete' AND ev.content_type IN ('lesson', 'module', 'course'))
      OR (ev.activity_name = 'formSubmit' AND ev.form_type IN ('assessment', 'assignment'))
    )
),
-- One row per learner per period; the segment attribute is the last value seen in that period.
learner_periods AS (
  SELECT learner_id, period_start, access_entitlement_type
  FROM (
    SELECT
      learner_id,
      period_start,
      COALESCE(access_entitlement_type, 'unknown') AS access_entitlement_type,
      ROW_NUMBER() OVER (PARTITION BY learner_id, period_start ORDER BY event_ts DESC) AS rn
    FROM qualifying_activity
  ) AS ranked
  WHERE rn = 1
),
learner_first_period AS (
  SELECT learner_id, MIN(period_start) AS first_period_start
  FROM learner_periods
  GROUP BY learner_id
),
period_days AS (
  SELECT explode(sequence(data_start, date_sub(current_period_start, 1), INTERVAL 1 DAY)) AS d
  FROM window_bounds
),
period_calendar AS (
  SELECT DISTINCT CAST(date_trunc(w.period_grain, CAST(pd.d AS TIMESTAMP)) AS DATE) AS period_start
  FROM period_days AS pd
  CROSS JOIN window_bounds AS w
),
periods AS (
  SELECT
    pc.period_start,
    CASE w.period_grain WHEN 'WEEK' THEN date_sub(pc.period_start, 7) WHEN 'QUARTER' THEN add_months(pc.period_start, -3) ELSE add_months(pc.period_start, -1) END AS prior_period_start,
    CASE w.period_grain WHEN 'WEEK' THEN date_add(pc.period_start, 7) WHEN 'QUARTER' THEN add_months(pc.period_start, 3) ELSE add_months(pc.period_start, 1) END AS next_period_start,
    w.data_start,
    w.reporting_tz
  FROM period_calendar AS pc
  CROSS JOIN window_bounds AS w
),
-- Closed periods with a complete P-1 only, finalised after the 72-hour late-hit window following period end.
closed_periods AS (
  SELECT period_start, prior_period_start, date_sub(next_period_start, 1) AS period_end
  FROM periods
  WHERE prior_period_start >= data_start
    AND to_utc_timestamp(CAST(next_period_start AS TIMESTAMP), reporting_tz) + INTERVAL 72 HOURS <= current_timestamp()
),
-- P-1 base: one row per learner. The join to P is 1:1 because learner_periods is unique by learner_id and period.
base_pairs AS (
  SELECT
    cp.period_start,
    cp.prior_period_start,
    cp.period_end,
    prev.learner_id,
    prev.access_entitlement_type,
    cur.learner_id IS NOT NULL AS is_retained
  FROM closed_periods AS cp
  JOIN learner_periods AS prev ON prev.period_start = cp.prior_period_start
  LEFT JOIN learner_periods AS cur ON cur.learner_id = prev.learner_id AND cur.period_start = cp.period_start
),
-- Access ended by design (course end date, contract expiry, refund) before P closed, with no entitlement continuing past P:
-- removed from both base and numerator for that period.
access_ended AS (
  SELECT b.learner_id, b.period_start
  FROM base_pairs AS b
  JOIN lms_learner_entitlements AS en ON en.learner_id = b.learner_id
  GROUP BY b.learner_id, b.period_start, b.prior_period_start, b.period_end
  HAVING SUM(CASE WHEN en.access_end_date BETWEEN b.prior_period_start AND b.period_end THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN en.access_start_date <= b.period_end AND (en.access_end_date IS NULL OR en.access_end_date > b.period_end) THEN 1 ELSE 0 END) = 0
),
eligible_base AS (
  SELECT b.*
  FROM base_pairs AS b
  LEFT JOIN access_ended AS ae ON ae.learner_id = b.learner_id AND ae.period_start = b.period_start
  WHERE ae.learner_id IS NULL
),
-- Reactivated: active in P, inactive in P-1, active in an earlier period of the lookback. Reported separately, outside the ratio.
reactivated AS (
  SELECT cp.period_start, cur.learner_id, cur.access_entitlement_type
  FROM closed_periods AS cp
  JOIN learner_periods AS cur ON cur.period_start = cp.period_start
  JOIN learner_first_period AS f ON f.learner_id = cur.learner_id
  LEFT JOIN learner_periods AS prev ON prev.learner_id = cur.learner_id AND prev.period_start = cp.prior_period_start
  WHERE prev.learner_id IS NULL
    AND f.first_period_start < cp.prior_period_start
),
-- Access Entitlement Type breakdown uses the learner's P-1 value, so each base learner sits in exactly one segment.
segment_base AS (
  SELECT period_start, 'all_learners' AS segment_dimension, 'all' AS segment_value, learner_id, is_retained FROM eligible_base
  UNION ALL
  SELECT period_start, 'access_entitlement_type' AS segment_dimension, access_entitlement_type AS segment_value, learner_id, is_retained FROM eligible_base
),
segment_reactivated AS (
  SELECT period_start, 'all_learners' AS segment_dimension, 'all' AS segment_value, learner_id FROM reactivated
  UNION ALL
  SELECT period_start, 'access_entitlement_type' AS segment_dimension, access_entitlement_type AS segment_value, learner_id FROM reactivated
),
base_counts AS (
  SELECT
    period_start,
    segment_dimension,
    segment_value,
    COUNT(DISTINCT CASE WHEN is_retained THEN learner_id END) AS retained_learners,
    COUNT(DISTINCT learner_id) AS prior_period_active_learners
  FROM segment_base
  GROUP BY period_start, segment_dimension, segment_value
),
reactivated_counts AS (
  SELECT period_start, segment_dimension, segment_value, COUNT(DISTINCT learner_id) AS reactivated_learners
  FROM segment_reactivated
  GROUP BY period_start, segment_dimension, segment_value
),
period_results AS (
  SELECT
    bc.period_start,
    cp.period_end,
    bc.segment_dimension,
    bc.segment_value,
    bc.retained_learners,
    bc.prior_period_active_learners,
    ROUND(bc.retained_learners * 100.0 / NULLIF(bc.prior_period_active_learners, 0), 2) AS retention_rate_pct,
    bc.prior_period_active_learners < w.min_base AS below_minimum_base,
    COALESCE(rc.reactivated_learners, 0) AS reactivated_learners
  FROM base_counts AS bc
  JOIN closed_periods AS cp ON cp.period_start = bc.period_start
  CROSS JOIN window_bounds AS w
  LEFT JOIN reactivated_counts AS rc
    ON rc.period_start = bc.period_start
    AND rc.segment_dimension = bc.segment_dimension
    AND rc.segment_value = bc.segment_value
),
reported_periods AS (
  SELECT period_start
  FROM (
    SELECT cp.period_start, w.report_periods, ROW_NUMBER() OVER (ORDER BY cp.period_start DESC) AS rn
    FROM closed_periods AS cp
    CROSS JOIN window_bounds AS w
  ) AS ranked_periods
  WHERE rn <= report_periods
)
SELECT
  r.period_start,
  r.period_end,
  w.period_grain,
  r.segment_dimension,
  r.segment_value,
  r.retained_learners,
  r.prior_period_active_learners,
  r.retention_rate_pct,
  r.below_minimum_base,
  CASE WHEN r.below_minimum_base THEN NULL ELSE r.retention_rate_pct END AS reportable_retention_rate_pct,
  r.reactivated_learners,
  -- Seasonality: same period last year (52 ISO weeks earlier at weekly grain).
  CASE WHEN ly.below_minimum_base THEN NULL ELSE ly.retention_rate_pct END AS same_period_last_year_retention_rate_pct,
  CASE WHEN r.below_minimum_base OR ly.below_minimum_base THEN NULL ELSE ROUND(r.retention_rate_pct - ly.retention_rate_pct, 2) END AS yoy_change_pts
FROM period_results AS r
CROSS JOIN window_bounds AS w
JOIN reported_periods AS rp ON rp.period_start = r.period_start
LEFT JOIN period_results AS ly
  ON ly.segment_dimension = r.segment_dimension
  AND ly.segment_value = r.segment_value
  AND ly.period_start = CASE w.period_grain WHEN 'WEEK' THEN date_sub(r.period_start, 364) ELSE add_months(r.period_start, -12) END
ORDER BY r.period_start, r.segment_dimension, r.segment_value