Learning Session Frequency

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "country": "US",
          "eventId": "evt_123456789",
          "courseId": "course_6001",
          "language": "en-US",
          "platform": "web",
          "tenantId": "tenant_3001",
          "accountId": "account_1001",
          "contentId": "content_7001",
          "eventName": "select_content",
          "learnerId": "learner_12345",
          "sessionId": "session_4001",
          "activityId": "activity_8001",
          "courseName": "Introduction to Data Analysis",
          "deviceType": "desktop",
          "classroomId": null,
          "contentName": "Data Analysis Fundamentals",
          "contentType": "lesson",
          "courseLevel": "beginner",
          "environment": "production",
          "activityType": "instructional_content",
          "assessmentId": null,
          "deliveryMode": "self_paced",
          "enrollmentId": "enrollment_10001",
          "instructorId": "instructor_9001",
          "sourceSystem": "learning_management_system",
          "activeLearner": true,
          "consentStatus": "granted",
          "learnerIdType": "pseudonymous",
          "learnerStatus": "active",
          "courseCategory": "data_analytics",
          "eventTimestamp": "2025-01-15T10:00:00.000Z",
          "organizationId": "organization_2001",
          "assessmentScore": null,
          "progressPercent": 35.5,
          "completionStatus": "in_progress",
          "enrollmentStatus": "enrolled",
          "interactionCount": 12,
          "learningSessionId": "learning_session_5001",
          "qualifyingSession": true,
          "reportingPeriodId": "2025-01",
          "reportingPeriodEnd": "2025-01-31T23:59:59.999Z",
          "qualificationRuleId": "learning_session_rule_v1",
          "sessionEndTimestamp": "2025-01-15T10:30:00.000Z",
          "reportingPeriodStart": "2025-01-01T00:00:00.000Z",
          "sessionSequenceNumber": 3,
          "sessionStartTimestamp": "2025-01-15T10:00:00.000Z",
          "contentMinutesConsumed": 30,
          "sessionDurationSeconds": 1800,
          "assessmentAttemptNumber": null
        }
      }
    ]
  }
}

{
  "event": "select_content",
  "country": "US",
  "item_id": "content_7001",
  "event_id": "evt_123456789",
  "language": "en-US",
  "platform": "web",
  "course_id": "course_6001",
  "tenant_id": "tenant_3001",
  "account_id": "account_1001",
  "learner_id": "learner_12345",
  "session_id": "session_4001",
  "activity_id": "activity_8001",
  "course_name": "Introduction to Data Analysis",
  "device_type": "desktop",
  "environment": "production",
  "classroom_id": null,
  "content_name": "Data Analysis Fundamentals",
  "content_type": "lesson",
  "course_level": "beginner",
  "activity_type": "instructional_content",
  "assessment_id": null,
  "delivery_mode": "self_paced",
  "enrollment_id": "enrollment_10001",
  "instructor_id": "instructor_9001",
  "source_system": "learning_management_system",
  "active_learner": true,
  "consent_status": "granted",
  "learner_status": "active",
  "course_category": "data_analytics",
  "event_timestamp": "2025-01-15T10:00:00.000Z",
  "learner_id_type": "pseudonymous",
  "organization_id": "organization_2001",
  "assessment_score": null,
  "progress_percent": 35.5,
  "completion_status": "in_progress",
  "enrollment_status": "enrolled",
  "interaction_count": 12,
  "qualifying_session": true,
  "learning_session_id": "learning_session_5001",
  "reporting_period_id": "2025-01",
  "reporting_period_end": "2025-01-31T23:59:59.999Z",
  "qualification_rule_id": "learning_session_rule_v1",
  "session_end_timestamp": "2025-01-15T10:30:00.000Z",
  "reporting_period_start": "2025-01-01T00:00:00.000Z",
  "session_sequence_number": 3,
  "session_start_timestamp": "2025-01-15T10:00:00.000Z",
  "content_minutes_consumed": 30,
  "session_duration_seconds": 1800,
  "assessment_attempt_number": null
}

{
  "event": "select_content",
  "eventInfo": {
    "country": "US",
    "eventId": "evt_123456789",
    "courseId": "course_6001",
    "language": "en-US",
    "platform": "web",
    "tenantId": "tenant_3001",
    "accountId": "account_1001",
    "contentId": "content_7001",
    "learnerId": "learner_12345",
    "sessionId": "session_4001",
    "activityId": "activity_8001",
    "courseName": "Introduction to Data Analysis",
    "deviceType": "desktop",
    "classroomId": null,
    "contentName": "Data Analysis Fundamentals",
    "contentType": "lesson",
    "courseLevel": "beginner",
    "environment": "production",
    "activityType": "instructional_content",
    "assessmentId": null,
    "deliveryMode": "self_paced",
    "enrollmentId": "enrollment_10001",
    "instructorId": "instructor_9001",
    "sourceSystem": "learning_management_system",
    "activeLearner": true,
    "consentStatus": "granted",
    "learnerIdType": "pseudonymous",
    "learnerStatus": "active",
    "courseCategory": "data_analytics",
    "eventTimestamp": "2025-01-15T10:00:00.000Z",
    "organizationId": "organization_2001",
    "assessmentScore": null,
    "progressPercent": 35.5,
    "completionStatus": "in_progress",
    "enrollmentStatus": "enrolled",
    "interactionCount": 12,
    "learningSessionId": "learning_session_5001",
    "qualifyingSession": true,
    "reportingPeriodId": "2025-01",
    "reportingPeriodEnd": "2025-01-31T23:59:59.999Z",
    "qualificationRuleId": "learning_session_rule_v1",
    "sessionEndTimestamp": "2025-01-15T10:30:00.000Z",
    "reportingPeriodStart": "2025-01-01T00:00:00.000Z",
    "sessionSequenceNumber": 3,
    "sessionStartTimestamp": "2025-01-15T10:00:00.000Z",
    "contentMinutesConsumed": 30,
    "sessionDurationSeconds": 1800,
    "assessmentAttemptNumber": null
  }
}

{
  "_id": "evt_123456789",
  "web": {
    "webInteraction": {
      "name": "select_content",
      "type": "other",
      "linkClicks": {
        "value": 1
      }
    }
  },
  "device": {
    "type": "desktop"
  },
  "eventType": "web.webinteraction.linkClicks",
  "timestamp": "2025-01-15T10:00:00.000Z",
  "producedBy": "self",
  "environment": {
    "type": "production",
    "language": "en-US"
  },
  "identityMap": {
    "learnerId": [
      {
        "id": "learner_12345",
        "primary": true,
        "authenticatedState": "authenticated"
      }
    ]
  },
  "placeContext": {
    "geo": {
      "countryCode": "US"
    }
  },
  "_learningAnalytics": {
    "courseId": "course_6001",
    "platform": "web",
    "tenantId": "tenant_3001",
    "accountId": "account_1001",
    "contentId": "content_7001",
    "sessionId": "session_4001",
    "activityId": "activity_8001",
    "courseName": "Introduction to Data Analysis",
    "classroomId": null,
    "contentName": "Data Analysis Fundamentals",
    "contentType": "lesson",
    "courseLevel": "beginner",
    "activityType": "instructional_content",
    "assessmentId": null,
    "deliveryMode": "self_paced",
    "enrollmentId": "enrollment_10001",
    "instructorId": "instructor_9001",
    "sourceSystem": "learning_management_system",
    "activeLearner": true,
    "consentStatus": "granted",
    "learnerIdType": "pseudonymous",
    "learnerStatus": "active",
    "courseCategory": "data_analytics",
    "organizationId": "organization_2001",
    "assessmentScore": null,
    "progressPercent": 35.5,
    "completionStatus": "in_progress",
    "enrollmentStatus": "enrolled",
    "interactionCount": 12,
    "learningSessionId": "learning_session_5001",
    "qualifyingSession": true,
    "reportingPeriodId": "2025-01",
    "reportingPeriodEnd": "2025-01-31T23:59:59.999Z",
    "qualificationRuleId": "learning_session_rule_v1",
    "sessionEndTimestamp": "2025-01-15T10:30:00.000Z",
    "reportingPeriodStart": "2025-01-01T00:00:00.000Z",
    "sessionSequenceNumber": 3,
    "sessionStartTimestamp": "2025-01-15T10:00:00.000Z",
    "contentMinutesConsumed": 30,
    "sessionDurationSeconds": 1800,
    "assessmentAttemptNumber": null
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
WITH extracted_events AS (
  SELECT
    e.event_date,
    e.event_timestamp AS ga4_event_timestamp,
    e.event_name,
    e.user_pseudo_id,
    e.user_id,
    e.device.category AS ga4_device_type,
    e.geo.country AS ga4_country,
    ep.*
  FROM `your-project.analytics_123456789.events_*` AS e
  CROSS JOIN (
    SELECT AS STRUCT
      MAX(IF(p.key = 'event_id', p.value.string_value, NULL)) AS event_id,
      MAX(IF(p.key = 'event_timestamp', p.value.string_value, NULL)) AS event_timestamp_string,
      MAX(IF(p.key = 'source_system', p.value.string_value, NULL)) AS source_system,
      MAX(IF(p.key = 'environment', p.value.string_value, NULL)) AS environment,
      MAX(IF(p.key = 'platform', p.value.string_value, NULL)) AS platform,
      MAX(IF(p.key = 'learner_id', p.value.string_value, NULL)) AS learner_id,
      MAX(IF(p.key = 'learner_id_type', p.value.string_value, NULL)) AS learner_id_type,
      MAX(IF(p.key = 'learner_status', p.value.string_value, NULL)) AS learner_status,
      MAX(IF(p.key = 'account_id', p.value.string_value, NULL)) AS account_id,
      MAX(IF(p.key = 'organization_id', p.value.string_value, NULL)) AS organization_id,
      MAX(IF(p.key = 'tenant_id', p.value.string_value, NULL)) AS tenant_id,
      MAX(IF(p.key = 'session_id', COALESCE(p.value.string_value, CAST(p.value.int_value AS STRING)), NULL)) AS session_id,
      MAX(IF(p.key = 'learning_session_id', p.value.string_value, NULL)) AS learning_session_id,
      MAX(IF(p.key = 'session_sequence_number', COALESCE(CAST(p.value.int_value AS STRING), p.value.string_value), NULL)) AS session_sequence_number_string,
      MAX(IF(p.key = 'session_start_timestamp', p.value.string_value, NULL)) AS session_start_timestamp_string,
      MAX(IF(p.key = 'session_end_timestamp', p.value.string_value, NULL)) AS session_end_timestamp_string,
      MAX(IF(p.key = 'session_duration_seconds', COALESCE(CAST(p.value.int_value AS STRING), CAST(p.value.double_value AS STRING), p.value.string_value), NULL)) AS session_duration_seconds_string,
      MAX(IF(p.key = 'qualifying_session', COALESCE(p.value.string_value, CAST(p.value.int_value AS STRING)), NULL)) AS qualifying_session_string,
      MAX(IF(p.key = 'qualification_rule_id', p.value.string_value, NULL)) AS qualification_rule_id,
      MAX(IF(p.key = 'active_learner', COALESCE(p.value.string_value, CAST(p.value.int_value AS STRING)), NULL)) AS active_learner_string,
      MAX(IF(p.key = 'reporting_period_id', p.value.string_value, NULL)) AS reporting_period_id,
      MAX(IF(p.key = 'reporting_period_start', p.value.string_value, NULL)) AS reporting_period_start_string,
      MAX(IF(p.key = 'reporting_period_end', p.value.string_value, NULL)) AS reporting_period_end_string,
      MAX(IF(p.key = 'course_id', p.value.string_value, NULL)) AS course_id,
      MAX(IF(p.key = 'course_name', p.value.string_value, NULL)) AS course_name,
      MAX(IF(p.key = 'course_category', p.value.string_value, NULL)) AS course_category,
      MAX(IF(p.key = 'course_level', p.value.string_value, NULL)) AS course_level,
      MAX(IF(p.key IN ('item_id', 'content_id'), p.value.string_value, NULL)) AS content_id,
      MAX(IF(p.key = 'content_name', p.value.string_value, NULL)) AS content_name,
      MAX(IF(p.key = 'content_type', p.value.string_value, NULL)) AS content_type,
      MAX(IF(p.key = 'activity_id', p.value.string_value, NULL)) AS activity_id,
      MAX(IF(p.key = 'activity_type', p.value.string_value, NULL)) AS activity_type,
      MAX(IF(p.key = 'assessment_id', p.value.string_value, NULL)) AS assessment_id,
      MAX(IF(p.key = 'delivery_mode', p.value.string_value, NULL)) AS delivery_mode,
      MAX(IF(p.key = 'classroom_id', p.value.string_value, NULL)) AS classroom_id,
      MAX(IF(p.key = 'instructor_id', p.value.string_value, NULL)) AS instructor_id,
      MAX(IF(p.key = 'enrollment_id', p.value.string_value, NULL)) AS enrollment_id,
      MAX(IF(p.key = 'enrollment_status', p.value.string_value, NULL)) AS enrollment_status,
      MAX(IF(p.key = 'progress_percent', COALESCE(CAST(p.value.double_value AS STRING), CAST(p.value.float_value AS STRING), CAST(p.value.int_value AS STRING), p.value.string_value), NULL)) AS progress_percent_string,
      MAX(IF(p.key = 'completion_status', p.value.string_value, NULL)) AS completion_status,
      MAX(IF(p.key = 'interaction_count', COALESCE(CAST(p.value.int_value AS STRING), p.value.string_value), NULL)) AS interaction_count_string,
      MAX(IF(p.key = 'content_minutes_consumed', COALESCE(CAST(p.value.double_value AS STRING), CAST(p.value.int_value AS STRING), p.value.string_value), NULL)) AS content_minutes_consumed_string,
      MAX(IF(p.key = 'assessment_attempt_number', COALESCE(CAST(p.value.int_value AS STRING), p.value.string_value), NULL)) AS assessment_attempt_number_string,
      MAX(IF(p.key = 'assessment_score', COALESCE(CAST(p.value.double_value AS STRING), CAST(p.value.int_value AS STRING), p.value.string_value), NULL)) AS assessment_score_string,
      MAX(IF(p.key = 'device_type', p.value.string_value, NULL)) AS parameter_device_type,
      MAX(IF(p.key = 'country', p.value.string_value, NULL)) AS parameter_country,
      MAX(IF(p.key = 'language', p.value.string_value, NULL)) AS language,
      MAX(IF(p.key = 'consent_status', p.value.string_value, NULL)) AS consent_status
    FROM UNNEST(e.event_params) AS p
  ) AS ep
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', @start_date)
                          AND FORMAT_DATE('%Y%m%d', @end_date)
    AND e.event_name = 'select_content'
),
canonical_events AS (
  SELECT
    event_name,
    COALESCE(event_id, CONCAT('ga4-', CAST(ga4_event_timestamp AS STRING), '-', COALESCE(user_pseudo_id, 'unknown'))) AS event_id,
    COALESCE(SAFE_CAST(event_timestamp_string AS TIMESTAMP), TIMESTAMP_MICROS(ga4_event_timestamp)) AS event_timestamp,
    source_system,
    environment,
    platform,
    COALESCE(learner_id, user_id, user_pseudo_id) AS learner_id,
    learner_id_type,
    learner_status,
    COALESCE(account_id, 'unknown') AS account_id,
    COALESCE(organization_id, 'unknown') AS organization_id,
    COALESCE(tenant_id, 'unknown') AS tenant_id,
    session_id,
    learning_session_id,
    SAFE_CAST(session_sequence_number_string AS INT64) AS session_sequence_number,
    SAFE_CAST(session_start_timestamp_string AS TIMESTAMP) AS session_start_timestamp,
    SAFE_CAST(session_end_timestamp_string AS TIMESTAMP) AS session_end_timestamp,
    SAFE_CAST(session_duration_seconds_string AS INT64) AS session_duration_seconds,
    LOWER(qualifying_session_string) IN ('true', '1') AS qualifying_session,
    qualification_rule_id,
    LOWER(active_learner_string) IN ('true', '1') AS active_learner,
    COALESCE(reporting_period_id, FORMAT_TIMESTAMP('%Y-%m', COALESCE(SAFE_CAST(event_timestamp_string AS TIMESTAMP), TIMESTAMP_MICROS(ga4_event_timestamp)))) AS reporting_period_id,
    COALESCE(
      SAFE_CAST(reporting_period_start_string AS TIMESTAMP),
      TIMESTAMP_TRUNC(COALESCE(SAFE_CAST(event_timestamp_string AS TIMESTAMP), TIMESTAMP_MICROS(ga4_event_timestamp)), MONTH)
    ) AS reporting_period_start,
    COALESCE(
      SAFE_CAST(reporting_period_end_string AS TIMESTAMP),
      TIMESTAMP_SUB(TIMESTAMP_ADD(TIMESTAMP_TRUNC(COALESCE(SAFE_CAST(event_timestamp_string AS TIMESTAMP), TIMESTAMP_MICROS(ga4_event_timestamp)), MONTH), INTERVAL 1 MONTH), INTERVAL 1 MICROSECOND)
    ) AS reporting_period_end,
    course_id,
    course_name,
    course_category,
    course_level,
    content_id,
    content_name,
    content_type,
    activity_id,
    activity_type,
    assessment_id,
    delivery_mode,
    classroom_id,
    instructor_id,
    enrollment_id,
    enrollment_status,
    SAFE_CAST(progress_percent_string AS NUMERIC) AS progress_percent,
    completion_status,
    SAFE_CAST(interaction_count_string AS INT64) AS interaction_count,
    SAFE_CAST(content_minutes_consumed_string AS NUMERIC) AS content_minutes_consumed,
    SAFE_CAST(assessment_attempt_number_string AS INT64) AS assessment_attempt_number,
    SAFE_CAST(assessment_score_string AS NUMERIC) AS assessment_score,
    COALESCE(parameter_device_type, ga4_device_type) AS device_type,
    COALESCE(parameter_country, ga4_country) AS country,
    language,
    consent_status
  FROM extracted_events
  WHERE COALESCE(environment, 'production') = 'production'
    AND COALESCE(consent_status, 'granted') = 'granted'
),
deduplicated_events AS (
  SELECT * EXCEPT(row_number)
  FROM (
    SELECT
      canonical_events.*,
      ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY event_timestamp DESC) AS row_number
    FROM canonical_events
  )
  WHERE row_number = 1
),
period_metrics AS (
  SELECT
    tenant_id,
    organization_id,
    account_id,
    reporting_period_id,
    reporting_period_start,
    reporting_period_end,
    COUNT(DISTINCT IF(
      qualifying_session
      AND learner_id IS NOT NULL
      AND COALESCE(learning_session_id, session_id) IS NOT NULL,
      CONCAT(learner_id, '|', COALESCE(learning_session_id, session_id)),
      NULL
    )) AS qualifying_learning_sessions,
    COUNT(DISTINCT IF(active_learner AND learner_id IS NOT NULL, learner_id, NULL)) AS active_learners
  FROM deduplicated_events
  GROUP BY
    tenant_id,
    organization_id,
    account_id,
    reporting_period_id,
    reporting_period_start,
    reporting_period_end
)
SELECT
  e.*,
  m.qualifying_learning_sessions,
  m.active_learners,
  SAFE_DIVIDE(m.qualifying_learning_sessions, m.active_learners) AS learning_session_frequency
FROM deduplicated_events AS e
JOIN period_metrics AS m
  USING (
    tenant_id,
    organization_id,
    account_id,
    reporting_period_id,
    reporting_period_start,
    reporting_period_end
  )
ORDER BY
  e.reporting_period_start,
  e.tenant_id,
  e.organization_id,
  e.account_id,
  e.event_timestamp;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH canonical_events AS (
  SELECT
    web.webInteraction.name AS event_name,
    COALESCE(_id, CONCAT('aep-', CAST(timestamp AS STRING), '-', COALESCE(identityMap['learnerId'][0].id, 'unknown'))) AS event_id,
    timestamp AS event_timestamp,
    _learningAnalytics.sourceSystem AS source_system,
    environment.type AS environment,
    _learningAnalytics.platform AS platform,
    COALESCE(_learningAnalytics.learnerId, identityMap['learnerId'][0].id) AS learner_id,
    _learningAnalytics.learnerIdType AS learner_id_type,
    _learningAnalytics.learnerStatus AS learner_status,
    COALESCE(_learningAnalytics.accountId, 'unknown') AS account_id,
    COALESCE(_learningAnalytics.organizationId, 'unknown') AS organization_id,
    COALESCE(_learningAnalytics.tenantId, 'unknown') AS tenant_id,
    _learningAnalytics.sessionId AS session_id,
    _learningAnalytics.learningSessionId AS learning_session_id,
    CAST(_learningAnalytics.sessionSequenceNumber AS BIGINT) AS session_sequence_number,
    CAST(_learningAnalytics.sessionStartTimestamp AS TIMESTAMP) AS session_start_timestamp,
    CAST(_learningAnalytics.sessionEndTimestamp AS TIMESTAMP) AS session_end_timestamp,
    CAST(_learningAnalytics.sessionDurationSeconds AS BIGINT) AS session_duration_seconds,
    COALESCE(CAST(_learningAnalytics.qualifyingSession AS BOOLEAN), FALSE) AS qualifying_session,
    _learningAnalytics.qualificationRuleId AS qualification_rule_id,
    COALESCE(CAST(_learningAnalytics.activeLearner AS BOOLEAN), FALSE) AS active_learner,
    COALESCE(_learningAnalytics.reportingPeriodId, DATE_FORMAT(timestamp, 'yyyy-MM')) AS reporting_period_id,
    COALESCE(
      CAST(_learningAnalytics.reportingPeriodStart AS TIMESTAMP),
      DATE_TRUNC('MONTH', timestamp)
    ) AS reporting_period_start,
    COALESCE(
      CAST(_learningAnalytics.reportingPeriodEnd AS TIMESTAMP),
      DATE_TRUNC('MONTH', timestamp) + INTERVAL '1' MONTH - INTERVAL '1' MILLISECOND
    ) AS reporting_period_end,
    _learningAnalytics.courseId AS course_id,
    _learningAnalytics.courseName AS course_name,
    _learningAnalytics.courseCategory AS course_category,
    _learningAnalytics.courseLevel AS course_level,
    _learningAnalytics.contentId AS content_id,
    _learningAnalytics.contentName AS content_name,
    _learningAnalytics.contentType AS content_type,
    _learningAnalytics.activityId AS activity_id,
    _learningAnalytics.activityType AS activity_type,
    _learningAnalytics.assessmentId AS assessment_id,
    _learningAnalytics.deliveryMode AS delivery_mode,
    _learningAnalytics.classroomId AS classroom_id,
    _learningAnalytics.instructorId AS instructor_id,
    _learningAnalytics.enrollmentId AS enrollment_id,
    _learningAnalytics.enrollmentStatus AS enrollment_status,
    CAST(_learningAnalytics.progressPercent AS DECIMAL(18,4)) AS progress_percent,
    _learningAnalytics.completionStatus AS completion_status,
    CAST(_learningAnalytics.interactionCount AS BIGINT) AS interaction_count,
    CAST(_learningAnalytics.contentMinutesConsumed AS DECIMAL(18,4)) AS content_minutes_consumed,
    CAST(_learningAnalytics.assessmentAttemptNumber AS BIGINT) AS assessment_attempt_number,
    CAST(_learningAnalytics.assessmentScore AS DECIMAL(18,4)) AS assessment_score,
    device.type AS device_type,
    placeContext.geo.countryCode AS country,
    environment.language AS language,
    _learningAnalytics.consentStatus AS consent_status
  FROM learning_analytics_events
  WHERE timestamp >= CAST('${START_TIMESTAMP}' AS TIMESTAMP)
    AND timestamp < CAST('${END_TIMESTAMP}' AS TIMESTAMP)
    AND web.webInteraction.name = 'select_content'
    AND COALESCE(environment.type, 'production') = 'production'
    AND COALESCE(_learningAnalytics.consentStatus, 'granted') = 'granted'
),
deduplicated_events AS (
  SELECT
    event_name,
    event_id,
    event_timestamp,
    source_system,
    environment,
    platform,
    learner_id,
    learner_id_type,
    learner_status,
    account_id,
    organization_id,
    tenant_id,
    session_id,
    learning_session_id,
    session_sequence_number,
    session_start_timestamp,
    session_end_timestamp,
    session_duration_seconds,
    qualifying_session,
    qualification_rule_id,
    active_learner,
    reporting_period_id,
    reporting_period_start,
    reporting_period_end,
    course_id,
    course_name,
    course_category,
    course_level,
    content_id,
    content_name,
    content_type,
    activity_id,
    activity_type,
    assessment_id,
    delivery_mode,
    classroom_id,
    instructor_id,
    enrollment_id,
    enrollment_status,
    progress_percent,
    completion_status,
    interaction_count,
    content_minutes_consumed,
    assessment_attempt_number,
    assessment_score,
    device_type,
    country,
    language,
    consent_status
  FROM (
    SELECT
      canonical_events.*,
      ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY event_timestamp DESC) AS row_number
    FROM canonical_events
  ) ranked_events
  WHERE row_number = 1
),
period_metrics AS (
  SELECT
    tenant_id,
    organization_id,
    account_id,
    reporting_period_id,
    reporting_period_start,
    reporting_period_end,
    COUNT(DISTINCT CASE
      WHEN qualifying_session = TRUE
       AND learner_id IS NOT NULL
       AND COALESCE(learning_session_id, session_id) IS NOT NULL
      THEN CONCAT(learner_id, '|', COALESCE(learning_session_id, session_id))
      ELSE NULL
    END) AS qualifying_learning_sessions,
    COUNT(DISTINCT CASE
      WHEN active_learner = TRUE AND learner_id IS NOT NULL THEN learner_id
      ELSE NULL
    END) AS active_learners
  FROM deduplicated_events
  GROUP BY
    tenant_id,
    organization_id,
    account_id,
    reporting_period_id,
    reporting_period_start,
    reporting_period_end
)
SELECT
  e.*,
  m.qualifying_learning_sessions,
  m.active_learners,
  CASE
    WHEN m.active_learners = 0 THEN NULL
    ELSE CAST(m.qualifying_learning_sessions AS DOUBLE) / CAST(m.active_learners AS DOUBLE)
  END AS learning_session_frequency
FROM deduplicated_events e
INNER JOIN period_metrics m
  ON e.tenant_id = m.tenant_id
 AND e.organization_id = m.organization_id
 AND e.account_id = m.account_id
 AND e.reporting_period_id = m.reporting_period_id
 AND e.reporting_period_start = m.reporting_period_start
 AND e.reporting_period_end = m.reporting_period_end
ORDER BY
  e.reporting_period_start,
  e.tenant_id,
  e.organization_id,
  e.account_id,
  e.event_timestamp;