Early Insights

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


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

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. 1


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
Using Percentage
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”

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.

Using Standard Deviation
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”

Footnotes

  1. 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.↩︎