# Certification Insights

<figure><img src="/files/pmOFojaKs66OxFu5VkWq" alt=""><figcaption></figcaption></figure>

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

```sql
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
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://enterprise-docs.datacamp.com/integrating-our-data-into-your-tools-via-data-connector-2.0/queries-to-recreate-key-reports-in-the-groups-tab/reporting-section/certification-insights.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.