headless_lms_models/library/
course_stats.rs

1use crate::library::TimeGranularity;
2use crate::{prelude::*, roles::UserRole};
3use std::collections::HashMap;
4
5/// A generic result representing a count metric over a time period.
6/// When the time period is not applicable (for overall totals), `period` will be `None`.
7#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
8#[cfg_attr(feature = "ts_rs", derive(TS))]
9pub struct CountResult {
10    /// The start of the time period (e.g., day, week, month) associated with this count.
11    /// For overall totals, this will be `None`.
12    pub period: Option<DateTime<Utc>>,
13    /// The count (for example, the number of users).
14    pub count: i64,
15}
16
17/// A generic result representing an average metric over a time period.
18/// The average value (e.g. average time in seconds) may be absent if no data is available.
19#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
20#[cfg_attr(feature = "ts_rs", derive(TS))]
21pub struct AverageMetric {
22    /// The start of the time period (e.g., day, week, month) associated with this metric.
23    pub period: Option<DateTime<Utc>>,
24    /// The average value. For example, the average time (in seconds) from course start to first submission.
25    pub average: Option<f64>,
26}
27
28/// Represents cohort activity metrics for both weekly and daily cohorts.
29/// For daily cohorts, `offset` will be populated (and `activity_period` may be computed from it);
30/// for weekly cohorts, `offset` will be `None` and `activity_period` indicates the week start.
31#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
32#[cfg_attr(feature = "ts_rs", derive(TS))]
33pub struct CohortActivity {
34    /// The start date of the cohort (either day or week).
35    pub cohort_start: Option<DateTime<Utc>>,
36    /// The activity period (for example, the start of the week or the computed activity day).
37    pub activity_period: Option<DateTime<Utc>>,
38    /// The day offset from the cohort start (only applicable for daily cohorts).
39    pub offset: Option<i32>,
40    /// The number of active users in this cohort for the given period.
41    pub active_users: i64,
42}
43
44/// Gets user IDs to exclude from course statistics for a single course.
45/// Excludes users with any role other than MaterialViewer in the course, its organization, or globally.
46async fn get_user_ids_to_exclude_from_course_stats(
47    conn: &mut PgConnection,
48    course_id: Uuid,
49) -> ModelResult<Vec<Uuid>> {
50    let roles = crate::roles::get_course_related_roles(conn, course_id).await?;
51    let user_ids: Vec<_> = roles
52        .iter()
53        .filter(|role| role.role != UserRole::MaterialViewer)
54        .map(|role| role.user_id)
55        .collect::<std::collections::HashSet<_>>()
56        .into_iter()
57        .collect();
58    Ok(user_ids)
59}
60
61/// Gets user IDs to exclude from course language group statistics.
62/// Uses a single query to get all roles and filters out MaterialViewer roles.
63async fn get_user_ids_to_exclude_from_course_language_group_stats(
64    conn: &mut PgConnection,
65    course_language_group_id: Uuid,
66) -> ModelResult<Vec<Uuid>> {
67    let roles =
68        crate::roles::get_course_language_group_related_roles(conn, course_language_group_id)
69            .await?;
70    let user_ids: Vec<_> = roles
71        .iter()
72        .filter(|role| role.role != UserRole::MaterialViewer)
73        .map(|role| role.user_id)
74        .collect::<std::collections::HashSet<_>>()
75        .into_iter()
76        .collect();
77    Ok(user_ids)
78}
79
80/// Total unique users in the course settings table.
81pub async fn get_total_users_started_course(
82    conn: &mut PgConnection,
83    course_id: Uuid,
84) -> ModelResult<CountResult> {
85    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
86    let res = sqlx::query_as!(
87        CountResult,
88        r#"
89SELECT NULL::timestamptz AS "period",
90       COUNT(DISTINCT user_id) AS "count!"
91FROM user_course_settings
92WHERE current_course_id = $1
93  AND deleted_at IS NULL
94  AND user_id != ALL($2);
95        "#,
96        course_id,
97        &exclude_user_ids
98    )
99    .fetch_one(conn)
100    .await?;
101    Ok(res)
102}
103
104/// Total unique users who have completed the course.
105pub async fn get_total_users_completed_course(
106    conn: &mut PgConnection,
107    course_id: Uuid,
108) -> ModelResult<CountResult> {
109    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
110    let res = sqlx::query_as!(
111        CountResult,
112        r#"
113SELECT NULL::timestamptz AS "period",
114       COUNT(DISTINCT user_id) AS "count!"
115FROM course_module_completions
116WHERE course_id = $1
117  AND deleted_at IS NULL
118  AND user_id != ALL($2);
119        "#,
120        course_id,
121        &exclude_user_ids
122    )
123    .fetch_one(conn)
124    .await?;
125    Ok(res)
126}
127
128/// Total unique users who have returned at least one exercise.
129pub async fn get_total_users_returned_at_least_one_exercise(
130    conn: &mut PgConnection,
131    course_id: Uuid,
132) -> ModelResult<CountResult> {
133    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
134    let res = sqlx::query_as!(
135        CountResult,
136        r#"
137SELECT NULL::timestamptz AS "period",
138  COUNT(DISTINCT user_id) AS "count!"
139FROM exercise_slide_submissions
140WHERE course_id = $1
141  AND deleted_at IS NULL
142  AND user_id != ALL($2);
143      "#,
144        course_id,
145        &exclude_user_ids
146    )
147    .fetch_one(conn)
148    .await?;
149    Ok(res)
150}
151
152/// Total unique users who have completed the course in all language versions
153pub async fn get_total_users_completed_all_language_versions_of_a_course(
154    conn: &mut PgConnection,
155    course_language_group_id: Uuid,
156) -> ModelResult<CountResult> {
157    let exclude_user_ids =
158        get_user_ids_to_exclude_from_course_language_group_stats(conn, course_language_group_id)
159            .await?;
160
161    let res = sqlx::query_as!(
162        CountResult,
163        r#"
164SELECT NULL::timestamptz AS "period",
165  COUNT(DISTINCT user_id) AS "count!"
166FROM course_module_completions
167WHERE course_id IN (
168    SELECT id
169    FROM courses
170    WHERE course_language_group_id = $1
171      AND deleted_at IS NULL
172  )
173  AND deleted_at IS NULL
174  AND user_id != ALL($2);
175    "#,
176        course_language_group_id,
177        &exclude_user_ids
178    )
179    .fetch_one(conn)
180    .await?;
181    Ok(res)
182}
183
184/// Total unique users who have started the course in all language versions
185pub async fn get_total_users_started_all_language_versions_of_a_course(
186    conn: &mut PgConnection,
187    course_language_group_id: Uuid,
188) -> ModelResult<CountResult> {
189    let exclude_user_ids =
190        get_user_ids_to_exclude_from_course_language_group_stats(conn, course_language_group_id)
191            .await?;
192
193    let res = sqlx::query_as!(
194        CountResult,
195        r#"
196SELECT NULL::timestamptz AS "period",
197  COUNT(DISTINCT user_id) AS "count!"
198FROM user_course_settings
199WHERE current_course_id IN (
200    SELECT id
201    FROM courses
202    WHERE course_language_group_id = $1
203      AND deleted_at IS NULL
204  )
205  AND deleted_at IS NULL
206  AND user_id != ALL($2);
207    "#,
208        course_language_group_id,
209        &exclude_user_ids
210    )
211    .fetch_one(conn)
212    .await?;
213    Ok(res)
214}
215
216/// Get unique users starting counts with specified time granularity.
217///
218/// The time_window parameter controls how far back to look:
219/// - For Year granularity: number of years
220/// - For Month granularity: number of months
221/// - For Day granularity: number of days
222pub async fn unique_users_starting_history(
223    conn: &mut PgConnection,
224    course_id: Uuid,
225    granularity: TimeGranularity,
226    time_window: u16,
227) -> ModelResult<Vec<CountResult>> {
228    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
229    let (interval_unit, time_unit) = granularity.get_sql_units();
230
231    let res = sqlx::query_as!(
232        CountResult,
233        r#"
234SELECT DATE_TRUNC($5, created_at) AS "period",
235  COUNT(DISTINCT user_id) AS "count!"
236FROM user_course_settings
237WHERE current_course_id = $1
238  AND deleted_at IS NULL
239  AND NOT user_id = ANY($2)
240  AND created_at >= NOW() - ($3 || ' ' || $4)::INTERVAL
241GROUP BY "period"
242ORDER BY "period"
243        "#,
244        course_id,
245        &exclude_user_ids,
246        &time_window.to_string(),
247        interval_unit,
248        time_unit,
249    )
250    .fetch_all(conn)
251    .await?;
252
253    Ok(res)
254}
255
256/// Get first exercise submission counts with specified time granularity.
257///
258/// The time_window parameter controls how far back to look:
259/// - For Year granularity: number of years
260/// - For Month granularity: number of months
261/// - For Day granularity: number of days
262pub async fn first_exercise_submissions_history(
263    conn: &mut PgConnection,
264    course_id: Uuid,
265    granularity: TimeGranularity,
266    time_window: u16,
267) -> ModelResult<Vec<CountResult>> {
268    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
269    let (interval_unit, time_unit) = granularity.get_sql_units();
270
271    let res = sqlx::query_as!(
272        CountResult,
273        r#"
274SELECT DATE_TRUNC($5, first_submission) AS "period",
275  COUNT(user_id) AS "count!"
276FROM (
277    SELECT user_id,
278      MIN(created_at) AS first_submission
279    FROM exercise_slide_submissions
280    WHERE course_id = $1
281      AND deleted_at IS NULL
282      AND NOT user_id = ANY($2)
283      AND created_at >= NOW() - ($3 || ' ' || $4)::INTERVAL
284    GROUP BY user_id
285  ) AS first_submissions
286GROUP BY "period"
287ORDER BY "period"
288        "#,
289        course_id,
290        &exclude_user_ids,
291        &time_window.to_string(),
292        interval_unit,
293        time_unit,
294    )
295    .fetch_all(conn)
296    .await?;
297
298    Ok(res)
299}
300
301/// Get users returning exercises counts with specified time granularity.
302///
303/// The time_window parameter controls how far back to look:
304/// - For Year granularity: number of years
305/// - For Month granularity: number of months
306/// - For Day granularity: number of days
307pub async fn users_returning_exercises_history(
308    conn: &mut PgConnection,
309    course_id: Uuid,
310    granularity: TimeGranularity,
311    time_window: u16,
312) -> ModelResult<Vec<CountResult>> {
313    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
314    let (interval_unit, time_unit) = granularity.get_sql_units();
315
316    let res = sqlx::query_as!(
317        CountResult,
318        r#"
319SELECT DATE_TRUNC($5, created_at) AS "period",
320  COUNT(DISTINCT user_id) AS "count!"
321FROM exercise_slide_submissions
322WHERE course_id = $1
323  AND deleted_at IS NULL
324  AND NOT user_id = ANY($2)
325  AND created_at >= NOW() - ($3 || ' ' || $4)::INTERVAL
326GROUP BY "period"
327ORDER BY "period"
328        "#,
329        course_id,
330        &exclude_user_ids,
331        &time_window.to_string(),
332        interval_unit,
333        time_unit,
334    )
335    .fetch_all(conn)
336    .await?;
337
338    Ok(res)
339}
340
341/// Get average time from course start to first exercise submission with specified time granularity.
342///
343/// The time_window parameter controls how far back to look:
344/// - For Year granularity: number of years
345/// - For Month granularity: number of months
346/// - For Day granularity: number of days
347///
348/// Returns the average time in seconds.
349pub async fn avg_time_to_first_submission_history(
350    conn: &mut PgConnection,
351    course_id: Uuid,
352    granularity: TimeGranularity,
353    time_window: u16,
354) -> ModelResult<Vec<AverageMetric>> {
355    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
356    let (interval_unit, time_unit) = granularity.get_sql_units();
357
358    let res = sqlx::query_as!(
359        AverageMetric,
360        r#"
361SELECT DATE_TRUNC($5, user_start) AS "period",
362  AVG(
363    EXTRACT(
364      EPOCH
365      FROM (first_submission - user_start)
366    )
367  )::float8 AS "average"
368FROM (
369    SELECT u.user_id,
370      MIN(u.created_at) AS user_start,
371      MIN(e.created_at) AS first_submission
372    FROM user_course_settings u
373      JOIN exercise_slide_submissions e ON u.user_id = e.user_id
374      AND e.course_id = $1
375      AND e.deleted_at IS NULL
376    WHERE u.current_course_id = $1
377      AND u.deleted_at IS NULL
378      AND NOT u.user_id = ANY($2)
379      AND u.created_at >= NOW() - ($3 || ' ' || $4)::INTERVAL
380    GROUP BY u.user_id
381  ) AS timings
382GROUP BY "period"
383ORDER BY "period"
384        "#,
385        course_id,
386        &exclude_user_ids,
387        &time_window.to_string(),
388        interval_unit,
389        time_unit,
390    )
391    .fetch_all(conn)
392    .await?;
393
394    Ok(res)
395}
396
397/// Get cohort activity statistics with specified time granularity.
398///
399/// Parameters:
400/// - history_window: How far back to look for cohorts
401/// - tracking_window: How long to track activity after each cohort's start
402///
403/// For each granularity:
404/// - Year: windows in years, tracking monthly activity
405/// - Month: windows in months, tracking weekly activity
406/// - Day: windows in days, tracking daily activity
407///
408/// Cohorts are defined by when users first submitted an exercise.
409pub async fn get_cohort_activity_history(
410    conn: &mut PgConnection,
411    course_id: Uuid,
412    granularity: TimeGranularity,
413    history_window: u16,
414    tracking_window: u16,
415) -> ModelResult<Vec<CohortActivity>> {
416    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
417    let (interval_unit, time_unit) = granularity.get_sql_units();
418
419    Ok(sqlx::query_as!(
420        CohortActivity,
421        r#"
422WITH first_activity AS (
423  SELECT user_id,
424    MIN(DATE_TRUNC($6, created_at)) AS first_active_at
425  FROM exercise_slide_submissions
426  WHERE course_id = $1
427    AND created_at >= NOW() - ($3 || ' ' || $4)::INTERVAL
428    AND deleted_at IS NULL
429    AND NOT user_id = ANY($2)
430  GROUP BY user_id
431),
432cohort AS (
433  SELECT user_id,
434    first_active_at AS cohort_start
435  FROM first_activity
436)
437SELECT c.cohort_start AS "cohort_start",
438  DATE_TRUNC($6, s.created_at) AS "activity_period",
439  CASE
440    WHEN $6 = 'day' THEN EXTRACT(
441      DAY
442      FROM (DATE_TRUNC('day', s.created_at) - c.cohort_start)
443    )::integer
444    WHEN $6 = 'week' THEN EXTRACT(
445      WEEK
446      FROM (
447          DATE_TRUNC('week', s.created_at) - c.cohort_start
448        )
449    )::integer
450    WHEN $6 = 'month' THEN (
451      EXTRACT(
452        YEAR
453        FROM s.created_at
454      ) - EXTRACT(
455        YEAR
456        FROM c.cohort_start
457      )
458    )::integer * 12 + (
459      EXTRACT(
460        MONTH
461        FROM s.created_at
462      ) - EXTRACT(
463        MONTH
464        FROM c.cohort_start
465      )
466    )::integer
467    ELSE NULL::integer
468  END AS "offset",
469  COUNT(DISTINCT s.user_id) AS "active_users!"
470FROM cohort c
471  JOIN exercise_slide_submissions s ON (
472    c.user_id = s.user_id
473    AND s.course_id = $1
474  )
475  AND s.created_at >= c.cohort_start
476  AND s.created_at < c.cohort_start + ($5 || ' ' || $4)::INTERVAL
477  AND s.deleted_at IS NULL
478GROUP BY c.cohort_start,
479  "activity_period",
480  "offset"
481ORDER BY c.cohort_start,
482  "offset"
483        "#,
484        course_id,
485        &exclude_user_ids,
486        &history_window.to_string(),
487        interval_unit,
488        &tracking_window.to_string(),
489        time_unit,
490    )
491    .fetch_all(conn)
492    .await?)
493}
494
495/// Get course completion counts with specified time granularity.
496///
497/// The time_window parameter controls how far back to look:
498/// - For Year granularity: number of years
499/// - For Month granularity: number of months
500/// - For Day granularity: number of days
501pub async fn course_completions_history(
502    conn: &mut PgConnection,
503    course_id: Uuid,
504    granularity: TimeGranularity,
505    time_window: u16,
506) -> ModelResult<Vec<CountResult>> {
507    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
508    let (interval_unit, time_unit) = granularity.get_sql_units();
509
510    let res = sqlx::query_as!(
511        CountResult,
512        r#"
513SELECT DATE_TRUNC($5, created_at) AS "period",
514  COUNT(DISTINCT user_id) AS "count!"
515FROM course_module_completions
516WHERE course_id = $1
517  AND prerequisite_modules_completed = TRUE
518  AND needs_to_be_reviewed = FALSE
519  AND passed = TRUE
520  AND deleted_at IS NULL
521  AND NOT user_id = ANY($2)
522  AND created_at >= NOW() - ($3 || ' ' || $4)::INTERVAL
523GROUP BY "period"
524ORDER BY "period"
525          "#,
526        course_id,
527        &exclude_user_ids,
528        &time_window.to_string(),
529        interval_unit,
530        time_unit,
531    )
532    .fetch_all(conn)
533    .await?;
534
535    Ok(res)
536}
537
538/// Get completion counts for all language versions of a course with specified time granularity.
539///
540/// The time_window parameter controls how far back to look:
541/// - For Year granularity: number of years
542/// - For Month granularity: number of months
543/// - For Day granularity: number of days
544pub async fn course_completions_history_all_language_versions(
545    conn: &mut PgConnection,
546    course_language_group_id: Uuid,
547    granularity: TimeGranularity,
548    time_window: u16,
549) -> ModelResult<Vec<CountResult>> {
550    let exclude_user_ids =
551        get_user_ids_to_exclude_from_course_language_group_stats(conn, course_language_group_id)
552            .await?;
553    let (interval_unit, time_unit) = granularity.get_sql_units();
554
555    let res = sqlx::query_as!(
556        CountResult,
557        r#"
558SELECT DATE_TRUNC($5, created_at) AS "period",
559COUNT(DISTINCT user_id) AS "count!"
560FROM course_module_completions
561WHERE course_id IN (
562    SELECT id
563    FROM courses
564    WHERE course_language_group_id = $1
565      AND deleted_at IS NULL
566  )
567  AND prerequisite_modules_completed = TRUE
568  AND needs_to_be_reviewed = FALSE
569  AND passed = TRUE
570  AND deleted_at IS NULL
571  AND NOT user_id = ANY($2)
572  AND created_at >= NOW() - ($3 || ' ' || $4)::INTERVAL
573GROUP BY "period"
574ORDER BY "period"
575        "#,
576        course_language_group_id,
577        &exclude_user_ids,
578        &time_window.to_string(),
579        interval_unit,
580        time_unit,
581    )
582    .fetch_all(conn)
583    .await?;
584
585    Ok(res)
586}
587
588/// Get unique users starting counts for all language versions with specified time granularity.
589///
590/// The time_window parameter controls how far back to look:
591/// - For Year granularity: number of years
592/// - For Month granularity: number of months
593/// - For Day granularity: number of days
594pub async fn unique_users_starting_history_all_language_versions(
595    conn: &mut PgConnection,
596    course_language_group_id: Uuid,
597    granularity: TimeGranularity,
598    time_window: u16,
599) -> ModelResult<Vec<CountResult>> {
600    let exclude_user_ids =
601        get_user_ids_to_exclude_from_course_language_group_stats(conn, course_language_group_id)
602            .await?;
603    let (interval_unit, time_unit) = granularity.get_sql_units();
604
605    let res = sqlx::query_as!(
606        CountResult,
607        r#"
608SELECT DATE_TRUNC($5, created_at) AS "period",
609  COUNT(DISTINCT user_id) AS "count!"
610FROM user_course_settings
611WHERE current_course_id IN (
612    SELECT id
613    FROM courses
614    WHERE course_language_group_id = $1
615      AND deleted_at IS NULL
616  )
617  AND deleted_at IS NULL
618  AND NOT user_id = ANY($2)
619  AND created_at >= NOW() - ($3 || ' ' || $4)::INTERVAL
620GROUP BY "period"
621ORDER BY "period"
622        "#,
623        course_language_group_id,
624        &exclude_user_ids,
625        &time_window.to_string(),
626        interval_unit,
627        time_unit,
628    )
629    .fetch_all(conn)
630    .await?;
631
632    Ok(res)
633}
634
635/// Total unique users in the course settings table, grouped by course instance.
636///
637/// Returns a HashMap where keys are course instance IDs and values are the total user counts
638/// for that instance.
639pub async fn get_total_users_started_course_by_instance(
640    conn: &mut PgConnection,
641    course_id: Uuid,
642) -> ModelResult<HashMap<Uuid, CountResult>> {
643    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
644    let results = sqlx::query!(
645        r#"
646SELECT current_course_instance_id AS "instance_id!",
647  NULL::timestamptz AS "period",
648  COUNT(DISTINCT user_id) AS "count!"
649FROM user_course_settings
650WHERE current_course_id = $1
651  AND deleted_at IS NULL
652  AND user_id != ALL($2)
653GROUP BY current_course_instance_id
654        "#,
655        course_id,
656        &exclude_user_ids
657    )
658    .fetch_all(conn)
659    .await?;
660
661    let mut grouped_results = HashMap::new();
662    for row in results {
663        let count_result = CountResult {
664            period: row.period,
665            count: row.count,
666        };
667        grouped_results.insert(row.instance_id, count_result);
668    }
669
670    Ok(grouped_results)
671}
672
673/// Total unique users who have completed the course, grouped by course instance.
674///
675/// Returns a HashMap where keys are course instance IDs and values are the completion counts
676/// for that instance.
677pub async fn get_total_users_completed_course_by_instance(
678    conn: &mut PgConnection,
679    course_id: Uuid,
680) -> ModelResult<HashMap<Uuid, CountResult>> {
681    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
682    let results = sqlx::query!(
683        r#"
684SELECT ucs.current_course_instance_id AS "instance_id!",
685  NULL::timestamptz AS "period",
686  COUNT(DISTINCT c.user_id) AS "count!"
687FROM course_module_completions c
688JOIN user_course_settings ucs ON c.user_id = ucs.user_id
689  AND ucs.current_course_id = c.course_id
690WHERE c.course_id = $1
691  AND c.deleted_at IS NULL
692  AND c.user_id != ALL($2)
693GROUP BY ucs.current_course_instance_id
694        "#,
695        course_id,
696        &exclude_user_ids
697    )
698    .fetch_all(conn)
699    .await?;
700
701    let mut grouped_results = HashMap::new();
702    for row in results {
703        let count_result = CountResult {
704            period: row.period,
705            count: row.count,
706        };
707        grouped_results.insert(row.instance_id, count_result);
708    }
709
710    Ok(grouped_results)
711}
712
713/// Total unique users who have returned at least one exercise, grouped by course instance.
714///
715/// Returns a HashMap where keys are course instance IDs and values are the submission counts
716/// for that instance.
717pub async fn get_total_users_returned_at_least_one_exercise_by_instance(
718    conn: &mut PgConnection,
719    course_id: Uuid,
720) -> ModelResult<HashMap<Uuid, CountResult>> {
721    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
722    let results = sqlx::query!(
723        r#"
724SELECT ucs.current_course_instance_id AS "instance_id!",
725  NULL::timestamptz AS "period",
726  COUNT(DISTINCT ess.user_id) AS "count!"
727FROM exercise_slide_submissions ess
728JOIN user_course_settings ucs ON ess.user_id = ucs.user_id
729  AND ucs.current_course_id = ess.course_id
730WHERE ess.course_id = $1
731  AND ess.deleted_at IS NULL
732  AND ess.user_id != ALL($2)
733GROUP BY ucs.current_course_instance_id
734        "#,
735        course_id,
736        &exclude_user_ids
737    )
738    .fetch_all(conn)
739    .await?;
740
741    let mut grouped_results = HashMap::new();
742    for row in results {
743        let count_result = CountResult {
744            period: row.period,
745            count: row.count,
746        };
747        grouped_results.insert(row.instance_id, count_result);
748    }
749
750    Ok(grouped_results)
751}
752
753/// Get course completion counts with specified time granularity, grouped by course instance.
754///
755/// Returns a HashMap where keys are course instance IDs and values are vectors of completion counts
756/// over time for that instance.
757///
758/// The time_window parameter controls how far back to look:
759/// - For Year granularity: number of years
760/// - For Month granularity: number of months
761/// - For Day granularity: number of days
762pub async fn course_completions_history_by_instance(
763    conn: &mut PgConnection,
764    course_id: Uuid,
765    granularity: TimeGranularity,
766    time_window: u16,
767) -> ModelResult<HashMap<Uuid, Vec<CountResult>>> {
768    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
769    let (interval_unit, time_unit) = granularity.get_sql_units();
770
771    // Get completions joined with user_course_settings to get instance information
772    let results = sqlx::query!(
773        r#"
774WITH completions AS (
775SELECT c.user_id,
776  c.created_at,
777  ucs.current_course_instance_id
778FROM course_module_completions c
779  JOIN user_course_settings ucs ON c.user_id = ucs.user_id
780  AND ucs.current_course_id = c.course_id
781WHERE c.course_id = $1
782  AND c.prerequisite_modules_completed = TRUE
783  AND c.needs_to_be_reviewed = FALSE
784  AND c.passed = TRUE
785  AND c.deleted_at IS NULL
786  AND NOT c.user_id = ANY($2)
787  AND c.created_at >= NOW() - ($3 || ' ' || $4)::INTERVAL
788)
789SELECT current_course_instance_id AS "instance_id!",
790DATE_TRUNC($5, created_at) AS "period",
791COUNT(DISTINCT user_id) AS "count!"
792FROM completions
793GROUP BY current_course_instance_id,
794period
795ORDER BY current_course_instance_id,
796period "#,
797        course_id,
798        &exclude_user_ids,
799        &time_window.to_string(),
800        interval_unit,
801        time_unit,
802    )
803    .fetch_all(conn)
804    .await?;
805
806    // Convert the flat results into a HashMap grouped by instance_id
807    let mut grouped_results: HashMap<Uuid, Vec<CountResult>> = HashMap::new();
808
809    for row in results {
810        let count_result = CountResult {
811            period: row.period,
812            count: row.count,
813        };
814
815        grouped_results
816            .entry(row.instance_id)
817            .or_default()
818            .push(count_result);
819    }
820
821    Ok(grouped_results)
822}
823
824/// Get unique users starting counts with specified time granularity, grouped by course instance.
825///
826/// Returns a HashMap where keys are course instance IDs and values are vectors of user counts
827/// over time for that instance.
828///
829/// The time_window parameter controls how far back to look:
830/// - For Year granularity: number of years
831/// - For Month granularity: number of months
832/// - For Day granularity: number of days
833pub async fn unique_users_starting_history_by_instance(
834    conn: &mut PgConnection,
835    course_id: Uuid,
836    granularity: TimeGranularity,
837    time_window: u16,
838) -> ModelResult<HashMap<Uuid, Vec<CountResult>>> {
839    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
840    let (interval_unit, time_unit) = granularity.get_sql_units();
841
842    let results = sqlx::query!(
843        r#"
844SELECT current_course_instance_id AS "instance_id!",
845DATE_TRUNC($5, created_at) AS "period",
846COUNT(DISTINCT user_id) AS "count!"
847FROM user_course_settings
848WHERE current_course_id = $1
849AND deleted_at IS NULL
850AND NOT user_id = ANY($2)
851AND created_at >= NOW() - ($3 || ' ' || $4)::INTERVAL
852GROUP BY current_course_instance_id,
853period
854ORDER BY current_course_instance_id,
855period
856    "#,
857        course_id,
858        &exclude_user_ids,
859        &time_window.to_string(),
860        interval_unit,
861        time_unit,
862    )
863    .fetch_all(conn)
864    .await?;
865
866    // Convert the flat results into a HashMap grouped by instance_id
867    let mut grouped_results: HashMap<Uuid, Vec<CountResult>> = HashMap::new();
868
869    for row in results {
870        let count_result = CountResult {
871            period: row.period,
872            count: row.count,
873        };
874
875        grouped_results
876            .entry(row.instance_id)
877            .or_default()
878            .push(count_result);
879    }
880
881    Ok(grouped_results)
882}
883
884/// Get first exercise submission counts with specified time granularity, grouped by course instance.
885///
886/// Returns a HashMap where keys are course instance IDs and values are vectors of submission counts
887/// over time for that instance.
888///
889/// The time_window parameter controls how far back to look:
890/// - For Year granularity: number of years
891/// - For Month granularity: number of months
892/// - For Day granularity: number of days
893pub async fn first_exercise_submissions_history_by_instance(
894    conn: &mut PgConnection,
895    course_id: Uuid,
896    granularity: TimeGranularity,
897    time_window: u16,
898) -> ModelResult<HashMap<Uuid, Vec<CountResult>>> {
899    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
900    let (interval_unit, time_unit) = granularity.get_sql_units();
901
902    let results = sqlx::query!(
903        r#"
904WITH first_submissions AS (
905SELECT user_id,
906  MIN(created_at) AS first_submission
907FROM exercise_slide_submissions
908WHERE course_id = $1
909  AND deleted_at IS NULL
910  AND NOT user_id = ANY($2)
911  AND created_at >= NOW() - ($3 || ' ' || $4)::INTERVAL
912GROUP BY user_id
913)
914SELECT ucs.current_course_instance_id AS "instance_id!",
915DATE_TRUNC($5, fs.first_submission) AS "period",
916COUNT(fs.user_id) AS "count!"
917FROM first_submissions fs
918JOIN user_course_settings ucs ON fs.user_id = ucs.user_id
919AND ucs.current_course_id = $1
920GROUP BY ucs.current_course_instance_id,
921period
922ORDER BY ucs.current_course_instance_id,
923period
924    "#,
925        course_id,
926        &exclude_user_ids,
927        &time_window.to_string(),
928        interval_unit,
929        time_unit,
930    )
931    .fetch_all(conn)
932    .await?;
933
934    // Convert the flat results into a HashMap grouped by instance_id
935    let mut grouped_results: HashMap<Uuid, Vec<CountResult>> = HashMap::new();
936
937    for row in results {
938        let count_result = CountResult {
939            period: row.period,
940            count: row.count,
941        };
942
943        grouped_results
944            .entry(row.instance_id)
945            .or_default()
946            .push(count_result);
947    }
948
949    Ok(grouped_results)
950}
951
952/// Get users returning exercises counts with specified time granularity, grouped by course instance.
953///
954/// Returns a HashMap where keys are course instance IDs and values are vectors of user counts
955/// over time for that instance.
956///
957/// The time_window parameter controls how far back to look:
958/// - For Year granularity: number of years
959/// - For Month granularity: number of months
960/// - For Day granularity: number of days
961pub async fn users_returning_exercises_history_by_instance(
962    conn: &mut PgConnection,
963    course_id: Uuid,
964    granularity: TimeGranularity,
965    time_window: u16,
966) -> ModelResult<HashMap<Uuid, Vec<CountResult>>> {
967    let exclude_user_ids = get_user_ids_to_exclude_from_course_stats(conn, course_id).await?;
968    let (interval_unit, time_unit) = granularity.get_sql_units();
969
970    let results = sqlx::query!(
971        r#"
972SELECT ucs.current_course_instance_id AS "instance_id!",
973DATE_TRUNC($5, ess.created_at) AS "period",
974COUNT(DISTINCT ess.user_id) AS "count!"
975FROM exercise_slide_submissions ess
976JOIN user_course_settings ucs ON ess.user_id = ucs.user_id
977AND ucs.current_course_id = ess.course_id
978WHERE ess.course_id = $1
979AND ess.deleted_at IS NULL
980AND NOT ess.user_id = ANY($2)
981AND ess.created_at >= NOW() - ($3 || ' ' || $4)::INTERVAL
982GROUP BY ucs.current_course_instance_id,
983period
984ORDER BY ucs.current_course_instance_id,
985period
986    "#,
987        course_id,
988        &exclude_user_ids,
989        &time_window.to_string(),
990        interval_unit,
991        time_unit,
992    )
993    .fetch_all(conn)
994    .await?;
995
996    // Convert the flat results into a HashMap grouped by instance_id
997    let mut grouped_results: HashMap<Uuid, Vec<CountResult>> = HashMap::new();
998
999    for row in results {
1000        let count_result = CountResult {
1001            period: row.period,
1002            count: row.count,
1003        };
1004
1005        grouped_results
1006            .entry(row.instance_id)
1007            .or_default()
1008            .push(count_result);
1009    }
1010
1011    Ok(grouped_results)
1012}