Query

WITH paths_questions AS (
    SELECT 
        p.id AS path_id,
        c.id AS course_id,
        c.name AS course_name,
        q.id AS question_id,
        q.name AS question_name
        
    FROM paths p
    CROSS JOIN LATERAL FLATTEN(input => p.steps) fp
    LEFT JOIN courses c 
        ON c.id = fp.value['courseId']['$oid']
    CROSS JOIN LATERAL FLATTEN(input => c.elements) fc
    LEFT JOIN questions q 
        ON fc.value['_id']['$oid'] = q.id

    WHERE fc.value['collection'] = 'questions' AND q.qtype IN ('questionVideoPitch', 'questionScreencastDemo', 'questionOpen')
),

users_responses AS (
    SELECT 
        r.author_id,
        r.question_id,
        s.status

    FROM responses r
    LEFT JOIN openresponsesubmissions s 
        ON r.id = s.response_id

    WHERE r.question_type IN ('questionVideoPitch', 'questionScreencastDemo', 'questionOpen')

    QUALIFY ROW_NUMBER() OVER (PARTITION BY r.id ORDER BY s.created_at DESC) = 1
),

users_paths AS (
    SELECT 
        u.id AS user_id,
        u.mail,
        u.first_name,
        u.last_name,
        p.id AS path_id,
        p.name AS path_name,
        CASE 
            WHEN pt.start_date IS NULL THEN 'Not yet started'
            WHEN pt.completed_at IS NULL THEN 'On track' 
            ELSE 'Completed'
        END AS status,
        pt.enrolled_at,
        pt.is_self_enrolled,
        pt.start_date,
        pt.completed_at,
        pt.score,
        pt.result

    FROM paths_trackings pt
    INNER JOIN users u
        ON users.id = paths_trackings.learner_id
    INNER JOIN paths p
        ON paths.id = paths_trackings.path_id
),

final AS (
    SELECT
        up.user_id,
        up.mail,
        up.first_name,
        up.last_name,
        up.path_id,
        up.path_name,
        up.status AS attempt_status,
        up.enrolled_at,
        up.is_self_enrolled,
        up.start_date,
        up.completed_at,
        up.score,
        COUNT(pq.question_id) AS nb_to_submit,
        COUNT(ur.author_id) AS nb_submitted,
        COUNT(CASE WHEN ur.status != 'toAssess' THEN 1 ELSE NULL END) AS nb_reviewed,
        COUNT(CASE WHEN ur.status = 'success' THEN 1 ELSE NULL END) AS nb_validated,
        CASE WHEN nb_to_submit = nb_validated THEN 'Validated' ELSE 'Missing actions' END AS submission_status,
        ARRAY_AGG(CASE WHEN (pq.question_id IS NOT NULL AND NOT ur.status = 'success') THEN OBJECT_CONSTRUCT('course_name', pq.course_name, 'question_name', pq.question_name, 'status', COALESCE(ur.status, 'toSubmit')) ELSE NULL END) AS blockers

    FROM users_paths up
    LEFT JOIN paths_questions pq 
        ON up.path_id = pq.path_id
    LEFT JOIN users_responses ur 
        ON up.user_id = ur.author_id 
        AND pq.question_id = ur.question_id

    GROUP BY 1,2,3,4,5,6
)

SELECT 
    mail,
    first_name, 
    last_name,
    path_name,
    attempt_status,
    enrolled_at,
    is_self_enrolled,
    start_date,
    completed_at,
    score,
    submission_status,
    blockers,
    nb_to_submit,
    nb_submitted,
    nb_reviewed,
    nb_validated,
    
FROM final 

WHERE NOT (attempt_status = 'Completed' AND submission_status = 'Validated');

Description

This query retrieves all users and their paths that are either incomplete or contain answers that are not fully validated (pending submission, under review, or requiring a retry).

Fields

• mail: Mail of the learner
• first_name: First name of the learner
• last_name : Last name of the learner
• path_name: Name of the path of this path
• attempt_status : Result of the learner on the path
• enrolled_at : Timestamp when the learner was enrolled to the path session
• is_self_enrolled: TRUE if the learner self-enrolled to the path session
• start_date : Date when the learner started the path session
• completed_at : Date when the learner achieved the path session (if any)
• score : Score of the learner on the path session
• submission_status: ‘Validated’ if all expected contents have been submitted and validated
• blockers: If some actions are still needed, it lists the course, question and state for all pending contents
• nb_to_submit: Total number of ‘video’ / ‘screencast’ / ‘open questions’ in the path where content is expected
• nb_submitted: Total number of questions where content have been submitted