Content insights

SELECT 
    content.technology, 
    sum(events.xp_earned) AS total_xp
FROM data_connector_1234.fact_learn_events AS events 
INNER JOIN data_connector_1234.dim_content AS content 
  ON events.content_id = content.content_id
-- For legacy reasons, the report only shows course XP
WHERE events.event_name = 'course_engagement'
GROUP BY content.technology
ORDER BY total_xp DESC

WITH course_events AS (
        SELECT 
            content_id,
            event_name
        FROM data_connector_1234.fact_learn_events
        WHERE event_name IN ('course_started', 'course_completed')
    ),

    course_activity_counts AS (
        SELECT 
            content_id,
            count_if(event_name = 'course_started') AS starts,
            count_if(event_name = 'course_completed') AS completions
        FROM course_events
        GROUP BY content_id
    )

SELECT 
    content.content_title AS course,
    content.topic,
    content.technology,
    counts.starts,
    counts.completions,
    if(counts.starts = 0, 1, 
        round(100 * counts.completions / counts.starts, 2)) AS completion_rate
FROM course_activity_counts AS counts
INNER JOIN data_connector_1234.dim_content AS content
    ON counts.content_id = content.content_id
ORDER BY completions DESC

WITH project_events AS (
        SELECT 
            content_id,
            event_name
        FROM data_connector_1234.fact_learn_events
        WHERE event_name IN ('project_started', 'project_completed')
    ),

    project_activity_counts AS (
        SELECT 
            content_id,
            count_if(event_name = 'project_started') AS starts,
            count_if(event_name = 'project_completed') AS completions
        FROM project_events
        GROUP BY content_id
    )

SELECT 
    content.content_title AS project,
    content.technology,
    counts.starts,
    counts.completions,
    if(counts.starts = 0, 1, 
        round(100 * counts.completions / counts.starts, 2)) AS completion_rate
FROM project_activity_counts AS counts
INNER JOIN data_connector_1234.dim_content AS content
    ON counts.content_id = content.content_id
ORDER BY completions DESC

WITH track_names AS (
        SELECT DISTINCT
            track_id,
            /* Every time a particular track is updated, it gets a new track_version_id 
            while keeping the same track_id. Below, we keep the latest track_title
            and track_technology for each track_id */
            first_value(trim(track_title)) 
                OVER(PARTITION BY track_id 
                    ORDER BY track_version_id DESC) AS track_title,
            first_value(track_technology) 
                OVER(PARTITION BY track_id                            
                    ORDER BY track_version_id DESC) AS track_technology
        FROM data_connector_1234.dim_track
    ),
    
    track_events AS (
        SELECT 
            track_version_id,
            event_name
        FROM data_connector_1234.fact_learn_events
        WHERE event_name IN ('track_started', 'track_completed')
    ),

    track_activity_counts AS (
        SELECT 
            track_version_id,
            count_if(event_name = 'track_started') AS enrollments,
            count_if(event_name = 'track_completed') AS completions
        FROM track_events
        GROUP BY track_version_id
    ),

    track_activity_metrics AS (
        SELECT 
            track.track_id,
            track.track_category as track_type,
            sum(counts.enrollments) as enrollments,
            sum(counts.completions) as completions,
            if(sum(counts.enrollments) = 0, 1, 
                round(100* sum(counts.completions) / sum(counts.enrollments), 2)) AS completion_rate
        FROM track_activity_counts AS counts
        INNER JOIN data_connector_1234.dim_track AS track
            ON counts.track_version_id = track.track_version_id
        GROUP BY track.track_id, track.track_category
    )

SELECT
    /* The line below is to format the track names the 
    same way they appear in the Tracks Activity report */
    if(tracks.track_technology IS NULL, 
        tracks.track_title, 
        concat(tracks.track_title, ' (', tracks.track_technology, ')')
      ) AS track,
    metrics.track_type,
    metrics.enrollments,
    metrics.completions,
    metrics.completion_rate
FROM track_activity_metrics AS metrics
LEFT JOIN track_names AS tracks
    ON metrics.track_id = tracks.track_id
ORDER BY completions DESC