02-02b-early.qmd

---
title: Early Insights
execute: 
  enabled: false
lightbox:
  match: auto
  effect: fade
  desc-position: bottom
  loop: false
---

## Unveiling Basic Patterns

Even with this basic model, we can easily extract valuable insights, for example:

-   **Activity Load**: Identify staff with the highest number of teaching activities or total teaching hours.
-   **Student Timetable Profiles**: Calculate average hours per student or per programme to understand workload distribution.
-   **Resource utilisation**: Determine the busiest teaching locations or times on campus.
-   **Anomaly detection**: Identify students who have unexpected profiles or unusual combinations.

### Example code

**Busiest locations overall**

```{cypher}{.scroll-cypher} 

MATCH (r:room)<-[:OCCUPIES]-(a:activity) 
WITH r, sum(a.actDuration)/60 AS totalDurationInHours 
RETURN r.roomName AS Room, r.roomCapacity AS Capacity, r.roomType AS Type, totalDurationInHours 
ORDER BY totalDurationInHours 
DESC LIMIT 3
```

| Room    | Capacity | Type     | totalDurationInHours |
|---------|----------|----------|----------------------|
| 2Q12 FR | 25       | PC LAB   | 21                   |
| 4Q69 FR | 36       | PC LAB   | 19                   |
| 3E11 FR | 48       | TEACHING | 18                   |

**Busiest location for a specific time**

```{cypher}{.scroll-cypher} 
MATCH (r:room)<-[:OCCUPIES]-(a:activity) 
WHERE a.actStartTime = localtime({hour:9, minute:0, second:0})  
WITH r, count(a) AS Count, a.actStartTime AS StartTime 
RETURN r.roomName AS Room, Count, StartTime 
ORDER BY Count DESC 
LIMIT 3
```

| Room    | Count | StartTime |
|---------|-------|-----------|
| 2Q12 FR | 86    | 09:00:00  |
| 3E28 FR | 50    | 09:00:00  |
| 3E11 FR | 49    | 09:00:00  |

**Students with below/above average hours**

This query returns students and whether they have more or less scheduled time on their timetable compared to the programme cohort average. [^5]

[^5]: The average is calculated based on the total duration of activities attended by each student. The below/above average classification is based on a 10% deviation from the average. Alternative approaches have been used to define the average and the deviation threshold including median values and standard deviations.

```{cypher}{.scroll-cypher} 

MATCH (s:student)-[:ATTENDS]->(a:activity)
WITH s.stuProgName AS progName, s.stuID_anon AS studentID, SUM(a.actDurationInMinutes) AS studentTotalDuration
WITH progName, 
     AVG(studentTotalDuration) / 60 AS progAverageHoursPerStudent, 
     collect({studentID: studentID, studentTotalHours: studentTotalDuration / 60}) AS studentsData
UNWIND studentsData AS studentData
RETURN progName, 
       progAvgHrsPerStudent,
       studentData.studentID AS studentID, 
       studentData.studentTotalHours AS studentTotalHours, 
       CASE 
           WHEN studentData.studentTotalHours < (progAverageHoursPerStudent * 0.9) THEN 'Below Average'
           WHEN studentData.studentTotalHours > (progAverageHoursPerStudent * 1.1) THEN 'Above Average'
           ELSE 'Average'
       END AS compare
```

| prog Name | progAvgHours PerStudent | studentID | student TotalHours | compare |
|---------------|---------------|---------------|---------------|---------------|
| "Maths NS" | 274.7692307692308 | "stu-23442558" | 361 | "Above Average" |
| "Maths NS" | 274.7692307692308 | "stu-91911371" | 126 | "Below Average" |
| "Maths NS" | 274.7692307692308 | "stu-75224499" | 251 | "Average" |

: Using Percentage

When using standard deviation (1SD) the same three students have a different outcome. This is primarily due to the exceptionally large standard deviation. This is because students on this version of the programme could be trailing modules and either attending significantly more or less activity, making the range very large.

| prog Name | progAvgHours PerStudent | progStDevHours PerStudent | studentID | student TotalHours | compare |
|----|----|----|----|----|----|
| "Maths NS" | 274.7692307692308 | 118.81231781219205 | "stu-23442558" | 361 | "Average" |
| "Maths NS" | 274.7692307692308 | 118.81231781219205 | "stu-91911371" | 126 | "Below Average" |
| "Maths NS" | 274.7692307692308 | 118.81231781219205 | "stu-42997469" | 251 | "Below Average" |

: Using Standard Deviation