Query

WITH 

learners_groups_listed AS (
    SELECT
        groups.id,
        groups.name,
        learners.value::varchar AS learner_id
    FROM qwnogpy_ww21770_datasharing.core.groups groups,
    TABLE(FLATTEN(groups.users)) learners
),

learners_groups_aggregated AS (
    SELECT 
        learner_id,
        ARRAY_AGG(id) AS group_ids,
        ARRAY_AGG(name) AS group_names
    FROM learners_groups_listed
    GROUP BY 1
)

SELECT 
    trackings.user AS learner_id, 
    users.mail AS learner_mail,
    trackings.path_session AS path_session_id, 
    sessions.name AS path_session_name, 
    paths.name AS path_name,
    trackings.enrolled_at,
    trackings.enrolled AS is_enrolled,
    trackings.start_date, 
    trackings.completed_at, 
    trackings.progress,
    learners_groups_aggregated.group_ids AS learner_group_ids,
    learners_groups_aggregated.group_names AS learner_group_names
    
FROM qwnogpy_ww21770_datasharing.core.PATHS_TRACKINGS trackings
LEFT JOIN qwnogpy_ww21770_datasharing.core.USERS users 
    ON trackings.user = users.id
LEFT JOIN qwnogpy_ww21770_datasharing.core.PATHS_SESSIONS sessions
    ON trackings.path_session = sessions.id
LEFT JOIN qwnogpy_ww21770_datasharing.core.PATHS paths
    ON trackings.path_id = paths.id
LEFT JOIN learners_groups_aggregated
    ON trackings.user = learners_groups_aggregated.learner_id
WHERE paths.name ='' -- Replace by the path name you need, or remove the filter
    AND sessions.name='' -- Replace by the session name you need, or remove the filter
    AND ARRAY_CONTAINS(''::variant, learner_group_names) -- Replace by the group name you need, or remove the filter
ORDER BY trackings.created_at desc;

Description

• learner_id: ID of the learner
• learner_mail: Mail of the learner
• path_session_id: ID of the path session
• path_session_name: Name of the path session
• path_name: Name of the path
• enrolled_at: Date when the learner was enrolled to the path session
• is_enrolled: It is true if the learner is enrolled in the path, no matter how they have been enrolled (assigned by admin, assign by manager, self-enrolled). It is false if the enrollment is archived
• completed_at: Date when the learner completed the path session
• progress: Progress of the learner of the path session
• learner_group_ids: List of the groups' IDs to which the user belongs as “learner”
• learner_group_names: List of the groups' names to which the user belongs as “learner”