Skip to content

Exporte le score moyen des activités TOEIC pour un container spécifique

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SELECT
    container.name,
    container.container_master_code AS exam_name,
    mode_item.id AS mode_item_id,
    parent_mode_item.meta::$section_type_code AS section_name,
    activity.id AS activity_id,
    activity.name AS activity_name,
    COUNT(user_activity.id) AS total_sessions,
    ROUND(AVG(total::$user_points), 0) AS avg_score
FROM
    user_activity
        LEFT JOIN activity ON activity.id = user_activity.activity_id
        LEFT JOIN container ON container.id = user_activity.container_id
        LEFT JOIN mode_item ON mode_item.id = user_activity.mode_item_id
        LEFT JOIN mode_item AS parent_mode_item ON parent_mode_item.id = mode_item.parent_id
WHERE
    user_activity.mode_id IN (
        SELECT
            mode_id
        FROM
            container_mode_pivot
                INNER JOIN mode ON mode.id = container_mode_pivot.mode_id
        WHERE
            container_id = 62
          AND mode.mode_type_code != "ADAPTIVE_MODE"
  AND mode.mode_type_code != "EVAL"
    )
  AND container_id = 62
  AND user_activity.ended_at IS NOT NULL
  AND user_activity.created_at BETWEEN "2025-01-01" AND "2025_06_20"
GROUP BY
    section_name, activity.name;

Keywords

average_activities_score container