Per-meeting attendance rate

[lost1227]

Feature request: see how many meetings a student has attended in a given time period.

Note: this does not mean seeing meetings attended by any individual student (this is already implemented on the student details page). This feature is to have a report showing number of meetings (and possibly time spent at meetings) for every active student.

[lost1227]

Meeting count:

SELECT COUNT(DISTINCT CAST(created_at AS date)) FROM attendance_events WHERE created_at >= '2023-01-01';

Meetings attended per-student:

SELECT student_id, COUNT(DISTINCT CAST(created_at AS date)) AS meetings_attended
FROM attendance_events
WHERE created_at >= '2023-01-01'
GROUP BY student_id;

[lost1227]

Note, as we learned in #46 and #48, we'll need to consider time zones.

The updated queries are as follows:

Meeting count:

SELECT COUNT(DISTINCT CAST(CONVERT_TZ(created_at, '+0:00', '-8:00') AS DATE)) FROM attendance_events WHERE created_at >= '2023-01-01';

Meetings attended per-student

SELECT student_id, COUNT(DISTINCT CAST(CONVERT_TZ(created_at, '+0:00', '-8:00') AS DATE)) AS meetings_attended
FROM attendance_events
WHERE created_at >= '2023-01-01'
GROUP BY student_id;

[lost1227]

And, because of #51, we'll need to filter out soft-deleted events:

Meeting count:

SELECT COUNT(DISTINCT CAST(CONVERT_TZ(created_at, '+0:00', '-8:00') AS DATE)) FROM attendance_events WHERE created_at >= '2023-01-01' AND deleted_at IS NULL;

Meetings attended per-student:

SELECT student_id, COUNT(DISTINCT CAST(CONVERT_TZ(created_at, '+0:00', '-8:00') AS DATE)) AS meetings_attended
FROM attendance_events
WHERE created_at >= '2023-01-01'
AND deleted_at IS NULL
GROUP BY student_id;