1use crate::library::TimeGranularity;
2use crate::{prelude::*, roles::UserRole};
3use std::collections::HashMap;
4
5#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
8#[cfg_attr(feature = "ts_rs", derive(TS))]
9pub struct CountResult {
10 pub period: Option<DateTime<Utc>>,
13 pub count: i64,
15}
16
17#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
20#[cfg_attr(feature = "ts_rs", derive(TS))]
21pub struct AverageMetric {
22 pub period: Option<DateTime<Utc>>,
24 pub average: Option<f64>,
26}
27
28#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
32#[cfg_attr(feature = "ts_rs", derive(TS))]
33pub struct CohortActivity {
34 pub cohort_start: Option<DateTime<Utc>>,
36 pub activity_period: Option<DateTime<Utc>>,
38 pub offset: Option<i32>,
40 pub active_users: i64,
42}
43
44async 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
61async 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
80pub 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
104pub 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
128pub 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
152pub 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
184pub 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
216pub 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
256pub 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
301pub 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
341pub 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
397pub 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
495pub 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
538pub 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
588pub 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
635pub 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
673pub 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
713pub 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
753pub 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 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 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
824pub 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 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
884pub 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 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
952pub 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 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}