Certification Insights

To replicate the Certification Activity table, you can use the following query:

WITH current_members AS (
      SELECT
          user_id
      FROM data_connector_1234.bridge_user_group
      WHERE ended_at IS NULL
    ),

    all_events AS (
      SELECT
          user_id,
          certification_id,
          event_name,
          occurred_at,
          attempt_id
      FROM data_connector_1234.fact_certification_events
      WHERE event_name IN ('certification_registered', 'certification_granted', 'certification_failed')
    ),

    registration_events AS (
      SELECT 
          user_id,
          certification_id,
          event_name,
          occurred_at,
          attempt_id
      FROM all_events
      WHERE event_name = 'certification_registered'
    ),

    granted_events AS (
      SELECT 
          user_id,
          certification_id,
          event_name,
          occurred_at,
          attempt_id
      FROM all_events
      WHERE event_name = 'certification_granted'
    ),

    failed_events AS (
        SELECT 
            user_id,
            certification_id,
            event_name,
            occurred_at,
            attempt_id
        FROM all_events
        WHERE event_name = 'certification_failed'
    ),

    all_events_with_status AS (
        SELECT
            coalesce(ge.user_id, re.user_id) AS user_id,
            coalesce(ge.certification_id, re.certification_id) AS certification_id,
            re.occurred_at AS started_at,
            ge.occurred_at AS ended_at,
            'granted' AS status
        FROM granted_events AS ge
        LEFT JOIN registration_events AS re 
            ON ge.attempt_id = re.attempt_id

        UNION ALL 

        SELECT                
            coalesce(fe.user_id, re.user_id) AS user_id,
            coalesce(fe.certification_id, re.certification_id) AS certification_id,
            re.occurred_at AS started_at,
            fe.occurred_at AS ended_at,
            'failed' AS status
        FROM failed_events AS fe
        LEFT JOIN registration_events AS re 
            ON fe.attempt_id = re.attempt_id

        UNION ALL 

        -- Everything else is considered as 'in progress'
        SELECT
            re.user_id AS user_id,
            re.certification_id AS certification_id,
            re.occurred_at AS started_at,
            NULL AS ended_at,
            'in_progress' AS status
        FROM registration_events AS re
        LEFT JOIN granted_events AS ge 
            ON re.attempt_id = ge.attempt_id
        LEFT JOIN failed_events AS fe 
            ON re.attempt_id = fe.attempt_id
        WHERE ge.user_id IS NULL 
            AND fe.user_id IS NULL
    )

SELECT 
    user.email,
    certification.certification_name,
    events.started_at,
    events.ended_at,
    events.status
FROM all_events_with_status AS events
INNER JOIN current_members AS members 
    ON events.user_id = members.user_id
LEFT JOIN data_connector_1234.dim_user AS user
    ON events.user_id = user.user_id
LEFT JOIN data_connector_1234.dim_certification AS certification
    ON events.certification_id = certification.certification_id
ORDER BY user.email