headless_lms_models/library/
global_stats.rs

1use super::TimeGranularity;
2use crate::prelude::*;
3
4#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
5#[cfg_attr(feature = "ts_rs", derive(TS))]
6pub struct GlobalStatEntry {
7    pub course_name: String,
8    pub course_id: Uuid,
9    pub organization_id: Uuid,
10    pub organization_name: String,
11    pub year: i32,
12    pub month: Option<i32>, // Will be None when granularity is Year
13    pub value: i64,
14}
15
16#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
17#[cfg_attr(feature = "ts_rs", derive(TS))]
18pub struct GlobalCourseModuleStatEntry {
19    pub course_name: String,
20    pub course_id: Uuid,
21    pub course_module_id: Uuid,
22    pub course_module_name: Option<String>,
23    pub organization_id: Uuid,
24    pub organization_name: String,
25    pub year: String,
26    pub value: i64,
27    pub course_module_ects_credits: Option<f32>,
28}
29
30#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
31#[cfg_attr(feature = "ts_rs", derive(TS))]
32pub struct DomainCompletionStats {
33    pub email_domain: String,
34    pub total_completions: i64,
35    pub unique_users: i64,
36    pub registered_completion_percentage: Option<f64>,
37    pub registered_completions: i64,
38    pub not_registered_completions: i64,
39    pub users_with_some_registered_completions: i64,
40    pub users_with_some_unregistered_completions: i64,
41    pub registered_ects_credits: f32,
42    pub not_registered_ects_credits: f32,
43}
44
45#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
46#[cfg_attr(feature = "ts_rs", derive(TS))]
47pub struct CourseCompletionStats {
48    pub course_id: Uuid,
49    pub course_name: String,
50    pub total_completions: i64,
51    pub unique_users: i64,
52    pub registered_completion_percentage: Option<f64>,
53    pub registered_completions: i64,
54    pub not_registered_completions: i64,
55    pub users_with_some_registered_completions: i64,
56    pub users_with_some_unregistered_completions: i64,
57    pub registered_ects_credits: f32,
58    pub not_registered_ects_credits: f32,
59}
60
61pub async fn get_number_of_people_completed_a_course(
62    conn: &mut PgConnection,
63    granularity: TimeGranularity,
64) -> ModelResult<Vec<GlobalStatEntry>> {
65    let res = sqlx::query_as!(
66        GlobalStatEntry,
67        r#"
68SELECT c.name AS course_name,
69  EXTRACT('year' FROM completion_date)::int as "year!",
70  CASE WHEN $1 = 'Month' THEN EXTRACT('month' FROM completion_date)::int ELSE NULL END as "month",
71  COUNT(DISTINCT user_id) as "value!",
72  c.id as "course_id!",
73  o.id as "organization_id",
74  o.name as "organization_name"
75FROM course_module_completions cmc
76JOIN courses c ON cmc.course_id = c.id
77JOIN organizations o ON c.organization_id = o.id
78WHERE cmc.deleted_at IS NULL
79  AND c.is_draft = FALSE
80  AND c.deleted_at IS NULL
81  AND c.is_test_mode = FALSE
82GROUP BY c.name, c.id, o.id, o.name, "year!", "month"
83ORDER BY c.id, "year!", "month"
84"#,
85        granularity.to_string()
86    )
87    .fetch_all(conn)
88    .await?;
89    Ok(res)
90}
91
92pub async fn get_number_of_people_registered_completion_to_study_registry(
93    conn: &mut PgConnection,
94    granularity: TimeGranularity,
95) -> ModelResult<Vec<GlobalStatEntry>> {
96    let res = sqlx::query_as!(
97        GlobalStatEntry,
98        r#"
99SELECT c.name AS course_name,
100  EXTRACT('year' FROM cms.completion_date)::int as "year!",
101  CASE WHEN $1 = 'Month' THEN EXTRACT('month' FROM cms.completion_date)::int ELSE NULL END as "month",
102  COUNT(DISTINCT cmcrtsr.user_id) as "value!",
103  c.id as "course_id!",
104  o.id as "organization_id",
105  o.name as "organization_name"
106FROM course_module_completion_registered_to_study_registries cmcrtsr
107JOIN course_module_completions cms ON cmcrtsr.course_module_completion_id = cms.id
108JOIN courses c ON cmcrtsr.course_id = c.id
109JOIN organizations o ON c.organization_id = o.id
110WHERE cmcrtsr.deleted_at IS NULL
111  AND c.is_draft = FALSE
112  AND c.deleted_at IS NULL
113  AND c.is_test_mode = FALSE
114GROUP BY c.name, c.id, o.id, o.name, "year!", "month"
115ORDER BY c.id, "year!", "month"
116"#,
117        granularity.to_string()
118    )
119    .fetch_all(conn)
120    .await?;
121    Ok(res)
122}
123
124pub async fn get_number_of_people_done_at_least_one_exercise(
125    conn: &mut PgConnection,
126    granularity: TimeGranularity,
127) -> ModelResult<Vec<GlobalStatEntry>> {
128    dbg!(&granularity);
129    let res = sqlx::query_as!(
130        GlobalStatEntry,
131        r#"
132SELECT c.name AS course_name,
133  EXTRACT('year' FROM ess.created_at)::int as "year!",
134  CASE WHEN $1 = 'Month' THEN EXTRACT('month' FROM ess.created_at)::int ELSE NULL END as "month",
135  COUNT(DISTINCT ess.user_id) as "value!",
136  c.id as "course_id!",
137  o.id as "organization_id",
138  o.name as "organization_name"
139FROM exercise_slide_submissions ess
140JOIN courses c ON ess.course_id = c.id
141JOIN organizations o ON c.organization_id = o.id
142WHERE ess.deleted_at IS NULL
143  AND c.is_draft = FALSE
144  AND c.deleted_at IS NULL
145  AND c.is_test_mode = FALSE
146GROUP BY c.name, c.id, o.id, o.name, "year!", "month"
147ORDER BY c.id, "year!", "month"
148"#,
149        granularity.to_string()
150    )
151    .fetch_all(conn)
152    .await?;
153    dbg!(&res);
154    Ok(res)
155}
156
157pub async fn get_number_of_people_started_course(
158    conn: &mut PgConnection,
159    granularity: TimeGranularity,
160) -> ModelResult<Vec<GlobalStatEntry>> {
161    let res = sqlx::query_as!(
162        GlobalStatEntry,
163        r#"
164SELECT c.name AS course_name,
165  EXTRACT('year' FROM cie.created_at)::int as "year!",
166  CASE WHEN $1 = 'Month' THEN EXTRACT('month' FROM cie.created_at)::int ELSE NULL END as "month",
167  COUNT(DISTINCT cie.user_id) as "value!",
168  c.id as "course_id!",
169  o.id as "organization_id",
170  o.name as "organization_name"
171FROM course_instance_enrollments cie
172JOIN courses c ON cie.course_id = c.id
173JOIN organizations o ON c.organization_id = o.id
174WHERE cie.deleted_at IS NULL
175  AND c.is_draft = FALSE
176  AND c.deleted_at IS NULL
177  AND c.is_test_mode = FALSE
178GROUP BY c.name, c.id, o.id, o.name, "year!", "month"
179ORDER BY c.id, "year!", "month"
180"#,
181        granularity.to_string()
182    )
183    .fetch_all(conn)
184    .await?;
185    Ok(res)
186}
187
188pub async fn get_course_module_stats_by_completions_registered_to_study_registry(
189    conn: &mut PgConnection,
190    granularity: TimeGranularity,
191) -> ModelResult<Vec<GlobalCourseModuleStatEntry>> {
192    let res = sqlx::query_as!(
193        GlobalCourseModuleStatEntry,
194        r#"
195SELECT c.name as course_name,
196  q.year as "year!",
197  q.value as "value!",
198  q.course_module_id as "course_module_id!",
199  c.id as "course_id",
200  cm.name as "course_module_name",
201  cm.ects_credits as "course_module_ects_credits",
202  o.id as "organization_id",
203  o.name as "organization_name"
204FROM (
205    SELECT cmcrtsr.course_module_id,
206      CASE WHEN $1 = 'Month' THEN
207        EXTRACT('year' FROM cms.completion_date)::VARCHAR || '-' || LPAD(EXTRACT('month' FROM cms.completion_date)::VARCHAR, 2, '0')
208      ELSE
209        EXTRACT('year' FROM cms.completion_date)::VARCHAR
210      END as year,
211      COUNT(DISTINCT cmcrtsr.user_id) as value
212    FROM course_module_completion_registered_to_study_registries cmcrtsr
213      JOIN course_module_completions cms ON cmcrtsr.course_module_completion_id = cms.id
214    WHERE cmcrtsr.deleted_at IS NULL
215    GROUP BY cmcrtsr.course_module_id,
216      year
217    ORDER BY cmcrtsr.course_module_id,
218      year
219  ) q
220  JOIN course_modules cm ON q.course_module_id = cm.id
221  JOIN courses c ON cm.course_id = c.id
222  JOIN organizations o ON c.organization_id = o.id
223WHERE c.is_draft = FALSE
224  AND c.deleted_at IS NULL
225  AND c.is_test_mode = FALSE
226"#,
227        granularity.to_string()
228    )
229    .fetch_all(conn)
230    .await?;
231    Ok(res)
232}
233
234/// Produces a summary of course completions grouped by user's email domain.
235///
236/// The query deduplicates multiple completions of the same (user, course module) by:
237/// 1. Preferring any completion that has a registration (exists in course_module_completion_registered_to_study_registries)
238/// 2. If no registered completion is found, it picks the completion with the newest created_at timestamp
239///
240/// The query aggregates the following counts and sums by email domain:
241///
242/// * `total_completions` - Number of unique completions (after deduplication) for the domain
243/// * `unique_users` - Number of distinct users (by user_id) in those completions
244/// * `registered_completion_percentage` - Fraction of completions that are registered (multiplied by 100)
245/// * `registered_completions` - Number of completions with a matching registration
246/// * `not_registered_completions` - Number of completions without a matching registration
247/// * `users_with_some_registered_completions` - Count of distinct users with at least one registered completion
248/// * `users_with_some_unregistered_completions` - Count of distinct users with at least one unregistered completion
249/// * `registered_ects_credits` - Total ECTS credits for registered completions
250/// * `not_registered_ects_credits` - Total ECTS credits for unregistered completions
251///
252/// # Arguments
253///
254/// * `year` - Optional year to filter completions by
255pub async fn get_completion_stats_by_email_domain(
256    conn: &mut PgConnection,
257    year: Option<i32>,
258) -> ModelResult<Vec<DomainCompletionStats>> {
259    let res = sqlx::query_as!(
260      DomainCompletionStats,
261      r#"
262WITH deduped_completions AS (
263SELECT *
264FROM (
265    SELECT cmc.*,
266      CASE
267        WHEN cmr.course_module_completion_id IS NOT NULL THEN 1
268        ELSE 0
269      END AS is_registered,
270      ROW_NUMBER() OVER (
271        PARTITION BY cmc.user_id,
272        cmc.course_module_id
273        ORDER BY CASE
274            WHEN cmr.course_module_completion_id IS NOT NULL THEN 1
275            ELSE 0
276          END DESC,
277          cmc.created_at DESC
278      ) AS rn
279    FROM course_module_completions cmc
280      LEFT JOIN course_module_completion_registered_to_study_registries cmr ON cmc.id = cmr.course_module_completion_id
281      AND cmr.deleted_at IS NULL
282    WHERE cmc.deleted_at IS NULL
283      AND (
284        $1::int IS NULL
285        OR EXTRACT(
286          YEAR
287          FROM cmc.completion_date
288        ) = $1
289      )
290  ) sub
291WHERE rn = 1
292),
293unique_registrations AS (
294SELECT DISTINCT course_module_completion_id
295FROM course_module_completion_registered_to_study_registries cmr
296WHERE cmr.deleted_at IS NULL
297)
298SELECT u.email_domain AS "email_domain!",
299COUNT(DISTINCT d.id) AS "total_completions!",
300COUNT(DISTINCT d.user_id) AS "unique_users!",
301ROUND(
302  (
303    SUM(
304      CASE
305        WHEN ur.course_module_completion_id IS NOT NULL THEN 1
306        ELSE 0
307      END
308    ) * 100.0
309  ) / NULLIF(COUNT(DISTINCT d.id), 0),
310  2
311)::float8 AS "registered_completion_percentage",
312SUM(
313  CASE
314    WHEN ur.course_module_completion_id IS NOT NULL THEN 1
315    ELSE 0
316  END
317) AS "registered_completions!",
318SUM(
319  CASE
320    WHEN ur.course_module_completion_id IS NULL THEN 1
321    ELSE 0
322  END
323) AS "not_registered_completions!",
324COUNT(
325  DISTINCT CASE
326    WHEN ur.course_module_completion_id IS NOT NULL THEN d.user_id
327  END
328) AS "users_with_some_registered_completions!",
329COUNT(
330  DISTINCT CASE
331    WHEN ur.course_module_completion_id IS NULL THEN d.user_id
332  END
333) AS "users_with_some_unregistered_completions!",
334COALESCE(
335  SUM(
336    CASE
337      WHEN ur.course_module_completion_id IS NOT NULL THEN cm.ects_credits
338      ELSE 0
339    END
340  ),
341  0
342) AS "registered_ects_credits!",
343COALESCE(
344  SUM(
345    CASE
346      WHEN ur.course_module_completion_id IS NULL THEN cm.ects_credits
347      ELSE 0
348    END
349  ),
350  0
351) AS "not_registered_ects_credits!"
352FROM deduped_completions d
353JOIN users u ON d.user_id = u.id
354AND u.deleted_at IS NULL
355LEFT JOIN unique_registrations ur ON d.id = ur.course_module_completion_id
356JOIN courses c ON d.course_id = c.id
357AND c.deleted_at IS NULL
358JOIN course_modules cm ON d.course_module_id = cm.id
359AND cm.deleted_at IS NULL
360WHERE d.prerequisite_modules_completed = TRUE
361AND c.is_draft = FALSE
362AND c.is_test_mode = FALSE
363AND cm.enable_registering_completion_to_uh_open_university = TRUE
364AND cm.ects_credits IS NOT NULL
365AND cm.ects_credits > 0
366GROUP BY u.email_domain
367ORDER BY "total_completions!" DESC,
368email_domain
369      "#,
370      year
371  )
372  .fetch_all(conn)
373  .await?;
374    Ok(res)
375}
376
377/// Gets course completion statistics for a specific email domain.
378///
379/// Similar to get_completion_stats_by_email_domain, but returns per-course statistics
380/// for a specific email domain instead of per-domain statistics.
381///
382/// # Arguments
383///
384/// * `email_domain` - The email domain to filter by (e.g. "gmail.com")
385/// * `year` - Optional year to filter completions by
386pub async fn get_course_completion_stats_for_email_domain(
387    conn: &mut PgConnection,
388    email_domain: String,
389    year: Option<i32>,
390) -> ModelResult<Vec<CourseCompletionStats>> {
391    let res = sqlx::query_as!(
392        CourseCompletionStats,
393        r#"
394WITH deduped_completions AS (
395  SELECT *
396  FROM (
397      SELECT cmc.*,
398        CASE
399          WHEN cmr.course_module_completion_id IS NOT NULL THEN 1
400          ELSE 0
401        END AS is_registered,
402        ROW_NUMBER() OVER (
403          PARTITION BY cmc.user_id,
404          cmc.course_module_id
405          ORDER BY CASE
406              WHEN cmr.course_module_completion_id IS NOT NULL THEN 1
407              ELSE 0
408            END DESC,
409            cmc.created_at DESC
410        ) AS rn
411      FROM course_module_completions cmc
412        LEFT JOIN course_module_completion_registered_to_study_registries cmr ON cmc.id = cmr.course_module_completion_id
413        AND cmr.deleted_at IS NULL
414      WHERE cmc.deleted_at IS NULL
415        AND (
416          $2::int IS NULL
417          OR EXTRACT(
418            YEAR
419            FROM cmc.completion_date
420          ) = $2
421        )
422    ) sub
423  WHERE rn = 1
424),
425unique_registrations AS (
426  SELECT DISTINCT course_module_completion_id
427  FROM course_module_completion_registered_to_study_registries cmr
428  WHERE cmr.deleted_at IS NULL
429)
430SELECT c.id AS "course_id!",
431  c.name AS "course_name!",
432  COUNT(DISTINCT d.id) AS "total_completions!",
433  COUNT(DISTINCT d.user_id) AS "unique_users!",
434  ROUND(
435    (
436      SUM(
437        CASE
438          WHEN ur.course_module_completion_id IS NOT NULL THEN 1
439          ELSE 0
440        END
441      ) * 100.0
442    ) / NULLIF(COUNT(DISTINCT d.id), 0),
443    2
444  )::float8 AS "registered_completion_percentage",
445  SUM(
446    CASE
447      WHEN ur.course_module_completion_id IS NOT NULL THEN 1
448      ELSE 0
449    END
450  ) AS "registered_completions!",
451  SUM(
452    CASE
453      WHEN ur.course_module_completion_id IS NULL THEN 1
454      ELSE 0
455    END
456  ) AS "not_registered_completions!",
457  COUNT(
458    DISTINCT CASE
459      WHEN ur.course_module_completion_id IS NOT NULL THEN d.user_id
460    END
461  ) AS "users_with_some_registered_completions!",
462  COUNT(
463    DISTINCT CASE
464      WHEN ur.course_module_completion_id IS NULL THEN d.user_id
465    END
466  ) AS "users_with_some_unregistered_completions!",
467  COALESCE(
468    SUM(
469      CASE
470        WHEN ur.course_module_completion_id IS NOT NULL THEN cm.ects_credits
471        ELSE 0
472      END
473    ),
474    0
475  ) AS "registered_ects_credits!",
476  COALESCE(
477    SUM(
478      CASE
479        WHEN ur.course_module_completion_id IS NULL THEN cm.ects_credits
480        ELSE 0
481      END
482    ),
483    0
484  ) AS "not_registered_ects_credits!"
485FROM deduped_completions d
486  JOIN users u ON d.user_id = u.id
487  AND u.deleted_at IS NULL
488  LEFT JOIN unique_registrations ur ON d.id = ur.course_module_completion_id
489  JOIN courses c ON d.course_id = c.id
490  AND c.deleted_at IS NULL
491  JOIN course_modules cm ON d.course_module_id = cm.id
492  AND cm.deleted_at IS NULL
493WHERE d.prerequisite_modules_completed = TRUE
494  AND c.is_draft = FALSE
495  AND c.is_test_mode = FALSE
496  AND cm.enable_registering_completion_to_uh_open_university = TRUE
497  AND cm.ects_credits IS NOT NULL
498  AND cm.ects_credits > 0
499  AND u.email_domain = $1
500GROUP BY c.id,
501  c.name
502ORDER BY "total_completions!" DESC,
503  c.id
504        "#,
505        email_domain,
506        year
507    )
508    .fetch_all(conn)
509    .await?;
510    Ok(res)
511}