Query

WITH trackings AS (
    SELECT 
        course,
        user,
        created_at,
        completed_at,
        DATEDIFF('minute', created_at, completed_at) AS training_time_minutes,
        progress,
        score,
        context_id,
        context_type,
        id
    FROM qwnogpy_ww21770_datasharing.core.COURSES_TRACKINGS 
),

users_data AS (
    SELECT 
        initial_users.id,
        initial_users.mail,
        initial_users.customfields,
    FROM qwnogpy_ww21770_datasharing.core.USERS initial_users,
    ),

final AS (
    SELECT 
        trackings.course || '_' || coalesce(trackings.context_id, trackings.context_type)  || '_' || trackings.user AS pk, -- Primary key (unique and non null)
        
        trackings.course AS course_id, -- ID of the course on the platform
        courses.name AS course_name, -- Name of the course on the platform
        courses.external_content['catalogId']::varchar AS course_external_catalog, -- External Catalog of the course (for external content)
        trackings.user AS user_id, -- ID of the user on the platform
        users_data.mail AS user_mail, -- Email of the user
        trackings.context_type, -- Type of context in which the course was launched (from a path or program session or free from the catalog)
        trackings.context_id, -- ID of the context in which the course was launched (if not null, the course was launched within a session and it is the ID of the session, if null, it means the course was launched free from the catalog)
        paths.name AS path_name, -- Name of the path from which the course was launched
        path_sessions.name AS path_session_name, -- Name of the path session from which the course was launched
    
        COUNT(DISTINCT trackings.id) AS nb_attempts, -- Number of attempts made by the user on the course
        MIN(trackings.created_at) AS started_at, -- Minimum timestamp at which the user launched the course
        MAX(trackings.completed_at) AS completed_at, -- Maximum timestamp at which the user completed the course
        SUM(training_time_minutes) AS total_training_time_minutes, -- Total minutes between the first started time and the last completed time on the course, by the user
        MAX(trackings.progress) AS progress, -- Maximum progress reached by the user on the course
        MAX(trackings.score) AS score, -- Maximum score reached by the user on the course
        
    FROM trackings
    
    LEFT JOIN users_data
        ON trackings.user = users_data.id
    
    LEFT JOIN qwnogpy_ww21770_datasharing.core.COURSES courses
        ON trackings.course = courses.id
    
    LEFT JOIN qwnogpy_ww21770_datasharing.core.PATHS_SESSIONS path_sessions
        ON trackings.context_id = path_sessions.id
        
    LEFT JOIN qwnogpy_ww21770_datasharing.core.PATHS paths
        ON path_sessions.path = paths.id
    
    GROUP BY ALL
)

SELECT 
    *
FROM final

Description