Query

WITH 

filtered_customfields AS (
    SELECT 
        users.id,
        flattened_customfields.value['value']::varchar AS custom_field_value
    FROM qwnogpy_ww21770_datasharing.core.users,
    TABLE(FLATTEN(users.customfields)) as flattened_customfields
    WHERE flattened_customfields.value['name']::varchar='Area'-- Replace by your custom field name
),

raw_co_authors AS (
    SELECT
        paths.id,
        co_authors_flattened.value::varchar AS co_author_id
    FROM qwnogpy_ww21770_datasharing.core.paths,
    TABLE(FLATTEN(paths.co_authors)) co_authors_flattened
    WHERE paths.co_authors is not null 
),

co_authors AS (
    SELECT 
        raw_co_authors.id, 
        ARRAY_AGG(users.mail) AS co_author_mails
    FROM raw_co_authors
    LEFT JOIN qwnogpy_ww21770_datasharing.core.users 
        ON raw_co_authors.co_author_id = users.id 
    GROUP BY 1
),

raw_tags AS (
    SELECT 
        paths.id,
        tags_flattened.value::varchar AS tag_id
    FROM qwnogpy_ww21770_datasharing.core.paths,
    TABLE(FLATTEN(paths.tags)) tags_flattened
    WHERE paths.tags is not null 
),

tags AS (
    SELECT 
        raw_tags.id,
        ARRAY_AGG(tags.name) AS tag_names,
    FROM raw_tags
    LEFT JOIN qwnogpy_ww21770_datasharing.core.tags
        ON raw_tags.tag_id = tags.id 
    GROUP BY 1
),

sessions AS (
    SELECT
        id,
        MEDIAN(case when completed_at is not null then time_spent end) AS median_time_spent_on_session,
        COUNT(distinct case when progress>0 then user end) AS nb_learners_on_session
    FROM qwnogpy_ww21770_datasharing.core.paths_trackings
    GROUP BY 1
)

SELECT 
    paths.id AS path_id,
    paths.name AS path_name,
    paths.owner_group AS path_owner_group_id,
    paths_groups.name AS path_owner_group_name,
    authors.mail AS author_mail,
    filtered_customfields.custom_field_value, -- Rename by the name of the custom field
    co_authors.co_author_mails,
    paths.tags,
    tags.tag_names,
    paths.duration,
    paths_sessions.id AS session_id,
    paths_sessions.name AS session_name,
    paths_sessions.owner_group AS session_owner_group,
    sessions_groups.name AS session_owner_group_name,
    sessions.median_time_spent_on_session,
    sessions.nb_learners_on_session,
    case when paths_sessions.end_date > current_date or paths_sessions.end_date is null then true else false end as is_open_session,
    paths_sessions.start_date AS session_start_date,
    paths_sessions.end_date AS session_end_date,
    paths_sessions.main_instructor AS main_instructor_id,
    main_instructors.mail AS main_instructor_mail
FROM qwnogpy_ww21770_datasharing.core.paths 
LEFT JOIN qwnogpy_ww21770_datasharing.core.groups AS paths_groups
    ON paths.owner_group = paths_groups.id 
LEFT JOIN qwnogpy_ww21770_datasharing.core.users AS authors
    ON paths.author = authors.id 
LEFT JOIN filtered_customfields
    ON authors.id = filtered_customfields.id
LEFT JOIN co_authors
    ON paths.id = co_authors.id 
LEFT JOIN tags
    ON paths.id = tags.id 
LEFT JOIN qwnogpy_ww21770_datasharing.core.paths_sessions
    ON paths.id = paths_sessions.path 
LEFT JOIN qwnogpy_ww21770_datasharing.core.groups AS sessions_groups
    ON paths_sessions.owner_group = sessions_groups.id 
LEFT JOIN sessions
    ON paths_sessions.id = sessions.id 
LEFT JOIN qwnogpy_ww21770_datasharing.core.users AS main_instructors
    ON paths_sessions.main_instructor = main_instructors.id 

;

Description

• path_id : ID of the path
• path_name: Name of the path
• path_owner_group_id: ID of the path’s owner group
• path_owner_group_name: Name of the path’s owner group
• author_mail: Author mail
• custom_field_value: Custom value of the custom field filtered in the CTE filtered_customfields
• co_author_mails: List of co-authors mails
• tags: List of the tag IDs of the path
• tag_names: List of the tag names of the path
• duration: Duration of the path
• session_id: ID of the session from the path
• session_name: Name of the session from the path
• session_owner_group_id: ID of the session’s owner group
• session_owner_group_name: Name of the session’s owner group
• median_time_spent_on_session: Median time of the time spent on the session by learners (only when they completed the session!)
• nb_learners_on_session: Number of learners who started the session (where their progress is strictly above 0)